Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Get Detailed Wait Stats with SQL Server Execution Plan


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

Attend these FREE MSSQLTips webcasts >> click to register


Problem

When you execute a T-SQL query, it takes CPU time to process the query and retrieve the requested data, especially on busy systems with a large number of concurrent queries that share the same resources. SQL Server stores such information about query wait time and wait resources to help troubleshoot the query performance and track resource bottlenecks. Getting wait statistics information about a specific query is not very simple and requires strong T-SQL coding skills. But with SQL Server 2016 SP1 comes a direct way to get the wait statistics information about a specific query from the query execution plan, which we will discuss in this tip.

Solution

SQL Server Wait Statistics are one of the most important and common performance tuning metrics that can be used to track and troubleshoot query performance by identifying resource bottlenecks the query is waiting for. There are two main categories for the SQL Server Wait Statistics; the Single Wait type, where the query is waiting for CPU resource availability and the Resource Wait type, where the query is waiting for a specific resource availability, such as I/O or Memory resources.

The Wait Statistics information, such as the wait type, the wait duration and the wait count are recorded by the SQL Server Engine to help in tracking performance issues. The wait statistics information about an actively running query can be retrieved by joining different system DMVs such as sys.dm_os_waiting_tasks and sys.dm_os_wait_stats DMVs, in addition to the current state of the running queries. But what if I want to get wait statistics information of a specific query only? And what if I am not able to run both my query and the one querying the wait statistics information concurrently?

SQL Server 2016 SP1 comes with a lot of new features and improvements and of these new enhancements is to query execution plans, in which you can see the wait statistics information of the query inside the actual execution plan of that query, making the execution plans rich with extra valuable information.

Let's say we want to troubleshoot the below query that runs very frequently in our system, but recently it is running slower than usual. We will enable the Actual Execution Plan for the query before executing it, in order to trace the performance.

SELECT [TransactionID]
      ,[ProductID]
      ,[ReferenceOrderID]
      ,[ReferenceOrderLineID]
      ,[TransactionDate]
      ,[TransactionType]
      ,[Quantity]
      ,[ActualCost]
      ,[ModifiedDate]
      ,[Address]
FROM [MSSQLTipsDemo].[dbo].[TransactionHistory_New]
WHERE ReferenceOrderID <>15411 and [Address] LIKE '%Walnut%'
ORDER BY ModifiedDate DESC
   

The query is running under session 54, as you can see below:

SQL Server Query Plan for Session 54

Previous to SQL Server 2016 SP1, to get the wait statistics information about the query, the below script that joins sys.dm_os_waiting_tasks, sys.dm_exec_sessions and sys.dm_exec_requests DMVs can be used:

SELECT OSW.session_id,
       OSW.wait_duration_ms,
       OSW.wait_type,
       DB_NAME(EXR.database_id) AS DatabaseName
FROM sys.dm_os_waiting_tasks OSW
INNER JOIN sys.dm_exec_sessions EXS ON OSW.session_id = EXS.session_id
INNER JOIN Join sys.dm_exec_requests EXR ON EXR.session_id = OSW.session_id
OPTION(Recompile);
   

From the previous script that returns the wait statistics information about all currently running queries, we can see that session 54 is waiting for ďASYNC_NETWORK_IOĒ wait type as shown below.

session number 54 is waiting for ASYNC_NETWORK_IO wait type

With SQL Server 2016 SP1:

SQL Server 2016 (SP1-CU3) is the version

The Wait Statistics information can be viewed from the Actual Execution Plan of the query by right-clicking on the left most operator (the SELECT operator) in the plan to display the operatorís Properties window. The window can be also viewed by choosing the operator then clicking F4. The operator Properties window will be like below:

SQL Server WaitStats Properties for the SELECT statement

As you can see from the previous image, a new section is added to the operator properties window that lists the Wait Stats information of that query. If you expand the WaitStats section from the properties window, we can see the below information.

SQL Server WaitStats Details including the Count, Time in MS and Type

A list of all wait types, that the executed query was waiting for during its execution will be displayed in that new section, with extra information about how many times the query waited for that wait type and the total duration for each wait type.

Comparing the wait statistics information derived from the execution plan with the information derived from querying the DMVs, you will see that the execution plan provided us with full wait statistics details for that specific query, but we were only able to catch the last wait type from the DMVs script.

During its execution, the query was waiting for three wait types: the LATCH_SH, LATCH_EX and ASYNC_NETWORK_IO as shown from the execution plan, not only ASYNC_NETWORK_IO as shown by querying the system DMVs.

The same wait statistics information can be also viewed easily from the XML execution plan by looking at the new WaitStats section as shown below:

SQL Server WaitStats as an XML Plan

Keep in mind that the wait statistics information can be viewed only from the Actual Execution Plan that is generated after executing the query. If you try to search for the WaitStats information from the Estimated Execution Plan that is generated without executing the query, you will not be able to see the wait stats. This make sense as the SQL Server Engine cannot predicate what the query will be waiting for during its execution. The SELECT operator properties window shown below from the Estimated Execution Plan has no information about the query wait statistics:

Estimated Plan does not include WaitStats
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools