Monitoring Query Performance with Dynamic Management Views (DMVs)
In this we will learn how to monitor Query Performance with Dynamic Management Views (DMVs).
Monitoring query performance
Slow or long running queries can consume significant system resources. This section demonstrates how to use dynamic management views to detect a few common query performance problems.
Finding top N queries
The following example returns information about the top five queries ranked by average CPU time. However, this example aggregates the queries according to their query hash. That is to say, logically equivalent queries are grouped by their cumulative resource consumption.
SQL
SELECT TOP 5 query_stats.query_hash AS “Query Hash”,
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS “Avg CPU Time”,
MIN(query_stats.statement_text) AS “Statement Text”
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
– QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
Monitoring blocked queries
Slow or long-running queries can contribute to excessive resource consumption and be the consequence of blocked queries. The cause of the blocking can be poor application design, bad query plans, the lack of useful indexes, and so on. However, you can use the sys.dm_tran_locks view to get information about the current locking activity in database.
Monitoring query plans
An inefficient query plan also may increase CPU consumption. However, the following example uses the sys.dm_exec_query_stats view to determine which query uses the most cumulative CPU.
SQL
SELECT
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]FROM
(SELECT TOP 50
qs.plan_handle,
qs.total_worker_time
FROM
sys.dm_exec_query_stats qs
ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;
Reference: Microsoft Documentation