First thing first, this feature of getting wait stats related to Specific Session ID with sys.dm_exec_session_wait_stats will only work with SQL Server 2016 or later version of SQL Server. If you attempt to run this on earlier versions of SQL Server, you will for sure get an error.
Earlier on this blog post I wrote a 28 day series about how SQL Server Wait Stats works. If you have not caught up on SQL Server Wait Stats or have limited understanding of how it works, I think it is a great time for you to get started with this concept.
Here is the link of all the blog posts which I have earlier written about SQL Wait Stats: Wait Stats – Wait Types – Wait Queues – Day 0 of 28. Now today’s blog post is actually new thread in the same series, which works for SQL Server 2016.
As of SQL Server 2016, now we can also get with stats details for every single session, which is running on your SQL Server as well. Here is the script for the same.
SELECT * FROM sys.dm_exec_session_wait_stats
When I ran above script on my system, it gave me following results.
Please note that the details of any specific session will be reset if SQL Server resets the connection or opens the same connections again.
I use this particular feature quite a lot in my Comprehensive Database Performance Health Check service when we have to identify which particular session is facing the most bottlenecks. It is extremely useful DMV and I really wished that it was available to all of us earlier.
Reference: Pinal Dave (https://blog.sqlauthority.com)
