New Dynamic Management Views in SQL Server 2016 Part 2: dm_exec_session_waits

In part one of this series we began our journey into the new Dynamic Management Objects available in SQL Server 2016 with sys.dm_exec_session_wait_stats.  Similar to the existing sys.dm_os_wait_stats this Dynamic Management View (DMV) returns wait information - metadata pertaining to what the SQL query engine is waiting for resources on - before it can completely fulfill a request from a user's session.  Unlike the existing sys.dm_os_wait_stats DMV sys.dm_exec_session_wait_stats returns this information at the session-level of granularity.

This is exciting because by including session_id as one of the columns that is returned it allows us to connect to a plethora of other Dynamic Management Objects (DMOs) that expose session_id as one of their columns.  In addition to being able to discern the constrained resources for a session you can now use session_id to join to sys.dm_exec_requests to aggregate wait statistics to the request tier.  You can also do likewise through the transaction log layer via sys.dm_trans_session_transactions (the intermediate DMV that bridges the relationship between session_id and transaction_id in SQL Server.)

With the addition of the database_id column to sys.dm_exec_sessions as of SQL Server 2014 you can also aggregate waits for all sessions associated with a specific database.  Prior to SQL Server 2014 you had to join sys.dm_exec_sessions to sys.dm_exec_request in order to return or filter on database_id.  This means you're results were constrained to only sessions with active requests - a major shortcoming for all metadata nerds out there.

It's quite surprising actually that it took 6 versions of SQL since the creation of DMOs to include a DMV with session-level wait stats.  If you take a look at sys.sysprocesses that information has been there even before there were DMOs:

SELECT spid, lastwaittype FROM sys.sysprocesses

This means that the information was being recorded at the session level - more likely at the request level - as requests were fulfilled by the Query Engine but were only exposed to the end-user/DBA after being aggregated at the instance level.  The only places we were able to see this information at any other granularity was in sys.dm_exec_sessions and sys.dm_exec_requests DMVs but only as individual "hits" (not as aggregated values over the lifetime of the session/request.)

Why Do We Care?

As has been explained in countless articles, blogs, books, and presentations these wait statistics are cumulative.  We are not necessarily able to get an understanding on the existing state of waits without a bit of effort.  The results of sys.dm_exec_wait_stats return waits since the last time services were started or restarted.  This means you see the effects of all periods of activity: normal load and maintenance times when you're likely to see an uptick in OLEDB waits due to running queries against sys.dm_db_index_physical_stats to determine index fragmentation information used for index maintenance.  You also get wait statistics associated with those periods when Bob from HR runs that rogue query to get all the hiring information for the existence of the company (because he forgot once again to include a valid start date in that query of his.)  This is where I lean towards time-slicing my wait statistics collection: taking periodic scheduled dumps of sys.dm_os_wait_stats including a date_stamp column and persist it to a physical table in a database dedicated to administrative and maintenance tasks/data on each of my instances.  This allows me to narrow down waits for a specific range of time rather than all loads since services were restarted.

There continued to be a drawback on even this process until SQL Server 2016.  These waits are inclusive of every session submitting queries for processing and there is no designation between user sessions or system sessions.  Now that we're able to query a DMV that breaks down this information at the session level we now have options we never had before.

So why do we care?

Because waits for an instance provide us with information about what the instance as a whole sees as resource constraints based upon its overall workload.  Session waits provide insight into not only what the sessions see as their bottleneck for resources but also allow you to infer what that session is contributing to the overall waits on the instance.  Additionally (as you'll see in the three queries that close out this article) not all sessions' workloads wait on the same resources and just because the instance has high PAGEIOLATCH_EX waits it doesn't mean each session will.  This new DMV allows you to dissect performance issues in a way sys.dm_os_wait_stats didn't allow in previous versions of SQL Server.

An Added Twist - The Old Way of Collecting Wait Statistics is Invalid in SQL Server 2016

In researching this article I also discovered something interesting about SQL Server 2016 - at least when it comes to CTP 2.4: the old tried and true method of querying sys.dm_os_wait_stats now returns duplicate information and the existing calculation for running percentage of total waits doesn't calculate correctly.  Whether it's due to changes in how transactional isolation issues are handled for the underlying wait constructs, parallelism issues, or it's due to some other behind-the-scenes changes that took place to allow the creation and successful collection and reporting of session-level waits remains unknown at this time. Regardless this means we have to change up how we collect waits (at either the instance or session level) in SQL 2016. 

The old method of assessing wait metadata is simply not going to work.  When we run the existing standard query many authors and presenters have been sharing for years in some form or another (see immediately below)

WITH Waits AS ( SELECT    wait_type,    wait_time_ms / 1000. AS wait_time_s,   100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,   ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn FROM sys.dm_os_wait_stats WHERE wait_type    NOT IN     (-- filter out additional irrelevant waits'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT', 'KSOURCE_WAKEUP' , 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MISCELLANEOUS', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK', 'TRACEWRITE', 'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT', 'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_EVENTHANDLER', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE', 'HADR_NOTIFICATION_DEQUEUE', 'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL', 'HADR_TIMER_TASK', 'HADR_SYNC_COMMIT', 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'PREEMPTIVE_HADR_LEASE_MECHANISM','PREEMPTIVE_OS_GETFILEATTRIBUTES', 'PREEMPTIVE_OS_CREATEFILE', 'PREEMPTIVE_OS_FILEOPS', 'XE_LIVE_TARGET_TVF'))   SELECT W1.wait_type, CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, CAST(W1.pct AS DECIMAL(12, 2)) AS pct, CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pctFROM Waits AS W1 INNER JOIN Waits AS W2 ON W2.rn <= W1.rnGROUP BY W1.rn,  W1.wait_type,  W1.wait_time_s,  W1.pctHAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold;

these are the results we now see in SQL Server 2016 (CTP 2.4):

If you'll note item "A" you'll see that once you hit 100% for running percent the running percent value begins recalculating rather than abiding the rule for the HAVING clause.  Issue "B" eventually crops up with duplicate records.  Adding a secondary join criteria on W2.wait_type = W1.wait_type removes the duplicate records (issue "B") but does not resolve the issue regarding running percent calculation failure.  This behavior up until this release of SQL Server did not occur and there were no duplicate records despite the self-join on only W2.rn <= W1.rn. Matter of fact adding the additional join rule in earlier versions of SQL Server prior to the 2016 CTPs invalidates the HAVING clause limits.

I ran the same query above in SQL Server 2014: once as-is and the second time adding the additional join on wait_type:

Results in SQL Server 2014 self-join only on W2.rn <= W1.rn

Results in SQL Server 2014 self-join ob both W2.rn <= W1.rn AND W2.wait_type = W1.wait_type

So what are we to do?  Innovate!

Collecting Instance Waits and Session Waits in SQL Server 2016

Two changes in collecting wait statistics at both granularities work just fine:

There are a few shortcomings with needing to rely on a windowing function though: windowing functions can only be used in SELECT and WHERE clauses.  This is why you'll see that I retooled my queries here to return the TOP 3 waits rather than all waits up to a threshold of 95% of all queries which was baked into the standard wait query that has been everywhere from the book I wrote with Louis Davidson on the subject of Dynamic Management Objects in 2010 to websites and presentations world-wide.  I always found that threshold a bit of overkill as you're typically interested in the wait types that are comprising the bulk of your resource constraints rather than those that perhaps are contributing only 1-2% towards all waits as you (typically) approach that 95% threshold in the old query. 

Of course you can retool these queries below to include the TOP 5, 10 or 42 waits if you so choose.  This is up to you!

Let's look at two options for querying the new sys.dm_exec_session_wait_stats as well as a new approach to querying sys.dm_os_wait_stats for instance-level wait statistics in SQL Server 2016.

(Note that when I return results for session-level wait statistics I don't convert milliseconds to seconds. Sessions tend to not persist as long as an instance is (hopefully) running without service restarts. By converting to milliseconds we would be likely to see a great deal of wait data in fractions of a second potentially.)

Each of these queries take into consideration the need to run as MAXDOP 1 and the windowing function for calculating the running percentage as described above.

The New Process for Collecting Instance Waits in SQL Server 2016

As I delved into earlier in this article we now see a change in behavior in SQL Server 2016 (CTP 2.4) that affects the results of the tried-and-true wait stats query that we've been using for so long now.  Below is a process that will work in SQL Server 2016.

--======================================================================-- CUMULATIVE INSTANCE WAITS--======================================================================WITH Waits AS(SELECT WS.wait_type , WS.wait_time_ms, WS.signal_wait_time_ms , (WS.wait_time_ms - WS.signal_wait_time_ms) AS resource_wait_time_ms, WS.waiting_tasks_count , CASE WS.waiting_tasks_countWHEN 0 THEN 0ELSE WS.[wait_time_ms]/WS.[waiting_tasks_count]END AS avg_wait_time_ms, 100. * WS.wait_time_ms / SUM(WS.wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY (WS.wait_time_ms - WS.signal_wait_time_ms) DESC) AS rnFROM sys.dm_os_wait_stats AS WSWHERE WS.wait_type NOT IN (-- filter out additional irrelevant waits'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MISCELLANEOUS', 'ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK', 'TRACEWRITE','SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT', 'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'BROKER_EVENTHANDLER', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'HADR_WORK_QUEUE', 'HADR_NOTIFICATION_DEQUEUE', 'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL', 'HADR_TIMER_TASK', 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'PREEMPTIVE_HADR_LEASE_MECHANISM','PREEMPTIVE_OS_GETFILEATTRIBUTES', 'PREEMPTIVE_OS_CREATEFILE', 'PREEMPTIVE_OS_FILEOPS', 'HADR_SYNC_COMMIT', 'XE_LIVE_TARGET_TVF') )SELECT TOP 3 W1.wait_type, CAST(W1.wait_time_ms AS DECIMAL(20, 0)) AS wait_time_ms, CAST(W1.signal_wait_time_ms AS DECIMAL(20, 0)) AS signal_wait_time_ms, CAST(W1.resource_wait_time_ms AS DECIMAL(20, 0)) AS resource_wait_time_ms, W1.waiting_tasks_count, W1.avg_wait_time_ms, CAST(W1.pct AS DECIMAL(5, 2)) AS pct, CAST(SUM(W1.pct) OVER(ORDER BY (W1.wait_type) DESC)AS DECIMAL(5,2)) AS running_pctFROM Waits AS W1GROUP BY W1.rn, W1.wait_type, W1.wait_time_ms, W1.signal_wait_time_ms, W1.resource_wait_time_ms, W1.waiting_tasks_count, W1.avg_wait_time_ms, W1.pctORDER BY W1.pct DESC OPTION (MAXDOP 1);

New query for collecting instance wait stats in SQL Server 2016 using sys.dm_os_wait_stats

Return Accumulated Wait Statistics for a Specific Session

--============================================================-- WAITS FOR A SPECIFIC SESSION--============================================================WITH Waits AS(SELECT SWS.wait_type, SWS.wait_time_ms, SWS.signal_wait_time_ms , (SWS.wait_time_ms - SWS.signal_wait_time_ms) AS resource_wait_time_ms, SWS.waiting_tasks_count , CASE SWS.waiting_tasks_countWHEN 0 THEN 0ELSE SWS.wait_time_ms/SWS.waiting_tasks_countEND AS avg_wait_time_ms, (100. * SWS.wait_time_ms) / SUM(SWS.wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY (SWS.wait_time_ms - SWS.signal_wait_time_ms) DESC) AS row_numFROM sys.dm_exec_session_wait_stats SWSWHERE SWS.wait_type NOT IN (-- filter out additional irrelevant waits'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT', 'KSOURCE_WAKEUP' , 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MISCELLANEOUS', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK', 'TRACEWRITE', 'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT', 'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_EVENTHANDLER', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE', 'HADR_NOTIFICATION_DEQUEUE', 'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL', 'HADR_TIMER_TASK', 'HADR_SYNC_COMMIT', 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'PREEMPTIVE_HADR_LEASE_MECHANISM','PREEMPTIVE_OS_GETFILEATTRIBUTES', 'PREEMPTIVE_OS_CREATEFILE', 'PREEMPTIVE_OS_FILEOPS', 'XE_LIVE_TARGET_TVF') AND SWS.session_id = )SELECT TOP 3 W.wait_type, CAST(W.wait_time_ms AS DECIMAL(20, 0)) AS wait_time_ms, CAST(W.signal_wait_time_ms AS DECIMAL(20, 0)) AS signal_wait_time_ms, CAST(W.resource_wait_time_ms AS DECIMAL(20, 0)) AS resource_wait_time_ms, W.waiting_tasks_count, W.avg_wait_time_ms, CAST(W.pct AS DECIMAL(5, 2)) AS pct, CAST(SUM(W.pct) OVER(ORDER BY (W.resource_wait_time_ms) DESC)AS DECIMAL(5,2)) AS running_pctFROM Waits AS WGROUP BY W.row_num , W.wait_type , W.wait_time_ms, W.signal_wait_time_ms, W.resource_wait_time_ms, W.waiting_tasks_count, W.avg_wait_time_ms , W.pctORDER BY W.pct DESC OPTION (MAXDOP 1);

Waits for a specific session in SQL Server 2016

You'll note the syntax for a Template Parameter to specify the session_id. This allows you to re-use the code and swap out that parameter by either using the option on the SSMS menu bar under "Query" or using the keyboard shortcut of Ctrl+Shift+M. More details about Template Parameters are available in the article here.

Return Accumulated Wait Statistics for All Session for a Specific Database

Things get a bit more involved here due to limitations around those windowing functions and the fact that by aggregating multiple sessions into this query we now have the possibility of duplicate wait_types.  This means adding one additional step in front of the aforementioned query to collect all the waits and work towards aggregation of wait_types to "eliminate" those duplicates from the remainder of the query.  There are two options for this: a temporary table or a table variable. I'm partial to temporary tables in this case.

--============================================================-- WAITS FOR A SPECIFIC DATABASE'S SESSIONS--============================================================IF NOT EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name LIKE '#WAITS_%')BEGINCREATE TABLE #WAITS(database_id sysname, wait_type nvarchar(60), waiting_tasks_count bigint, wait_time_ms bigint, max_wait_time_ms bigint, signal_wait_time_ms bigint) ENDELSE BEGINTRUNCATE TABLE #WAITS;ENDINSERT INTO #WAITS(database_id, wait_type, waiting_tasks_count, wait_time_ms , signal_wait_time_ms)SELECT eS.database_id, SWS.wait_type, SUM(SWS.waiting_tasks_count) AS waiting_tasks_count, SUM(SWS.wait_time_ms) AS wait_time_ms, SUM(SWS.signal_wait_time_ms) AS signal_wait_time_msFROM sys.dm_exec_session_wait_stats SWSINNER JOIN sys.dm_exec_sessions eSON SWS.session_id = eS.session_idWHERE SWS.wait_type NOT IN (-- filter out additional irrelevant waits'BROKER_TASK_STOP', 'BROKER_RECEIVE_WAITFOR', 'BROKER_TO_FLUSH', 'BROKER_TRANSMITTER', 'CHECKPOINT_QUEUE', 'CHKPT', 'DISPATCHER_QUEUE_SEMAPHORE', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT','FT_IFTS_SCHEDULER_IDLE_WAIT', 'KSOURCE_WAKEUP' , 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'MISCELLANEOUS', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'SLEEP_TASK', 'TRACEWRITE', 'SQLTRACE_BUFFER_FLUSH', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT', 'DIRTY_PAGE_POLL', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_EVENTHANDLER', 'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'HADR_WORK_QUEUE', 'HADR_NOTIFICATION_DEQUEUE', 'HADR_LOGCAPTURE_WAIT', 'HADR_CLUSAPI_CALL', 'HADR_TIMER_TASK', 'HADR_SYNC_COMMIT', 'PREEMPTIVE_SP_SERVER_DIAGNOSTICS', 'PREEMPTIVE_HADR_LEASE_MECHANISM','PREEMPTIVE_OS_GETFILEATTRIBUTES', 'PREEMPTIVE_OS_CREATEFILE', 'PREEMPTIVE_OS_FILEOPS', 'XE_LIVE_TARGET_TVF') AND DB_NAME(es.database_id) = ''AND eS.is_user_process = 1GROUP BY eS.database_id, SWS.wait_typeOPTION (MAXDOP 1);WITH Waits AS(SELECT SWS.database_id, SWS.wait_type, SWS.wait_time_ms, SWS.signal_wait_time_ms , (SWS.wait_time_ms - SWS.signal_wait_time_ms) AS resource_wait_time_ms, SWS.waiting_tasks_count , CASE SWS.waiting_tasks_countWHEN 0 THEN 0ELSE SWS.wait_time_ms/SWS.waiting_tasks_countEND AS avg_wait_time_ms, (100. * SWS.wait_time_ms) / SUM(SWS.wait_time_ms) OVER() AS pct, ROW_NUMBER() OVER(ORDER BY (SWS.wait_time_ms - SWS.signal_wait_time_ms) DESC) AS row_numFROM #WAITS SWS)SELECT TOP 3 DB_NAME(W.database_id) AS the_database, W.wait_type, CAST(W.wait_time_ms AS DECIMAL(20, 0)) AS wait_time_ms, CAST(W.signal_wait_time_ms AS DECIMAL(20, 0)) AS signal_wait_time_ms, CAST(W.resource_wait_time_ms AS DECIMAL(20, 0)) AS resource_wait_time_ms, W.waiting_tasks_count, W.avg_wait_time_ms, CAST(W.pct AS DECIMAL(5, 2)) AS pct, CAST(SUM(W.pct) OVER(ORDER BY (W.resource_wait_time_ms) DESC)AS DECIMAL(5,2)) AS running_pctFROM Waits AS WGROUP BY W.database_id, W.row_num , W.wait_type , W.wait_time_ms, W.signal_wait_time_ms, W.resource_wait_time_ms, W.waiting_tasks_count, W.avg_wait_time_ms , W.pctORDER BY W.pct DESC OPTION (MAXDOP 1);DROP TABLE #WAITS;GO

Note that we're inserting records into this temporary table (#WAITS) grouped by the database name (entered as a Template Parameter once again) and wait_type in order to "dedupe" those wait_types for processing through the rest of the query.

Waits for all sessions associated with a specific database in SQL Server 2016

Conclusion

What you can see from the results of the three queries above is that waits for a specific session or collection of sessions is not indicative of the overall waits for the instance itself. The same applies for the converse: instance wait statistics are not indicative (though likely are a factor) in what a specific session may be dealing with for a resource bottleneck.

With SQL Server 2016 we have some new challenges as well as opportunities to collect wait statistics to help guide us in performance tuning analysis. I expect refinements in this process as we approach release and perhaps the current issues we're seeing in the CTP will be resolved prior to go-live for SQL Server 2016. If so I'll be back here with an update to this article.