Measuring Query Performance and Taking a Baseline

October 17, 2026

Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Tuning without measuring is guessing. Before changing a query, an index or a setting, record how the query performs now, make one change, and measure again in the same way. This post covers where the numbers come from.

What to measure

Duration: what the user feels, but it changes with blocking, load and network.
CPU time: the work the query did. More stable than duration.
Logical reads: pages read from memory. The most repeatable number, and the best one to compare before and after a change.
Rows returned and executions: a fast query run 50,000 times a minute can matter more than a slow one run once a day.

A single query: STATISTICS IO and TIME

SET STATISTICS IO, TIME ON;

SELECT CustomerID, SUM(Amount) AS total
FROM dbo.Orders
WHERE OrderDate >= '2026-06-01'
GROUP BY CustomerID;

SET STATISTICS IO, TIME OFF;

The Messages tab shows logical reads for each table and the CPU and elapsed time. Run the query twice and use the second run, so both runs read from memory. Never run DBCC DROPCLEANBUFFERS or clear the plan cache on a production server to get a “cold” figure.

The busiest queries since the last restart

sys.dm_exec_query_stats holds totals for every plan still in cache:

SELECT TOP (20)
       qs.execution_count,
       qs.total_worker_time / 1000 AS total_cpu_ms,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       qs.total_logical_reads / qs.execution_count AS avg_reads,
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_duration_ms,
       DB_NAME(st.dbid) AS database_name,
       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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Order by total_logical_reads or execution_count to see other views. The figures reset when a plan leaves the cache or the server restarts, so a quiet entry may only be quiet because its plan is new.

Query Store: history that survives restarts

Query Store keeps runtime figures per plan in the database itself, in time intervals, so you can compare last week with today. Turn it on for every user database you care about (it is on by default for new databases from SQL Server 2022):

ALTER DATABASE [YourDB] SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO, MAX_STORAGE_SIZE_MB = 1024);

-- Top queries by CPU over the last 7 days
SELECT TOP (20) q.query_id, p.plan_id,
       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) / NULLIF(SUM(rs.count_executions), 0) AS avg_reads,
       LEFT(qt.query_sql_text, 200) AS query_text
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_runtime_stats_interval i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query q ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id
WHERE i.start_time >= DATEADD(DAY, -7, SYSUTCDATETIME())
GROUP BY q.query_id, p.plan_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

Capturing every call with Extended Events

When you need each execution (which application, which parameters), capture completed statements over a threshold to a file. Keep the filter tight: an unfiltered capture on a busy server can cost real performance.

CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.username)
    WHERE duration > 1000000),            -- microseconds: over 1 second
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.client_app_name, sqlserver.database_name, sqlserver.username)
    WHERE duration > 1000000)
ADD TARGET package0.event_file (SET filename = N'SlowQueries', max_file_size = 100, max_rollover_files = 5)
WITH (STARTUP_STATE = OFF);

ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;
-- ... later
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = STOP;

Taking a baseline

A baseline is the same measurements taken regularly, so “slow” has something to be compared with. At its simplest, save the top queries and the wait statistics once a day:

CREATE TABLE dbo.QueryBaseline (
    captured_at     datetime2(0) NOT NULL DEFAULT SYSDATETIME(),
    query_hash      binary(8)    NOT NULL,
    execution_count bigint       NOT NULL,
    total_cpu_ms    bigint       NOT NULL,
    total_reads     bigint       NOT NULL,
    statement_text  nvarchar(400) NULL
);

INSERT dbo.QueryBaseline (query_hash, execution_count, total_cpu_ms, total_reads, statement_text)
SELECT TOP (50) qs.query_hash, SUM(qs.execution_count), SUM(qs.total_worker_time) / 1000,
       SUM(qs.total_logical_reads), MIN(LEFT(st.text, 400))
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
GROUP BY qs.query_hash
ORDER BY SUM(qs.total_worker_time) DESC;

Grouping by query_hash adds together queries that differ only in their literal values, which is how an application that does not use parameters really looks. Run the insert from a daily SQL Agent job and keep a few months.

Good practice

Compare logical reads first, CPU second and duration last.
Change one thing at a time, and measure the same way each time.
Test with production-sized data. A query that is fast on 1,000 rows tells you nothing about 100 million.
Note when the statistics were last updated before and after a test: a statistics update alone can change the plan.

From my SQL Server DBA handbook.

Performance

Clive Roberts