How to Find Slow Queries in SQL Server (DMVs and Query Store)

Find the slow queries in SQL Server fast: ready-to-run DMV scripts for running queries, top CPU and reads, wait stats, plus Query Store reports.    Before you can tune anything, you need to know which queries are the problem. Users report "the system is slow", but the cause is usually a handful of queries. SQL Server already tracks everything you need to find them. This guide gives you the scripts I use first, in the order I use them.

1. What is running right now?

When the system is slow at this moment, start with live requests. This shows each running query, how long it has run, what it is waiting on and whether it is blocked:

SELECT
    r.session_id,
    r.status,
    r.start_time,
    DATEDIFF(SECOND, r.start_time, SYSDATETIME()) AS running_seconds,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    DB_NAME(r.database_id) AS database_name,
    t.text AS query_text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
  AND r.session_id > 50
ORDER BY r.start_time;

• A non-zero blocking_session_id means the query is waiting for another session's locks.
• wait_type tells you what it is waiting for, such as locks (LCK_M_*), disk (PAGEIOLATCH_*) or parallelism (CXPACKET/CXCONSUMER).

2. Which queries cost the most overall?

For general slowness, the most useful view is total cost across all executions. A 50 ms query that runs 100,000 times an hour usually matters more than one 30-second report that runs once a day.

-- Top 10 queries by total CPU since they were cached
SELECT TOP (10)
    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_logical_reads / qs.execution_count        AS avg_reads,
    qs.total_elapsed_time / qs.execution_count / 1000  AS avg_duration_ms,
    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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDER BY qs.total_worker_time DESC;

Change the ORDER BY to qs.total_logical_reads DESC to find the queries that read the most data, which are often the ones missing an index.

Keep in mind that this view only covers plans still in cache. It resets on restart and loses queries whose plans were evicted, which is why Query Store is better for history.

3. Use Query Store for history and regressions

Query Store records query performance and plans over time inside the database. It is on by default in Azure SQL Database and for new databases in SQL Server 2022. In SSMS, open the database, expand Query Store, and use:

• Top Resource Consuming Queries: the heaviest queries over any time window you pick.
• Regressed Queries: queries that got slower, usually because their plan changed.
• Queries With High Variation: queries whose duration swings a lot, a common sign of parameter sniffing.

The big advantage is history: you can compare "today, when it was slow" with "last week, when it was fine".

4. Check what the whole server waits on

Wait statistics show where SQL Server spends its waiting time overall, which points you to the type of problem:

SELECT TOP (10)
    wait_type,
    wait_time_ms / 1000.0 AS wait_seconds,
    waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SLEEP%'
  AND wait_type NOT LIKE '%QUEUE%'
  AND wait_type NOT IN ('BROKER_TASK_STOP','CHECKPOINT_QUEUE','WAITFOR',
                        'XE_TIMER_EVENT','DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION')
ORDER BY wait_time_ms DESC;

• PAGEIOLATCH_*: reading data from disk. Often too many reads, so look for missing indexes or scans.
• LCK_M_*: blocking. Look at long transactions and locking patterns.
• SOS_SCHEDULER_YIELD with high CPU: CPU-heavy queries.
• WRITELOG: transaction log writes. Look at many tiny commits or slow log storage.

Wait stats accumulate since the last restart, so compare snapshots taken a few minutes apart for a picture of current load. This filter list is a starting point; there are more harmless background waits you may want to exclude.

5. Pick one query and fix it

Once you have a shortlist, take the query with the biggest total impact, get its actual execution plan, and look for scans on large tables, key lookups, bad row estimates and warnings. Fix one thing at a time, and compare logical reads and CPU before and after.

Quick routine

1. Slow right now? Check sys.dm_exec_requests for blocking and waits.
2. Slow in general? Rank queries by total CPU and reads.
3. Slower than last week? Use Query Store's Regressed Queries.
4. Check wait stats to understand the type of bottleneck.
5. Tune the top query, then measure again.

Related reading:

• SQL Server Query Store: A Practical Beginner's Guide
• How to Read a SQL Server Execution Plan
• SQL Server Index Tuning for Beginners