TABLE OF CONTENTS
When someone says “the system is slow”, the first job is to find which queries the slowness comes from. SQL Server already keeps the information you need: the requests running right now, the accumulated statistics of queries in the plan cache, and the history in Query Store. With the right queries, you can see within minutes what consumes most of the server’s time and resources.
This guide shows how to find slow queries on three time scales: right now, over the period the cache covers, and in the past. It also explains what each query shows and what it can’t.
Which query is slowing your system down?
Aryasoft finds your most expensive queries, analyzes their execution plans and makes lasting improvements at index, query and configuration level.
1. What’s Running Right Now?
If users are experiencing slowness right now, the running requests are the first place to look. They show how long each has been running, what it’s waiting on and who’s blocking it:
SELECT r.session_id, r.status, r.command,
r.total_elapsed_time / 1000 AS elapsed_sec,
r.cpu_time / 1000 AS cpu_sec,
r.logical_reads,
r.wait_type, r.wait_time, r.blocking_session_id,
DB_NAME(r.database_id) AS database_name,
s.login_name, s.host_name, s.program_name,
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 statement_text
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;Rows with blocking_session_id filled in are blocked requests; find the session at the head of the chain (the head blocker). wait_type tells you what the request is waiting on: LCK_* for locks, PAGEIOLATCH_* for disk, CXPACKET for parallelism, and CPU if it’s empty with status = running.
2. The Plan Cache: The Most Expensive Queries
sys.dm_exec_query_stats keeps the execution count and total CPU, duration and reads for every query in the plan cache. The “most expensive” query isn’t the slowest single execution but the one that consumes the most resources in total; a 50-millisecond query running a hundred times a second can cost more than a five-minute report that runs once a day.
-- Top 20 queries by total CPU (swap the ORDER BY for total_logical_reads or total_elapsed_time)
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_duration_ms,
qs.total_logical_reads / qs.execution_count AS avg_reads,
qs.last_execution_time,
DB_NAME(st.dbid) AS database_name,
SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;Grouping ad hoc queries
Queries with values embedded in the text create a separate cache entry for every value, and each one looks insignificant on its own. Grouping by query_hash reveals the combined impact of queries with the same pattern:
SELECT TOP (20)
qs.query_hash,
COUNT(*) AS distinct_plans,
SUM(qs.execution_count) AS executions,
SUM(qs.total_worker_time) / 1000 AS total_cpu_ms,
SUM(qs.total_logical_reads) AS total_reads,
MIN(SUBSTRING(st.text, 1, 200)) AS sample_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY qs.query_hash
ORDER BY total_cpu_ms DESC;Warning
Queries based on the plan cache only see plans currently in the cache. The history is gone after a service restart, under memory pressure, after statistics updates, or when someone runs DBCC FREEPROCCACHE. Queries using OPTION (RECOMPILE) never stay in the cache at all. When reading the results, look at last_execution_time and how long the server has been running.
3. History: Query Store
The answer to “what happened yesterday at 3 p.m.?” isn’t in the plan cache; it’s in Query Store. Query Store keeps queries, their plans and statistics in time intervals, and shows when a query slowed down and whether its plan changed. See our Query Store guide for setup and queries.
4. Context: What Is the Server Waiting On?
Before looking at individual queries, seeing what the server waits on overall points the search in the right direction:
SELECT TOP (10) wait_type,
wait_time_ms / 1000 AS wait_sec,
waiting_tasks_count,
CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS decimal(5, 1)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'CHECKPOINT_QUEUE', N'LOGMGR_QUEUE',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAITFOR', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT',
N'ONDEMAND_TASK_QUEUE', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'XE_LIVE_TARGET_TVF')
AND wait_time_ms > 0
ORDER BY wait_time_ms DESC;These values accumulate from service start. If PAGEIOLATCH_* leads, focus on reducing reads (indexes, query design) and on the disk; if CXPACKET leads, on parallelism settings; if LCK_* leads, on blocking and transaction design.
5. What Next?
- Open the plan of the most expensive query (the
query_plancolumn). Large table scans, warnings (yellow exclamation marks) and big gaps between estimated and actual row counts are the first clues. - Look for non-SARGable predicates: a column wrapped in a function, implicit type conversions, LIKE with a leading wildcard.
- Don’t apply missing index suggestions blindly; evaluate them together with the existing indexes. See our index guide.
- Repeat the same measurement after the change; confirm the improvement with numbers.
Tip
For a real example, see our public sector success story: the query that locked CPU at 100% after a new release was found with this approach within minutes, and a view fix plus a single filtered index brought CPU down to 5%.
Checklist
- For live slowness, running requests and blocking were checked with sys.dm_exec_requests.
- The most expensive queries in the plan cache were listed by total CPU, reads and duration.
- Ad hoc queries were grouped by query_hash.
- Query Store is on and used for history.
- Wait statistics and query findings were assessed together.
Related Reading
SQL Server Performance Consulting
Find the source of slowness by measuring, not guessing
Aryasoft’s senior DBA team analyzes your most expensive queries and wait statistics, and applies lasting, measured improvements at index, query and configuration level.
