SQL Server query performance: how to measure and improve it

The Messages tab reports logical, physical and read-ahead reads per table, then compile time and execution time. Compare logical reads between two versions of a query. Physical reads depend on what happens to be in memory, so they vary between runs.

Add the actual execution plan (Ctrl+M in SSMS) for per-operator row counts, the memory grant and warnings. Reading SQL Server execution plans covers how to read it. When you cannot rerun the query, SQL Server 2019 and later can return the last actual plan from cache through sys.dm_exec_query_plan_stats, once the LAST_QUERY_PLAN_STATS database scoped configuration is on.

Measure the workload from the plan cache

sys.dm_exec_query_stats aggregates every cached statement. Group by query_hash so that the same statement compiled with different literals counts as one query:

WITH q AS (
    SELECT qs.query_hash,
           SUM(qs.execution_count) AS executions,
           SUM(qs.total_worker_time) / 1000 AS total_cpu_ms,
           SUM(qs.total_elapsed_time) / 1000 AS total_elapsed_ms,
           SUM(qs.total_logical_reads) AS total_logical_reads,
           SUM(qs.total_grant_kb) / 1024 AS total_grant_mb,
           SUM(qs.total_spills) AS total_spilled_pages,
           MIN(qs.creation_time) AS oldest_plan
    FROM sys.dm_exec_query_stats AS qs
    GROUP BY qs.query_hash
)
SELECT TOP (20) q.*, s.sample_statement
FROM q
CROSS APPLY (SELECT TOP (1) SUBSTRING(t.text, x.statement_start_offset / 2 + 1, 300) AS sample_statement
             FROM sys.dm_exec_query_stats AS x
             CROSS APPLY sys.dm_exec_sql_text(x.sql_handle) AS t
             WHERE x.query_hash = q.query_hash) AS s
ORDER BY q.total_cpu_ms DESC;

Times are in microseconds, accurate to milliseconds. total_grant_kb needs SQL Server 2016 or later, and total_spills needs SQL Server 2016 SP2 or 2017 CU3. The limits matter more than the columns: a row lives only as long as its plan stays in cache, so a restart, memory pressure or a recompile removes the history, and a statement run with OPTION (RECOMPILE) is either missing or shows only its most recent execution. oldest_plan shows how much history you are looking at.

Query Store: the durable record

Query Store writes query text, plans and runtime statistics into the database itself, so they survive restarts and failovers and keep every plan a query has used. It is on by default for new databases in SQL Server 2022 and later, and in Azure SQL Database. Databases created on older versions, or upgraded or restored from them, keep their previous setting, so check is_query_store_on in sys.databases.

ALTER DATABASE CURRENT SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE,
     QUERY_CAPTURE_MODE = AUTO,
     MAX_STORAGE_SIZE_MB = 2048,
     SIZE_BASED_CLEANUP_MODE = AUTO,
     WAIT_STATS_CAPTURE_MODE = ON);

The options that cause trouble:

• QUERY_CAPTURE_MODE. The default is ALL in SQL Server 2016 and 2017, and AUTO from SQL Server 2019. AUTO skips infrequent, cheap ad hoc queries. From SQL Server 2019 it captures a query after 30 executions, 1 second of compile CPU or 100 ms of execution CPU. SQL Server 2019 also adds CUSTOM to set those thresholds yourself.
• MAX_STORAGE_SIZE_MB. The default is 100 MB in SQL Server 2016 and 2017, and 1,000 MB from SQL Server 2019. When the limit is reached, Query Store switches to read-only and silently stops collecting. Compare actual_state_desc with desired_state_desc and read readonly_reason in sys.database_query_store_options.
• WAIT_STATS_CAPTURE_MODE. Available from SQL Server 2017, on by default.

Top queries by total CPU over the last seven days:

SELECT TOP (20)
       q.query_id,
       OBJECT_NAME(q.object_id) AS object_name,
       SUM(rs.count_executions) AS executions,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms,
       SUM(rs.avg_duration * rs.count_executions) / 1000 AS total_duration_ms,
       SUM(rs.avg_logical_io_reads * rs.count_executions) AS total_logical_reads,
       MAX(rs.max_query_max_used_memory) * 8 AS max_used_memory_kb,
       COUNT(DISTINCT p.plan_id) AS plan_count,
       qt.query_sql_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, -7, SYSDATETIMEOFFSET())
GROUP BY q.query_id, q.object_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

Durations and CPU are in microseconds, and memory is in 8 KB pages. A plan_count above 1 with very different durations per plan points to plan instability.

sys.query_store_wait_stats adds what each query waited on, grouped into categories:

SELECT TOP (20) p.query_id, ws.wait_category_desc,
       SUM(ws.total_query_wait_time_ms) AS total_wait_ms
FROM sys.query_store_wait_stats AS ws
JOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
     ON i.runtime_stats_interval_id = ws.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(DAY, -1, SYSDATETIMEOFFSET())
GROUP BY p.query_id, ws.wait_category_desc
ORDER BY total_wait_ms DESC;

The CPU category maps to SOS_SCHEDULER_YIELD. It is time spent waiting for a processor, not CPU used. Lock is blocking, Buffer IO is PAGEIOLATCH_* (reading pages from disk), Memory includes RESOURCE_SEMAPHORE (waiting for a memory grant) and Network IO is the client being slow to take rows.

Extended Events for specific cases

Query Store aggregates per interval. Use an Extended Events session when you need individual executions: the exact parameter values of one slow call, statements that time out, or blocked process reports.

CREATE EVENT SESSION SlowCalls ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.database_name, sqlserver.client_app_name, sqlserver.query_hash)
    WHERE duration > 2000000),            -- microseconds
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.database_name, sqlserver.client_app_name, sqlserver.query_hash)
    WHERE duration > 2000000)
ADD TARGET package0.event_file (SET filename = N'SlowCalls.xel', max_file_size = 100, max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS);

ALTER EVENT SESSION SlowCalls ON SERVER STATE = START;

Always filter. Avoid query_post_execution_showplan on a busy server, because it uses standard profiling for every query. query_post_execution_plan_profile (SQL Server 2019, and 2017 from CU14) collects the equivalent plan through lightweight profiling.

Build a baseline

A number means little without the normal value next to it. Keep, for at least one full business cycle:

• The top 20 queries by total CPU, total duration and total logical reads per day, from Query Store.
• Periodic snapshots of sys.dm_os_wait_stats and sys.dm_io_virtual_file_stats. Both accumulate from startup, so store the difference between snapshots.
• The instance settings and database options listed below, so you can tell when one changed.

When someone reports that the system is slow, compare today's list with the baseline. A new query in the top 20, or a known query with ten times its usual reads, is the place to start.

Pick targets by total resource use

Rank by average multiplied by executions. A 40-second report that runs once a night uses less of the server than a 15 ms lookup that runs four million times a day, and the lookup is usually easier to fix. Single slow runs get reported, but total consumption is what leaves less CPU, memory and I/O for everything else.

Use three lists: total CPU, total logical reads and total duration. A query that appears on all three comes first. A query with a large gap between min_duration and max_duration is a parameter sensitivity candidate, which is covered in Why is my stored procedure fast one day and slow the next?

Settings that affect every query

SELECT name, value_in_use
FROM sys.configurations
WHERE name IN (N'max degree of parallelism', N'cost threshold for parallelism', N'max server memory (MB)');

SELECT name, compatibility_level, is_query_store_on,
       is_auto_create_stats_on, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases;

MAXDOP can also be set per database (ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP), per workload group in Resource Governor and per query. The cost threshold is compared with the estimated cost of the serial plan in the optimizer's units, not seconds. To choose a value, look at the estimated costs of your frequent short queries and set the threshold above them. Memory sizing is covered in Why is SQL Server using all my server's memory?

Compatibility level and the cardinality estimator

The database compatibility level selects optimizer behavior, including the cardinality estimator (CE). Levels 110 and below use CE 70, and level 120 and above use the newer model with different assumptions. Upgrading the instance, or restoring a database to a newer one, does not change the level. A database can run on SQL Server 2022 with the optimizer behavior of SQL Server 2012 for years.

To raise it safely, turn on Query Store, collect a baseline at the old level, change the level, and review the Regressed Queries report. Fix regressions individually. The older CE stays available per database (ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON) and per query (OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')), SQL Server 2016 SP1 and later).

Intelligent Query Processing by version

These features change plans without code changes, and most need a minimum compatibility level:

Edition matters. In SQL Server 2022, batch mode on rowstore and both batch mode and row mode memory grant feedback are Enterprise edition features, while scalar UDF inlining, table variable deferred compilation, Parameter Sensitive Plan optimization and optimized plan forcing are in every edition. All of them are available in Azure SQL Database.

Statistics options

Leave AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on. By default a statistics update is synchronous: the query that finds stale statistics waits for the update before it compiles. AUTO_UPDATE_STATISTICS_ASYNC (off by default) lets that query compile with the old statistics while a background request updates them, which gives more even response times for workloads with frequent short queries. From SQL Server 2022, the ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY database scoped configuration stops the background update from blocking compilations.

Query Store hints and plan forcing

When you can't change the code, two tools change the plan from outside it:

-- SQL Server 2022 and later, Azure SQL
EXEC sys.sp_query_store_set_hints @query_id = 1234, @query_hints = N'OPTION (MAXDOP 1)';

-- SQL Server 2016 and later
EXEC sys.sp_query_store_force_plan @query_id = 1234, @plan_id = 56;

Query Store hints persist across restarts and override hints in the statement text and in plan guides. A forced plan can fail to apply, for example after an index it uses is dropped, so check force_failure_count and last_force_failure_reason_desc in sys.query_store_plan. Treat both as patches. Record why each one exists and review them after every upgrade.

A repeatable tuning loop

1. Take the top queries by total CPU, reads and duration from Query Store for a representative period.
2. For the first one, get the actual plan and SET STATISTICS IO, TIME output with realistic parameter values.
3. Classify it. High reads for few rows returned means an access path problem. A large gap between estimated and actual rows means statistics or parameters. Elapsed time far above CPU time means waits.
4. Change one thing: the query text, an index, statistics or a hint.
5. Measure again with the same parameters, and compare reads and CPU with step 2.
6. Deploy, then confirm in Query Store that the query's totals fell and no other query regressed.
7. Record the change and return to step 1. The list will have changed.

For the index step, see SQL Server index optimization: a practical workflow and SQL Server missing index DMVs.

Common mistakes

• Tuning the query someone complained about. It may be 0.1% of the workload. Rank by totals first.
• Comparing elapsed time between runs. It depends on caching, blocking and load. Compare logical reads and CPU.
• Trusting the plan cache as history. It is cleared by restarts and memory pressure. Query Store is the record.
• Leaving Query Store in read-only mode. It filled up weeks ago and nobody looked at actual_state_desc.
• Changing MAXDOP and the cost threshold together. You can't tell which one helped. Change one, then wait a business cycle.
• Raising the compatibility level without a Query Store baseline. You then have no previous plan to compare against or force.
• Forcing a plan and forgetting it. The data changes and the forced plan becomes the slow one.

Checklist

1. Enable Query Store in READ_WRITE mode with AUTO capture, size-based cleanup and enough storage, and confirm its actual state.
2. Record a baseline: top queries by total CPU, reads and duration, plus wait and file statistics.
3. Check MAXDOP, cost threshold for parallelism and max server memory against Microsoft's guidance.
4. Check each database's compatibility level and statistics options.
5. Pick targets by total resource use across CPU, reads and duration.
6. For each target, measure with SET STATISTICS IO, TIME and the actual plan, change one thing, and measure again.
7. Confirm the result in Query Store and review hints and forced plans periodically.

References

• Monitor performance by using the Query Store (Microsoft Learn)
• Best practices for monitoring workloads with Query Store
• sys.query_store_wait_stats, including the wait category mapping
• sys.dm_exec_query_stats
• Server configuration: max degree of parallelism
• Server configuration: cost threshold for parallelism
• Server memory configuration options
• Intelligent query processing
• Cardinality estimation (SQL Server)
• Query Store hints
• Query profiling infrastructure

SQL Server index optimization: a practical workflow

A table-by-table workflow for tuning existing SQL Server indexes: find unused and overlapping indexes, merge them, and replace blanket rebuilds with statistics updates.

#db-administration#indexing#maintenance