SpDev Install free

SQL Server performance tuning: find the slow query before you fix it

A method for SQL Server performance tuning: start from waits, find the queries that cost the most with Query Store and DMVs, fix them one at a time, and prove each fix.

By Areeb Ahmer, . 4 minute read.

Performance tuning goes wrong in a predictable way: someone has a theory (“it needs more memory”, “rebuild the indexes”, “it’s the new release”), acts on it, and the problem stays. Good tuning is less about knowing tricks and more about following evidence, in order:

  1. What is the server waiting on?
  2. Which queries cause most of that?
  3. Why is each of them slow?
  4. Did the fix work?

The queries below are standard DMV and Query Store queries. They need VIEW SERVER STATE (or VIEW SERVER PERFORMANCE STATE on SQL Server 2022) and VIEW DATABASE STATE.

1. What is the server waiting on?

Every time a query can’t proceed (for a page from disk, a lock, CPU time, memory), SQL Server records a wait. The totals since the last restart tell you what kind of problem you have:

SELECT TOP (10)
    wait_type,
    wait_time_ms / 1000                     AS wait_seconds,
    waiting_tasks_count,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
  AND wait_type NOT LIKE N'%SLEEP%'
  AND wait_type NOT LIKE N'%QUEUE%'
  AND wait_type NOT LIKE N'BROKER[_]%'
  AND wait_type NOT LIKE N'XE[_]%'
  AND wait_type NOT LIKE N'PWAIT[_]%'
  AND wait_type NOT LIKE N'QDS[_]%'
  AND wait_type NOT IN (N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'WAITFOR', N'DISPATCHER_QUEUE_SEMAPHORE',
                        N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'SOS_WORK_DISPATCHER',
                        N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'AZURE_IMDS_VERSIONS', N'PVS_PREALLOCATE',
                        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'SQLTRACE_BUFFER_FLUSH', N'SP_SERVER_DIAGNOSTICS_SLEEP')
ORDER BY wait_time_ms DESC;

The filters remove the background waits SQL Server’s own idle threads accumulate. What the top waits usually mean:

Wait Usually means
PAGEIOLATCH_SH, PAGEIOLATCH_EX Reading pages from disk: queries read more than fits in memory, often because of scans
CXPACKET, CXCONSUMER Parallel queries; often big scans, sometimes a parallelism setting worth checking
SOS_SCHEDULER_YIELD CPU pressure: queries doing a lot of work in memory
LCK_M_* Blocking: sessions waiting for locks held by others
WRITELOG Waiting for the transaction log to be written: slow log disk or many tiny transactions
RESOURCE_SEMAPHORE Queries waiting for a memory grant
ASYNC_NETWORK_IO SQL Server is waiting for the client to read its results: the app, not the database

Notice that most of these point back at queries: scans cause disk reads, parallelism and CPU; long transactions cause blocking. That’s why step 2 matters most.

2. Which queries cost the most?

Query Store (on by default for new databases on SQL Server 2022) keeps per-query statistics across restarts. The heaviest queries of the last day, by total CPU:

SELECT TOP (10)
    q.query_id,
    OBJECT_NAME(q.object_id)                         AS procedure_name,
    SUM(rs.count_executions)                         AS executions,
    SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms,
    SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_reads,
    MAX(qt.query_sql_text)                           AS query_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE i.start_time >= DATEADD(DAY, -1, SYSUTCDATETIME())
GROUP BY q.query_id, q.object_id
ORDER BY total_cpu_ms DESC;

Without Query Store, the plan cache has similar numbers since each plan was cached:

SELECT TOP (10)
    DB_NAME(t.dbid)                       AS database_name,
    OBJECT_NAME(t.objectid, t.dbid)       AS procedure_name,
    qs.execution_count,
    qs.total_worker_time / 1000           AS total_cpu_ms,
    qs.total_logical_reads,
    qs.total_elapsed_time / 1000          AS total_elapsed_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 AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.total_worker_time DESC;

Sort by total cost, not average. A query that takes 20 ms but runs 50,000 times an hour costs more than a 30-second report run once a day, and is usually easier to fix.

3. Why is it slow?

Take the top query and look at its actual execution plan (how to read one). The usual causes, roughly in order of frequency:

  • A missing or unusable index: scans that read far more than they return (indexes).
  • Non-sargable predicates and implicit conversions that prevent seeks (optimization checklist).
  • Bad estimates from stale statistics, parameter sniffing or table variables.
  • Row-by-row processing in cursors and loops.
  • Blocking: the query is fine but waits on locks held by a long transaction.

For a stored procedure, first find which of its statements is the problem. sys.dm_exec_query_stats has one row per statement, and Query Store groups statements by object_id; filter on the procedure to see its statements side by side.

4. Did the fix work?

Measure the same thing before and after, ideally logical reads (SET STATISTICS IO ON), which don’t depend on what else the server is doing. Then watch the query’s numbers in Query Store for a few days: a fix that helps one parameter value can hurt another.

Keep a note of what you changed and why. Six months later, someone will want to drop “that unused index”.

What not to do first

  • Restart the server or clear the plan cache. It hides the evidence and fixes nothing that won’t come back.
  • Add memory or CPU. Sometimes right, but a scan that reads 10 GB is still a scan with more RAM.
  • Rebuild every index nightly. Fragmentation rarely matters on modern storage; keeping statistics up to date usually matters more.
  • Apply hints you found online. NOLOCK, MAXDOP 1 and FORCE ORDER each fix one problem by causing another.

SpDev Pro is built for step 3 on stored procedures: it measures every statement of a procedure on your development database, captures the actual plans of the expensive ones and lists what’s wrong with them on the right lines. Step 4 is built in too: every run is kept, and the next one is compared with it statement by statement.