AdroitDBA

Book a Consultation

Monitor SQL Query Performance with DMVs

·

When a SQL Server “feels slow”, the question is always the same: which queries, and why?
SQL Server already knows. Its Dynamic Management Views (DMVs) record what is running right
now and how every cached query has performed since it was compiled. This guide shows four
queries we use on client servers to go from “it’s slow” to a short list of statements worth
tuning.

1. What is running right now?

Start with the live picture. sys.dm_exec_requests lists every request currently executing.
Joining it to the statement text and the session tells you who is running what, for how long,
and what it is waiting on.

SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    DB_NAME(r.database_id)          AS database_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time                     AS wait_ms,
    r.blocking_session_id,
    r.total_elapsed_time / 1000     AS elapsed_seconds,
    r.cpu_time                      AS cpu_ms,
    r.logical_reads,
    SUBSTRING(t.text,
        r.statement_start_offset / 2 + 1,
        (CASE r.statement_end_offset
             WHEN -1 THEN DATALENGTH(t.text)
             ELSE r.statement_end_offset
         END - r.statement_start_offset) / 2 + 1) AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

How to read it:

  • blocking_session_id is not 0: the request is stuck behind another session. Look at
    that session first; the slow query may be the victim, not the cause.
  • elapsed_seconds much larger than cpu_ms: the query spends most of its time waiting.
    wait_type says on what (PAGEIOLATCH_* = reading from disk, LCK_M_* = locks,
    CXPACKET/CXCONSUMER = parallelism).
  • High cpu_ms and logical_reads: the query is doing a lot of work, often because of a
    missing index or a poor plan.

2. Which queries cost the most over time?

A query that runs for 50 milliseconds but executes 200,000 times a day can hurt more than a
single 30-second report. sys.dm_exec_query_stats keeps running totals for every cached
plan, so you can rank queries by their total and average cost.

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000                          AS total_cpu_ms,
    qs.total_worker_time / qs.execution_count / 1000     AS avg_cpu_ms,
    qs.total_elapsed_time / qs.execution_count / 1000    AS avg_elapsed_ms,
    qs.total_logical_reads / qs.execution_count          AS avg_logical_reads,
    qs.last_execution_time,
    DB_NAME(t.dbid)                                      AS database_name,
    SUBSTRING(t.text,
        qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset
             WHEN -1 THEN DATALENGTH(t.text)
             ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1)      AS statement_text,
    qs.plan_handle
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.total_worker_time DESC;

Change the ORDER BY to match the problem:

Symptom Sort by
CPU pegged total_worker_time DESC
Disk / memory pressure total_logical_reads DESC
Users complain a specific screen is slow avg_elapsed_ms DESC

The times in this DMV are stored in microseconds, which is why the query divides by 1,000.

3. Why is that query slow? Read its plan

Once you have a suspect, its execution plan shows how SQL Server is running it. Copy the
plan_handle from the previous result into this query:

SELECT qp.query_plan
FROM sys.dm_exec_query_plan(0x...) AS qp;   -- paste the plan_handle here

In SQL Server Management Studio, click the XML in the result to open the graphical plan.
Look for:

  • Missing index warnings (green text at the top of the plan). Treat them as hints, not
    orders: review them against the indexes the table already has before creating anything.
  • Scans on large tables where you expected a seek.
  • Thick arrows (many rows) going into a sort, hash, or key lookup.
  • Big gaps between estimated and actual rows, a sign of stale statistics or parameter
    sniffing.

4. Know the limits of the plan cache

The DMVs above only see plans that are still in the plan cache. They forget everything after
a restart, a failover, a plan recompile, or memory pressure. So “nothing shows up” does not
mean “nothing was slow last night”.

On SQL Server 2016 and later, turn on Query Store for databases you care about. It keeps
query performance history on disk, survives restarts, and lets you compare a query’s plans
over time:

ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON;

Then use the built-in reports in SSMS (database → Query Store → Top Resource Consuming
Queries) to see what was slow at a specific time.

A simple routine

  1. When users report slowness, run query 1 to see what is running and what it is waiting on.
  2. Once a week, run query 2 to catch the expensive queries nobody complained about yet.
  3. Read the plan for the top two or three, fix one thing at a time, and measure again.

Done consistently, this catches most performance problems before they turn into outages.
It is also exactly the kind of weekly check our DBAs run for clients.

More posts