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
Session 2: for me the session id is 52
Select * from sys.dm_exec_session_wait_stats where session_id=56
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
