In a previous article, we discussed the usefulness of the DMV sys.dm_os_wait_stats in identifying major resource bottlenecks at the instance level. However, there are times when we need to analyze the processes running on our server at a given instant. This article will provide a query that allows us to do just that.
Here is the query:
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) AS DatabaseName,
dm_ws.blocking_session_id, dm_r.wait_resource, dm_es.login_name, dm_r.command, dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
CROSS APPLY sys.dm_exec_sql_text(dm_r.sql_handle) dm_t
CROSS APPLY sys.dm_exec_query_plan(dm_r.plan_handle) dm_qp
WHERE dm_es.is_user_process = 1
In the above query, we are analyzing the queries that are currently running or have recently run and their plans are still in the cache. By executing this query, we can obtain important information about the processes and their associated wait types.
Some of the columns returned by the query include:
- wait_duration_ms: Indicates the current wait for the query at the time of execution.
- wait_type: Indicates the current wait type for the query.
- text: Displays the query text.
- query_plan: When clicked, displays the query plans.
Additionally, there are other important details such as CPU_time, memory_usage, and logical_reads that can be extracted from the query results.
In future posts in this series, we will explore how to reduce identified wait types. Stay tuned!
Read all the posts in the Wait Types and Queue series for more information.