As you probably know I am very fond of SQL Server Wait Statistics (shameless book link) and the options they provide for analyzing SQL Server performance. During SQL Server version releases new options became available to monitor and/or analyze Wait Statistics but one option for Wait Statistics analysis was still missing, the ability to monitor Wait Statistics on a session level instead of instance wide. Now imagine my surprise (and dancing all through the room) when SQL Server 2016 introduced a brand new DMV called sys.dm_exec_session_wait_stats!

With all the awesome new features in SQL Server 2016 (like Query Store), it seems this little DMV moves a bit under the radar regarding attention and the level of detail it will provide when dealing with performance issues. So let’s give this DMV the attention it deserves!

Sys.dm_exec_session_wait_stats

Before we go and look at the new DMV and what information it returns, let’s take a look at the MSDN entry of this new DMV:

“Returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches. “

So it sounds like this DMV is the answer to my years of hoping for a session based Wait Statistics view.

Let’s start of simple by running a SELECT against the DMV and see what is returned.

SELECT *

FROM sys.dm_exec_session_wait_stats

Now this sure looks familiar! As a matter of fact, it is completely identical to the sys.dm_os_wait_stats DMV but with an extra column for the session ID.

What is returned in the figure above are the waits two of my sessions (51 & 53) encountered when executing queries through SQL Server Management Studio, so let’s see what happens if I open up a new query window (take note of the session id) and execute the query below.

— Clear the buffer cache

DBCC DROPCLEANBUFFERS

— Generate some PAGEIOLATCH_SH & ASYNC_NETWORK_IO waits

USE AdventureWorks2014

SELECT *

FROM Sales.SalesOrderDetail

This query removes all the pages from the Buffer Cache and then performs a select against the Sales.SalesOrderDetail table in the AdventureWorks2014 database. The reason for clearing the Buffer Cache is to make sure some PAGEIOLATCH_SH waits turn up (PAGEIOLATCH_SH waits are recorded whenever SQL Server has to move a data page from disk into the Buffer Cache).

Now if I switch back to my previous query window and query the sys.dm_exec_session_wait_stats using the session ID from which I ran the query above, I can see all the wait types the query above ran into when executing.

Awesome isn’t it?

However, there is a catch. The sys.dm_exec_session_wait_stats only records Wait Statistics information for sessions that are connected to the SQL Server database! As soon as the session disconnects the Wait Statistics information is removed from the DMV. We can easily test this by closing the query window (or killing the session id) that we used to run our query against the AdventureWorks2014 database and run the select query against the sys.dm_exec_session_wait_stats again.

— Kill our AdventureWorks session

KILL 54

— Query session Wait Stats

SELECT *

FROM sys.dm_exec_session_wait_stats

WHERE session_id = 54

As you can see, the sys.dm_exec_session_wait_stats DMV no longer returns information because the session ID is no longer active. This behavior seems pretty logical for the function of the DMV, session ID’s can be reused and if we want to capture and store Wait Statistics on a per-session basis this would require fundamental changes in the way Wait Statistics are stored and gathered.

Sys.dm_exec_session_wait_stats usage

So now that we have access to this great new DMV, what are some of the usage scenarios?

The most obvious one is, of course, the ability to track what waits a single session runs into when executing its workload. This can be very powerful if you want to tune performance or take a look at what’s happening when a user complains about bad performance.

For instance, the query below joins the sys.dm_exec_wait_stats DMV to the sys.dm_exec_sessions DMV to grab Wait Statistics information for all the active sessions being run by “enrico@dotnine.nl”:

SELECT

s.nt_user_name,

s.[host_name],

wait_type,

wait_time_ms

FROM sys.dm_exec_session_wait_stats sws

INNER JOIN sys.dm_exec_sessions s

ON sws.session_id = s.session_id

WHERE s.nt_user_name = ‘enrico@dotnine.nl’

As you can guess we can use the information inside sys.dm_exec_session_wait_stats DMV for all kinds of analysis.

Another example is the query below, here we will use the DMV to take a look at what the most popular Wait Types are of all the currently active sessions.

SELECT

wait_type,

SUM(wait_time_ms) AS ‘Active session Wait Time’

FROM sys.dm_exec_session_wait_stats

GROUP BY wait_type

ORDER BY SUM(wait_time_ms) DESC

This query can give you a good idea of what types of waits your active sessions are running into without the need to capture information from the sys.dm_os_wait_stats DMV and calculate a delta, thus providing a more “direct” view.

Summary

The addition of the new sys.dm_exec_session_wait_stats DMV inside SQL Server 2016 extents the options we have when analyzing performance and introduces a new level of Wait Statistics analysis. This DMV, and the introduction of the Query Store, brings performance analysis to a whole new level in SQL Server 2016!