This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced dm_exec_session_wait_stats dm view to check the wait stats of your session level.
You should know about the dm_os_wait_stats dm view which uses for checking wait types and wait stats for server level. But from SQL Server 2016, you can check the same information for your sessions.
Very important, WHY?
As a SQL Server Database Developer or DBA, you should not write/optimize your session query blindly. You must check different wait types of your session which help you lot to optimize your query in a better way.
Once you close your session, wait stats of your session will remove so you can close/open your session for checking the required wait statistics.
First, check the SPID of your session:
|
1 |
SELECT @@SPID |
Use below query and filter data with your session_id:
|
1 2 3 4 5 6 7 8 9 |
SELECT session_id ,wait_type ,waiting_tasks_count ,wait_time_ms ,max_wait_time_ms ,signal_wait_time_ms FROM sys.dm_exec_session_wait_stats WHERE session_id = 58 |
Sample Result:
|
1 2 3 4 5 6 |
session_id wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms ---------- ------------------------------- -------------------- -------------------- -------------------- -------------------- 58 PAGEIOLATCH_SH 1 17 17 0 58 SOS_SCHEDULER_YIELD 1 0 0 0 58 MEMORY_ALLOCATION_EXT 505 0 0 0 58 RESERVED_MEMORY_ALLOCATION_EXT 19 0 0 0 |
Leave a Reply