DMV in SQL Server 2016 CTP – sys.dm_exec_session_wait_stats

Hi Friends,

Wait stats, I hope all of us who are working with SQL Server are aware about this thing. SQL Server DBA generally uses sys.dm_os_wait_stats DMV in SQL Server to collect the data about waits. This collection is very useful for troubleshooting of various performance problems. This DMV provides the cumulative information of wait for the complete instance.

Sometimes we need a simple mechanism to collect the information of waits only for a specific session. SQL Server 2016 CTP introduces a new DMV to provide the session level waits information i.e. sys.dm_exec_session_wait_stats. By using this DMV in SQL Server, now it is very simple to collect the total waits and type of wait for a specific session.

Let me show you the same practical. Here I am using SQL Server 2016 CTP 3.3 and AdventureWorks2014 database. I have created a copy of the SalesOrderDetail table with some data. Now I am going to alter one of the columns and want to collect the details about wait types which occurred due to this alter column operation. you can take the code from my previous post by clicking here.

Session 1: for me the session id is 56

dm_exec_session_wait_stats

   

Session 2: for me the session id is 52

Select * from sys.dm_exec_session_wait_stats where session_id=56

dm_exec_session_wait_stats

From the above image you can see that sys.dm_exec_session_wait_stats DMV provide us the complete details about wait occurred during alter column operation. The collected columns are almost same as sys.dm_os_wait_stats DMV with one extra column i.e. session_id. This DMV will be really helpful for performance troubleshooting of various operations.

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook

   

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published.