SQL Server has hundreds of wait types that can help you troubleshoot performance problems. The SentryOne Performance Analysis Dashboard helps you rapidly analyze your wait statistics so that you can quickly identify and resolve problems. Let’s look at how SentryOne helps troubleshoot SQL Server problems identified by wait types.
SQL Server waits occur when a thread running on a processor can’t proceed because a resource is unavailable; it must wait until the resource becomes available again. Although not all waits are indicative of problems, many wait types are an important clue in SQL Server performance troubleshooting. To learn more about what waits are and how to analyze them, see our webinar with Paul Randal of SQLskills, “Performance Troubleshooting Using Wait Statistics."
The SQLskills SQL Server Wait Types Library
SQL Server has hundreds of wait types, all of which are documented in the SQLskills SQL Server Wait Types Library. Within this library, each wait type includes:
All waits include an infographic showing how prevalent the wait is based on the previous month’s aggregated monitoring data from many thousands of SentryOne monitored instances.
The SentryOne Performance Analysis Dashboard helps you troubleshoot performance issues by offering a holistic view of SQL Server activity. The dashboard (shown below) presents key operating system–level metrics and SQL Server activity metrics, including wait statistics, all in one location. This dashboard enables you to visually correlate potentially related symptoms.
The Performance Analysis Dashboard highlights the selected time frame on each graph for quick visual correlation.
The Performance Analysis Dashboard also offers baselining capabilities, allowing you to quickly reference past time frames, which enables you to rapidly determine if observed activity is normal or abnormal compared to the selected baseline.
“SentryOne helps answer the question: Is this activity normal for this server or workload?”
– Andy Yun, SentryOne senior solutions engineer
Customizing Wait Statistics in the Dashboard
You can customize your SentryOne dashboard to show the waits that are most important to you, as well as break out and create custom categories.
More information can be found in the SentryOne documentation, as well as in the blog My Favorite SQL Sentry v8.4 Features: #1 – Dashboard Custom Categories.
SentryOne provides many standard reports, including the SQL Server Wait Stats Analysis report, which allows you to identify and further drill into potential problem areas. These reports help you understand what has happened within a specific period and gauge whether the wait pattern is normal or is indicative of a problem.
The SQL Server Wait Stats Analysis report provides categorized views of wait statistics.
A second report further breaks out the data by resource and CPU waits. This report can show if there is a spike in a specific wait type. By seeing how granular the waits are—whether it’s a general CPU problem or specific to a wait type—you can zero in on the issue quickly.
Breaking out wait types by CPU can help identify problems.
The reports in SentryOne can be deployed to SQL Server Reporting Services (SSRS) and modified to provide additional information your business is interested in seeing. Common modifications include filtering out more benign SQL Server wait types, known in your environment, so that you can focus on just those waits that might indicate a problem.
SentryOne enables you to quickly identify potential SQL Server wait problems and commence troubleshooting. It’s important to have basic knowledge about baselines and workload patterns in your organization and on your servers to take full advantage of this tool.