SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type

SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : http://blog.sqlauthority.com/2011/02/04/sql-server-dmv-sys-dm_os_waiting_tasks-and-sys-dm_exec_requests-wait-type-day-4-of-28/

SQL SERVER – DMV – sys.dm_os_waiting_tasks and sys.dm_exec_requests – Wait Type 

Previously, we covered the DMV sys.dm_os_wait_stats, and also saw how it can be useful to identify the major resource bottleneck. However, at the same time, we discussed that this is only useful when we are looking at an instance-level picture. Quite often we want to know about the processes going in our server at the given instant. Here is the query for the same.

This DMV is written taking the following into consideration: we want to analyze the queries that are currently running or which have recently ran and their plan is still in the cache.

SELECT dm_ws.wait_duration_ms,dm_ws.wait_type,dm_es.status,dm_t.TEXT,dm_qp.query_plan,dm_ws.session_ID,dm_es.cpu_time,dm_es.memory_usage,dm_es.logical_reads,dm_es.total_elapsed_time,dm_es.program_name,DB_NAME(dm_r.database_id) DatabaseName,-- Optional columnsdm_ws.blocking_session_id,dm_r.wait_resource,dm_es.login_name,dm_r.command,dm_r.last_wait_typeFROM sys.dm_os_waiting_tasks dm_wsINNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_idINNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_idCROSS APPLY sys.dm_exec_sql_text (dm_r.sql_handle) dm_tCROSS APPLY sys.dm_exec_query_plan (dm_r.plan_handle) dm_qpWHERE dm_es.is_user_process = 1
GO

You can change CROSS APPLY to OUTER APPLY if you want to see all the details which are omitted because of the plan cache.

Let us analyze the result of the above query and see how it can be helpful to identify the query and the kind of wait type it creates.

Click to Enlarage

The above query will return various columns. There are various columns that provide very important details. e.g.

wait_duration_ms – it indicates current wait for the query that executes at that point of time.

wait_type – it indicates the current wait type for the query

text – indicates the query text

query_plan – when clicked on the same, it will display the query plans

There are many other important information like CPU_time, memory_usage, and logical_reads, which can be read from the query as well.

In future posts on this series, we will see how once identified wait type we can attempt to reduce the same.