White Paper

Rapidly Analyzing Wait Statistics with SentryOne

 

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 are important clues in performance troubleshooting.

 

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:

  • A description
  • Known occurrence
  • SQL Server versions in which the wait type was added and removed
  • An extended events wait_type value
  • Other important information

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 identify problems quickly.

 

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 timeframe on each graph for quick visual correlation.

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.

 

Reports drill into wait statistics, making it easier to zero in on problem areas.

 

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.

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.

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.

 

Cast a wide net and know baselines and workload patterns.

 

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.

  • Be familiar with workload patterns and be able to determine if the pattern is normal or abnormal, especially when troubleshooting SQL Server performance problems and looking at wait statistics.
  • Know the baseline. It’s impossible to know if there is a problem unless “normal” is known. Similarly, it’s difficult to tell if a problem is fixed unless the baseline is understood.
  • Cast a wide net, beginning with a holistic view of the SQL Server and then zooming in on problem areas. Often, bottlenecks are traced back to overshared resources, so you won’t find those problems if you begin by looking at a specific query level; only the big picture view will allow for that.