Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Release Notes

SQL Server | Wait Events Drilldown

The Wait Events drilldown assesses sampled workload waits, that is waits that occur as a result of executing SQL statements (via an XEvents trace). The Wait Events drilldown is designed to assist in identifying SQL Statements involved with specific waits to the extent that this information is actually available.

To open the Wait Events drilldown

To open the drilldown for the selected category of wait
  1. Open the SQL Server | Performance Health Drilldown
  2. Click on a bar of the Instance Wait Time chart.

To open the drilldown with category ALL initially selected

From the Spotlight on SQL Server ribbon:

  • Click Monitor | Wait Events.

Category and Workload

The category and workload selected qualify the information shown on the grids and charts on this page.

Category

Select from the available categories of waits or select ALL for all waits.

The SQL Server | Performance Health Drilldown shows the categories currently contributing to wait time and in what proportion. Note that data from the Performance Health and Wait Events drilldowns is sourced from different places (and data from the Wait Events drilldown is sampled) so it is not mathematically valid to directly compare them.

Workload

Select a workload to investigate for the selected category of wait.

The workload items are dependent on the Category selected.

Workload Description
Database Which database is contributing most to the wait time?
Application Which application is contributing most to the wait time?
User Which user is contributing most to the wait time?
Host Which host is contributing most to the wait time?
Wait Type Which wait type is contributing most to the wait time?

Statement

 

Which SQL statement is contributing most to the wait time?

TIP: Right click on a statement and select View Workload Analysis to show the statement in the SQL Server | Workload Analysis Drilldown drilldown. Workload Analysis will provide execution details of the SQL statement like how many times the query ran.

Grids and Charts

TIP: When a Workload Statement is selected, click View Workload Analysis to show the statement in the SQL Server | Workload Analysis Drilldown drilldown. Workload Analysis will provide execution details of the SQL statement like how many times the query ran.

 

Category Description

Category and Workload wait
Grid Description
Wait Type The wait type impacting on wait time.
Wait Time The wait time spent on this wait type aggregated over the last hour.
% of Total Wait The proportion of wait time spent on this wait type, compared to other wait types of the selected workload.
% of Total Available CPU Time The proportion of available CPU time spent on this wait type over the time frame selected on this drilldown page.

Selected item compared to All
Chart Description
Selected item compared to All Show on a time series chart the wait time attributed to the selected workload item and the selected category of wait.
Current selected item compared Show on a pie chart the wait time currently attributed to the selected workload item as a proportion of the selected category of wait.

Selected category compared to Total
Chart Description
Selected category comparison Show on a time series chart the wait time attributed to the selected category and the total wait time.
Current Wait category comparison Show on a pie chart the wait time currently attributed to the selected category as a proportion of the total wait.

 

Note: For information on unknown values or missing values or too many "0" values, see Data display and collection problems - SQL Server connection.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating