Most SQL Server performance tuning advice starts with a list of tactics. Add indexes, rewrite queries, buy faster storage. That ordering fails because it skips the first question: what is the server waiting on?
A query can be slow for three reasons that look identical from the application. The optimizer chose a bad plan. The server ran out of memory, or CPU, or worker threads. Another session is holding a lock. Each has a different fix, and applying the wrong one wastes a maintenance window.
Start with wait statistics
SQL Server tracks every reason a thread stopped running.
SELECT TOP (10)
wait_type,
wait_time_ms / 1000.0 AS total_wait_sec,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS resource_wait_sec,
signal_wait_time_ms / 1000.0 AS signal_wait_sec,
waiting_tasks_count,
CAST(100.0 * wait_time_ms
/ NULLIF(SUM(wait_time_ms) OVER (), 0) AS DECIMAL(5,2)) AS pct_of_total
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
AND wait_type NOT IN (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'SQLTRACE_BUFFER_FLUSH',
N'WAITFOR', N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'BROKER_TO_FLUSH',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT', N'CLR_AUTO_EVENT',
N'DISPATCHER_QUEUE_SEMAPHORE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'XE_DISPATCHER_WAIT', N'XE_DISPATCHER_JOIN', N'ONDEMAND_TASK_QUEUE',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'BROKER_EVENTHANDLER',
N'SLEEP_BPOOL_FLUSH', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'DIRTY_PAGE_POLL',
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'QDS_ASYNC_QUEUE',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE',
N'PARALLEL_REDO_WORKER_WAIT_WORK', N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'BROKER_RECEIVE_WAITFOR', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRRORING_CMD', N'CXCONSUMER'
)
ORDER BY wait_time_ms DESC;
These counters accumulate from the last service restart, so on a server with 400 days of uptime they describe history rather than this morning. Clear them and wait an hour when you want a current picture:
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
Read two columns. Resource wait is time spent waiting for the thing itself. Signal wait is time spent after the resource freed up, queued for a CPU. When signal wait climbs past a quarter of the total, start looking at CPU scheduling, and confirm it against runnable queue length in sys.dm_os_schedulers rather than treating the ratio as proof.
Reading the top wait
| Wait type | What stopped the thread | Where to look |
|---|---|---|
PAGEIOLATCH_SH, PAGEIOLATCH_EX |
Reading data pages from disk into the buffer pool | Usually a plan reading far more data than it needs. Check the queries before you blame storage. |
RESOURCE_SEMAPHORE |
Query waiting for a memory grant | Bad row estimates producing oversized grants. One query can starve the rest. A one-row estimate against a large set is the classic source: why a table variable estimates one row. |
WRITELOG |
Waiting for a log flush to complete | Log file storage latency, or an application committing row by row. Also what a WRITELOG wait is telling you about the log file. |
SOS_SCHEDULER_YIELD |
Thread ran its full 4ms quantum and yielded the scheduler | Large scans running in memory. This wait does not indicate CPU pressure on its own, whatever its rank. Diagnose CPU from signal wait and runnable queue length. |
LCK_M_S, LCK_M_X, LCK_M_U |
Blocked by another session's lock | A concurrency problem. Tuning the blocked query fixes nothing. |
THREADPOOL |
No worker threads left | Most often a blocking chain that has consumed the pool. Runaway parallelism and a low max worker threads setting produce it too. Treat as an emergency. |
ASYNC_NETWORK_IO |
Network buffer full, client has not drained it mid-stream | Application side. Often a client pulling a result set one row at a time. |
PAGELATCH_UP on tempdb |
Allocation bitmap contention in tempdb | File count and sizing. On 2019 and later, consider memory-optimized tempdb metadata. |
PAGEIOLATCH at the top is the one most teams misread. It looks like a storage complaint, so the on-call escalates it to the storage team. In practice a query scanning 40 million rows to return 12 produces the same wait as a slow SAN. One of those is a rewrite. The other is a purchase order.
Sort the top wait into one of the three categories before you touch anything. PAGEIOLATCH, RESOURCE_SEMAPHORE and SOS_SCHEDULER_YIELD point at a plan reading or requesting more than it should, and the next three sections are where you take them. LCK_M_* and THREADPOOL say the query you were handed is a victim, so go and find the session at the head of the chain instead. WRITELOG and PAGELATCH_UP on tempdb are the resource branch, and they point at files and file counts rather than at any statement. Nothing further down this page applies until you know which of the three you have.
A single top wait is a shortlist, not a diagnosis. Take the top two or three and look at what share of accumulated wait time each one holds: a leader sitting at 6% of the total says the instance is not blocked on anything in particular, and the answer is in the plan cache rather than in this view. Then scope the numbers to the complaint you were called about. Clear the counters, have somebody reproduce the slow operation, and read the view again, so the top wait belongs to that workload rather than to 400 days of overnight batch jobs.
On the concurrency branch the next query is the chain, not the statement you were handed:
SELECT session_id, blocking_session_id, wait_type, wait_time, wait_resource
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
Follow blocking_session_id back until you reach a session that is not itself blocked. That one is the subject. Everything else on the list is queued behind it, and tuning any of them changes nothing.
Measure before you change anything
Logical reads are the unit. One logical read is one 8KB page read from the buffer cache, whether or not it required physical I/O, and the count stays stable across runs in a way that elapsed time does not.
SET STATISTICS IO, TIME ON;
Run the query, read the messages tab, and write down the logical reads per table. That number is your baseline. If a change does not move it, the change did nothing, whatever the stopwatch said. A second execution against a warm cache looks faster, which is how teams declare ineffective tuning a success.
Rank the workload by total reads:
SELECT TOP (20)
qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms,
qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_elapsed_ms,
qs.execution_count,
qs.total_logical_reads,
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
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_logical_reads DESC;
Divide by 1000.0 rather than 1000. All three columns are bigint, and integer division reports 0 ms for every sub-millisecond query, which hides the exact high-frequency statements you are hunting.
Sort by total_logical_reads rather than average. A query running 800,000 times a day at 90 reads costs the server more than a nightly report at four million. This view reads the plan cache, so a restart or a recompile erases the evidence.
Example: fast in SSMS, slow in the application
This is the most common complaint in the field.
CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
@CustomerID INT
AS
SELECT OrderID, OrderDate, TotalDue
FROM dbo.Orders
WHERE CustomerID = @CustomerID;
The optimizer builds a plan the first time the procedure runs, using the parameter value from that specific call, then caches it. Call it first for a customer with three orders and you get an index seek with a key lookup, which is correct for three rows. That plan stays in cache. The next call passes a customer with two million orders, and the server performs two million key lookups instead of scanning once.
Running the same statement in SSMS produces a fresh plan for the value you typed, so it returns in milliseconds and the report of "it's fine here" is accurate and useless.
Confirm it in the actual execution plan. Click the leftmost SELECT operator and open its properties. Under Parameter List, SQL Server shows both Parameter Compiled Value and Parameter Runtime Value. When those differ and the estimated row count sits far below the actual, you have found it.
Three fixes, in order of how much they cost:
-- Recompile on every call. Burns CPU, correct plan every time.
-- Good for procedures called dozens of times an hour, not thousands.
SELECT ... WHERE CustomerID = @CustomerID
OPTION (RECOMPILE);
-- Ignore the sniffed value, use the density vector average from statistics.
-- One mediocre plan for everyone instead of one great and one catastrophic.
OPTION (OPTIMIZE FOR UNKNOWN);
On SQL Server 2022 at compatibility level 160, Parameter Sensitive Plan optimization caches multiple plans for the same statement and dispatches on the parameter value. It evaluates at most three predicates and handles equality predicates only, so it will not rescue every case, but it covers this pattern without a hint.
Example: the index is fine, the query cannot use it
An index seek requires the column to sit alone on one side of the predicate. Wrap it in anything and the optimizer scans.
-- Scan. YEAR() runs against every row before comparison.
WHERE YEAR(OrderDate) = 2026
-- Seek. Same result, expressed as a range.
WHERE OrderDate >= '20260101'
AND OrderDate < '20270101'
A function around the column is the version you will meet most often, because dates are what people filter on, and the half-open range is the rewrite: rewriting a date predicate so the index is usable.
-- Scan.
WHERE LEFT(LastName, 3) = 'Pay'
-- Seek.
WHERE LastName LIKE 'Pay%'
Implicit conversion is the one that gets past experienced people, because the query text carries no function at all.
-- AccountNumber is VARCHAR(20). @acct arrives as NVARCHAR(20)
-- because the ORM sent it that way.
WHERE AccountNumber = @acct
Data type precedence puts NVARCHAR above VARCHAR, so SQL Server converts the column rather than the parameter, and converting the column means the index on AccountNumber goes unused. The plan shows CONVERT_IMPLICIT in the operator predicate and carries a PlanAffectingConvert warning, which SSMS renders as a warning triangle: data type precedence and the conversions it forces.
Adding an index accomplishes nothing in either case. Match the types or move the function off the column.
Query Store finds the plan that changed
When a query that ran for months goes bad overnight, the plan changed, and the plan cache cannot tell you what the old one looked like because the recompile that replaced it also erased it. Query Store keeps plan history and per-plan runtime statistics on disk, which makes one question answerable: did this statement run under a different plan last week, and was that plan faster?
The shape to look for is two plan_id values under one query_id with average duration far apart. That comparison is the reason to reach for Query Store during a tuning pass, and it is the only thing it does that the DMV ranking above cannot.
Query Store shipped in SQL Server 2016, runs on every edition, and defaults to on for new databases from SQL Server 2022. On anything older you turn it on per database. The configuration that decides whether it is still recording when you need it, the regression query itself, and what forcing a plan does and does not repair are covered in keeping Query Store collecting for more than a few weeks.
Four things to stop doing
Database Engine Tuning Advisor. DTA replays a workload and recommends indexes per query, without consolidating against the indexes you already have. Run it on a busy OLTP table and it will propose six overlapping covering indexes, each with a dozen included columns. The missing index DMVs carry the same flaw for the same reason: they answer one query at a time, and nobody deduplicates the answers.
WITH (NOLOCK) as a performance setting. NOLOCK is READ UNCOMMITTED with different spelling, and past the famous dirty reads an allocation order scan can return a committed row twice or skip it when a page split moves rows mid-read, both documented by Microsoft's own SQLCAT team. When readers block writers, turn on Read Committed Snapshot Isolation.
Weekly index rebuilds. The 5% and 30% thresholds were chosen for SQL Server 2000-era hardware and were never a universal rule, and when a rebuild does help, the statistics update it performs as a side effect is usually what helped: what a rebuild costs on a busy server and updating statistics instead of rebuilding.
Page life expectancy below 300. That number comes from an era when 4GB was a large server. On a machine with 256GB of RAM it means nothing as an absolute, and on a NUMA system the instance-wide figure averages the nodes together: one starved node reading 40 seconds disappears behind an instance average of 2000. Use wait statistics.
Configuration worth checking once
Cost Threshold for Parallelism still defaults to 5, a value Microsoft picked for 1990s hardware. Trivial queries go parallel, pay the cost of coordinating threads, and return no faster. Most production OLTP instances run better between 40 and 50.
MAXDOP at 0 lets a single query use every available processor up to 64. SQL Server 2019 added a MAXDOP recommendation to the installer, so instances built on 2016 or 2017, and any instance upgraded in place, still carry whatever they had. Microsoft's guidance: with one NUMA node and eight or fewer logical processors, keep MAXDOP at or below that count. Above eight, use 8. With multiple NUMA nodes, stay at or below the logical processors in one node, and above 16 per node use half that, capped at 16.
Neither setting is a tuning strategy. Both take two minutes and remove noise from the measurements that follow.
What your version already does
Before you rewrite a query, check whether the engine addresses it.
| Version | Relevant additions |
|---|---|
| 2016 SP1 | Columnstore, in-memory OLTP, partitioning, and compression available in Standard Edition |
| 2016 | Query Store |
| 2017 | Adaptive joins, batch mode memory grant feedback, automatic plan correction |
| 2019 | Scalar UDF inlining, row mode memory grant feedback, table variable deferred compilation |
| 2022 | Parameter Sensitive Plan optimization, cardinality estimation feedback, degree of parallelism feedback |
Most of these require the matching database compatibility level, not just the binaries. A database restored from a 2012 instance onto 2022 and left at compatibility level 110 receives none of them. Checking that takes one query and explains migrations that delivered no improvement.
The version is half the answer. SP1 in 2016 moved several features down to Standard Edition and left others where they were, and the split is not guessable from the feature name: which of these features your edition has.
A SQL Server performance tuning order that works
- Read wait statistics. Decide whether you have a plan problem, a resource problem, or a concurrency problem.
- Capture logical reads for the specific statement. That is your baseline.
- Find the top consumers by total logical reads, not average.
- Check the plan for the parameter and conversion problems above before adding anything.
- Use Query Store when the query used to be fast.
- Change one thing. Measure the same way. Keep it or revert it.
You shipped four changes in one window, the system improved, and nobody can say which one did it or what to do when the problem comes back. That is how most tuning work fails, and step six is the only defense.
This order covers a slow query or a slow window. When the complaint is the whole instance, the review widens to configuration, storage latency, tempdb, and backup posture; that wider pass is the SQL Server health check, written up separately.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.