SQL Server Performance Tuning Checklist

Tune SQL Server in this order: wait statistics first, then the queries behind them in Query Store, then parameter sniffing, statistics and indexes, parallelism settings, tempdb, memory, and finally storage. Changing settings or adding indexes before you know what the instance is waiting on is how most tuning work goes wrong. A sound order for reviewing an instance, and the scope of AceMQ's SQL Server DBA services. The Microsoft facts cited here were checked on 31 October 2026.

1. Read the wait statistics before touching anything

SQL Server records what each request waited on. The counters are cumulative since the last restart, so take two snapshots and compare them rather than reading one number.

SELECT TOP (10) wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Filter out benign waits, then read the leaders: PAGEIOLATCH waits point to reads from storage, LCK_M waits to blocking, SOS_SCHEDULER_YIELD to CPU pressure, RESOURCE_SEMAPHORE to memory grants, WRITELOG to log flush latency, and CXPACKET or CXCONSUMER to parallelism. Each leads to a different fix, which is why indexes are not the first answer.

Capture a baseline on a normal day and a second capture during the slow period, then subtract one from the other. The difference tells you what changed. The same discipline applies after any change: write down the wait profile before you touch a setting, and compare it afterwards, so you know the change helped instead of assuming it did.

2. Turn on Query Store and look for regressions

Query Store keeps query plans and runtime statistics over time, so you can see when a plan changed. Microsoft states it is not enabled by default on SQL Server 2016, 2017 and 2019, and is enabled by default in read-write mode for new databases starting with SQL Server 2022. From SQL Server 2017 it also records wait statistics per query. Use the Regressed Queries report to find queries that got slower, and plan forcing to put the previous plan back while you find the cause.

ALTER DATABASE [YourDatabase]
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

3. Separate parameter sniffing from everything else

One cached plan is wrong for some parameter values when the data is skewed. SQL Server 2022 added Parameter Sensitive Plan optimization, which keeps several plans for one parameterized query. Microsoft lists database compatibility level 160 as the requirement and notes that it works only with equality predicates. On earlier versions the options are query hints, plan forcing, or restructuring the query, each with a cost.

4. Maintain statistics and indexes by need

A nightly job that rebuilds every index is a common cause of long maintenance windows, log growth and blocking, and it can leave statistics worse than before. Rebuild or reorganise only what is fragmented enough to matter, and update statistics where the data changed enough for estimates to drift.

5. Check parallelism settings

The default cost threshold for parallelism is 5. Microsoft calls it a starting point, not a recommendation, and says raising it can keep smaller OLTP queries on serial plans. Signs that it is too low are many CPU-light queries going parallel and CXPACKET and CXCONSUMER dominating the waits. Change it in small steps and watch a full business cycle, and review MAXDOP against the core count at the same time.

6. Look at tempdb, memory and storage

• tempdb: PAGELATCH waits at peak load suggest allocation contention. Use several equally sized data files and find the workload that creates and drops temporary objects so often.
• Memory: max server memory defaults to 2,147,483,647 MB, which is effectively unlimited (Microsoft: server memory options). Set it so the operating system and anything else on the server keep enough memory.
• Storage: compare read and write latency per file with sys.dm_io_virtual_file_stats before blaming the queries for PAGEIOLATCH or WRITELOG waits.
• Blocking: long LCK_M waits usually mean a transaction held open too long. Row-versioning isolation can remove reader and writer blocking, but test it, because it moves load to tempdb.

Four fixes that usually make things worse

• Shrinking files on a schedule. The files grow again, fragment the data and add I/O, and the wait you were chasing is still there.
• Creating every index in the missing-index suggestions. They are hints for single queries, not a design, and each index adds write cost and storage.
• Clearing the plan cache to cure a slow query. It hides parameter sniffing for a while and then triggers a burst of recompilation.
• Adding cores before the CPU waits are understood. A parallelism or plan problem scales badly, and SQL Server licences are priced by core.

7. After an upgrade or compatibility level change

Raise the compatibility level with Query Store already collecting data, so any regressed plan is visible and can be forced back. This is the safest way through a move such as the one described in the SQL Server 2016 end of life guide.

If tuning keeps landing on your team at 3am, SQL Server support gives you a 15-minute first response on P1 emergencies, and managed SQL Server hands the instances to AceMQ.

• Microsoft Learn: Monitor performance by using the Query Store
• Microsoft Learn: Parameter Sensitive Plan optimization
• Microsoft Learn: Server configuration: cost threshold for parallelism