New Dynamic Management Views in SQL Server 2016 Part 1

We're getting close to the release of the next version of Microsoft SQL Server and that means new Dynamic Management Views and Functions for me to explore.  I'll be bringing you along for the ride on this adventure over the course of this series.  This first episode covers what's new through CTP 2.1 and looks at what I expect will be one of the DMVs that make the final cut when SQL Server 2016 is finally released: dm_os_session_wait_stats. 

It is important to understand that a CTP is not the final product and things may change (and likely will) between now and release.  I do my best to keep this series updated with those changes as they pertain to the Dynamic Management Objects as we proceed to release time.

DMVs Not Returning in SQL 2016

In a side-by-side comparison between SQL Server 2014 and SQL Server 2016 CTP 2.1 we have only a single DMV that is being dropped from the next release: sys.dm_db_xtp_merge_requests.  This DMV currently provides information related to database merge requests by end users or SQL Server internally.

DMVs New in SQL Server 2016 CTP 2.1

That is not a typographic error - there are no new Dynamic Management Functions in SQL Server 2016 CTP 2.1 - just Dynamic Management Views - and at that there are only 16 new ones to (currently) contend with:

While we'll look at each one of these over the course of this series right now I want to focus on the last one as it appears the most-attractive to a performance metadata geek like me.  Nothing gets the blood flowing like the phrase "wait stats".

dm_exec_session_wait_stats - Part 1

This DMV provides similar information to what the current dm_os_wait_stats DMV provides - metadata related to cumulative resource and cpu waits for all activity on a SQL Server instance since the last time that either the wait statistics were manually purged or since the SQL services were last restarted (whichever occurred most recently.) 

What differentiates this DMV from dm_os_wait_stats is the fact that it also partitions the waits by user|system session_id.  Matter of fact that is the only difference between the two DMVs.  You may initially think that this is a fantastic new resource for dialing-in on troubleshooting performance (and it can be) but at the same time don't expect it to be able to solve all your initial performance tuning analysis requirements and there are a few reasons why:

It's Probably Not Suited for Non-Persisted Sessions

Not all applications maintain sessions with their databases. Unless you're dealing with sessions (or groups of sessions) for an application that maintains a persisted connection to your SQL Server you're not going to see much value from the additional partitioning of wait statistics based upon session_id.  When you receive that "my database is slow" call from an end user you're going to be hard-pressed to use dm_exec_session_wait_stats to target your troubleshooting when that session no longer exists.  This is similar to the reasons I avoid using dm_exec_locks to troubleshoot blocking issues: the foundation of the data is constantly shifting underneath your feet.  By the time you obtain results they are already obsolete. 

If you are dealing with analyzing issues related to a state-maintained application this DMV shows promise.  You're able to join from dm_exec_session_wait_stats to dm_exec_sessions to determine the session(s) for a particular application or determine the specific session you're interested in troubleshooting based upon columns in that "parent" DMV:

Connect to dm_exec_sessions on session_id=session_id and then use dm_exec_sessions.host_name or program_name for filtering.

You can then do something akin to the following query that will return all session-based wait information for a specific application:

SELECT eSWS.wait_type, SUM(eSWS.wait_time_ms) - SUM(eSWS.signal_wait_time_ms) AS resource_wait_time_ms, SUM(eSWS.signal_wait_time_ms) AS signal_wait_time_ms, SUM(eSWS.wait_time_ms) AS wait_time_ms, MAX(eSWS.max_wait_time_ms) AS max_wait_time_ms, SUM(eSWS.waiting_tasks_count) AS waiting_tasks_countFROM sys.dm_exec_session_wait_stats AS eSWSINNER JOIN sys.dm_exec_sessions AS eS ON eSWS.session_id = eS.session_idWHERE eS.program_name =''GROUP BY eSWS.wait_typeORDER BY 4 DESC;

This leads to the same situation you encounter with the original dm_os_wait_stats DMV: you're dealing with cumulative metadata.

Waits are Cumulative for All Types of Activity Periods

This is one of the reasons I always recommend "time-slicing" wait stats collection for performance tuning.  This involves creating a physical table (usually in a database dedicated to all of your administrative tasks and only accessible to the DBAs) for insertion of regularly scheduled collections of the results from a particular DMV with an associated timestamp (and sometimes a unique identity id) for later analysis.  This allows you to look at the deltas between two points in time to see what the state of affairs was at a specific range in time.  It also allows you to keep this information around for trend analysis until a point in time where you feel it's acceptable to purge. 

When dealing with wait statistics in particular this is a better option than querying the dm_os_wait_stats or dm_exec_session_wait_stats DMVs directly since both of these DMVs collect information since time of the last flush of their statistics with will happen at each service restart or manually so that means through the course of system up-time the statistics are reflecting the state of waits at all types of system load: normal activity, backup periods, during index fragmentation remediation and so forth.  The waits encountered during each of these phases will be different and would lead you to act differently based upon the results returned.  For instance during periods of index maintenance activity you might see elevated latch waits that you'd not see during normal activity; or backup and log waits encountered during backup periods.  This is akin to a blind man touching the trunk of an elephant and exclaiming he's dealing with a snake while a different blind man at the opposite end of the same elephant may think he's dealing with, well, something rather stinky and non-snakelike.

Summary of Part 1

In this introduction to the new DMVs you've seen what is to come (and go) as well as get a taste of the first DMV we will be looking at: dm_exec_session_wait_stats.  In Part 2 of this series we'll cover: