SQL Server Query Tuning & Operations Guide

SQL Server Operations and Query Tuning

Microsoft SQL Server runs a large share of the world's line-of-business applications, and much of its performance depends on a few recurring themes: the optimizer choosing a good execution plan, the right indexes and up-to-date statistics, avoiding blocking, and recognizing when a plan that was fine yesterday has regressed today. Operationally, the essentials are just as consistent: least-privilege access, regular integrity checks, and backups that are restored and verified, not only taken.

SQL Server includes excellent built-in tooling for this work — Query Store records query performance and plan history, dynamic management views expose waits and index usage, and Extended Events capture deadlocks and long-running queries. This page focuses on the workflow: find the expensive queries, understand why they are slow, fix them safely, and keep the database recoverable.

TL;DR

Read actual execution plans and SET STATISTICS IO, TIME ON output; look for scans, key lookups, spills, and bad estimates.
Use Query Store to find top resource consumers and plan regressions, and to force a known good plan as a stopgap.
Handle parameter sniffing with better indexes, parameter-sensitive plan optimization, or targeted hints — not blanket recompiles.
Index for real queries: covering indexes with INCLUDE, filtered indexes, columnstore for analytics; remove unused indexes.
Investigate waits, blocking, and deadlocks; consider read committed snapshot isolation (RCSI) to reduce reader–writer blocking.
Grant least privilege, run DBCC CHECKDB, and test restores — automated with tools like Ola Hallengren's scripts and dbatools.

Quick Example

Finding the most expensive queries in Query Store, then forcing a known good plan for a regressed query:

A covering index for a frequent lookup, built online:

Backing up and proving the backup restores, with dbatools in PowerShell:

Core Concepts

Execution Plans

Compare estimated and actual plans and use SET STATISTICS IO, TIME ON to see logical reads per table — reads are a more stable tuning metric than duration. See Query Optimization.

Query Store

Query Store captures query text, plans, and runtime statistics over time, and is enabled by default for new databases in SQL Server 2022. Use it to find top consumers, compare plans for the same query, detect regressions after deployments or upgrades, and force a plan temporarily. Forced plans can fail or become suboptimal as data changes, so track them and fix underlying causes.

Parameter Sniffing

SQL Server compiles a plan using the parameter values of the first execution and reuses it. When data is skewed — one customer with millions of orders, most with a handful — a plan perfect for one value is terrible for another. Options:

Improve indexes so one plan works well for all values.
Rely on parameter-sensitive plan (PSP) optimization (SQL Server 2022, compatibility level 160), which caches multiple plans for skewed predicates.
Use OPTION (RECOMPILE) for infrequent, highly variable queries.
Use OPTIMIZE FOR hints or Query Store hints for specific cases.

Indexing

Check sys.dm_db_index_usage_stats for indexes that are written but never read, and treat missing-index suggestions as hints, not instructions — they ignore existing indexes and write costs. See Database Indexing.

Statistics and Maintenance

Keep statistics current (auto-update plus scheduled updates for large tables) — stale statistics cause many bad plans. Index fragmentation matters less on SSD storage than it once did; prioritize statistics updates and rebuild or reorganize only when measurements show a benefit. Run DBCC CHECKDB regularly to detect corruption early.

Waits, Blocking, and Deadlocks

sys.dm_os_wait_stats shows what the server waits on: PAGEIOLATCH_* (reading data from disk), LCK_M_* (locks), CXPACKET/CXCONSUMER (parallelism), WRITELOG (transaction log writes), RESOURCE_SEMAPHORE (memory grants). Investigate the top waits rather than guessing.

Blocking happens when sessions wait for locks held by others; find head blockers with sys.dm_exec_requests and sys.dm_tran_locks, and keep transactions short. Deadlocks are captured by the built-in system_health Extended Events session; fix them by accessing tables in a consistent order, adding indexes so fewer rows are locked, and retrying the victim. Read committed snapshot isolation lets readers see the last committed version instead of waiting for writers, removing most reader–writer blocking at the cost of tempdb (or version store) usage. See Database Transactions.

Security and Recovery

Least privilege: application logins get database roles with only needed permissions; no sysadmin or db_owner for applications; prefer Windows or Microsoft Entra authentication where possible.
Auditing: SQL Server Audit for sensitive access and permission changes.
Backups: full, differential, and transaction log backups matched to recovery point objectives; the full recovery model is required for point-in-time restore.
Restore testing: automate restores to a test server with integrity checks; a backup that has never been restored is a hope, not a backup. See Database Backups.
High availability: Always On availability groups, failover cluster instances, or log shipping, depending on edition and requirements.

Best Practices

Tune With Evidence

Start from Query Store, waits, and actual plans; measure logical reads before and after each change.

Fix Root Causes, Not Symptoms

Treat forced plans and hints as temporary; fix indexes, statistics, and query patterns.

Keep Transactions Short

Short transactions reduce blocking, deadlocks, and log growth; never wait on user input or network calls inside one.

Automate Maintenance and Restore Tests

Schedule statistics updates, integrity checks, backups, and restore verification, with alerts on failure.

Common Mistakes

Functions on Indexed Columns

NOLOCK Everywhere

Backups That Were Never Restored

FAQ

What is Query Store in SQL Server?

A built-in feature that records query texts, execution plans, and runtime statistics over time, used to find expensive queries, detect plan regressions, and force or hint specific plans.

What is parameter sniffing?

SQL Server compiles a plan using the first parameter values it sees and reuses that plan. When data is skewed, the plan can be poor for other values, causing intermittent slowness.

Should I rebuild indexes every night?

Usually not by default. On modern storage, keeping statistics up to date matters more; rebuild or reorganize indexes when measurements show a benefit.

What is read committed snapshot isolation?

A database setting that makes read-committed queries read the last committed row versions instead of waiting for writers' locks, greatly reducing blocking between readers and writers.

How do I know my SQL Server backups work?

Restore them regularly to another server, run integrity checks, and verify recovery time — tools such as dbatools can automate restore testing.