Someone changes an index and asks whether the server feels faster. A performance baseline gives you a saved starting point for that answer. Capture waits, file activity, query CPU, and workload counters before touching anything.
Give the Promise a Starting Number
Resolutions fail when the starting point is missing. Tuning has the same problem. You promise less CPU, faster storage, or shorter waits. Compared with what? Without a saved interval, the answer becomes a discussion about feelings.
I ask for the before numbers before reviewing a tuning change. Almost every server I inherit has plenty of screenshots and very little comparable history. A screenshot has excellent memory until someone closes the window.
Choose a normal workload period first. Record the business activity alongside your samples. A busy order-entry window and an idle maintenance window answer different questions. Keep the collection interval consistent. The goal is a useful comparison, not a giant collection project.
Write down the change you intend to test and the expected effect. An index change targets particular statements. A memory setting affects a broader workload. Decide which evidence answers your question before collecting it. Otherwise, one improved number becomes the excuse to ignore three worse ones. Keep the deployment time with your comparison notes.
Store the Performance Baseline Outside the DMVs
Use an existing DBA utility database for this small table. Run every example in that database. The JSON approach keeps four related snapshots in one row. OPENJSON needs database compatibility level 130 or higher. Check that prerequisite before starting.
On SQL Server 2022 and later, these performance DMVs require VIEW SERVER PERFORMANCE STATE. The collection account also needs INSERT on this table. Give readers SELECT separately. Query text belongs in this restricted utility database because statements contain business details.
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
IF OBJECT_ID(N'dbo.PerformanceBaseline', N'U') IS NULL
BEGIN
CREATE TABLE dbo.PerformanceBaseline
(
CaptureId bigint IDENTITY(1,1) NOT NULL PRIMARY KEY,
CaptureTimeUtc datetime2(3) NOT NULL,
SqlStartTime datetime2(3) NOT NULL,
WaitJson nvarchar(max) NOT NULL,
FileJson nvarchar(max) NOT NULL,
QueryJson nvarchar(max) NOT NULL,
CounterJson nvarchar(max) NOT NULL
);
END;Collect the Performance Baseline on a Schedule
The next block saves cumulative values, not calculated rates. Put this entire block in a SQL Server Agent T-SQL job step. Set the step’s database to your utility database. Use a regular schedule, such as every five minutes during the period you study.
Capture all cached query rows so an interval’s CPU winner is not excluded by an earlier ranking. The statement text helps identify the work later. These reads happen close together, rather than at one atomic instant. Keep that collection skew in mind for very short intervals.
Run the collection manually first and inspect the saved row. Confirm each JSON document contains the expected records. Measure collection overhead on your own instance before scheduling it. A large plan cache produces more query history than a small one. Adjust the interval and retention to the workload rather than collecting endlessly by habit.
SET NOCOUNT ON;
DECLARE @Captured datetime2(3) = SYSUTCDATETIME();
DECLARE @Started datetime2(3) =
(SELECT sqlserver_start_time FROM sys.dm_os_sys_info);
INSERT dbo.PerformanceBaseline
(CaptureTimeUtc, SqlStartTime, WaitJson, FileJson,
QueryJson, CounterJson)
SELECT @Captured, @Started,
(SELECT wait_type, waiting_tasks_count,
wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
FOR JSON PATH),
(SELECT v.database_id, v.file_id, f.physical_name,
v.num_of_reads, v.io_stall_read_ms,
v.num_of_writes, v.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS v
JOIN sys.master_files AS f
ON f.database_id = v.database_id AND f.file_id = v.file_id
FOR JSON PATH),
(SELECT CONVERT(varchar(130), q.plan_handle, 1) AS plan_handle,
q.creation_time, q.plan_generation_num,
q.statement_start_offset, q.statement_end_offset,
q.total_worker_time, q.execution_count,
LEFT(SUBSTRING(t.text, q.statement_start_offset / 2 + 1,
(CASE WHEN q.statement_end_offset = -1
THEN DATALENGTH(t.text)
ELSE q.statement_end_offset END
- q.statement_start_offset) / 2 + 1), 2000)
AS statement_text
FROM sys.dm_exec_query_stats AS q
OUTER APPLY sys.dm_exec_sql_text(q.sql_handle) AS t
FOR JSON PATH),
(SELECT RTRIM(object_name) AS object_name,
RTRIM(counter_name) AS counter_name,
RTRIM(instance_name) AS instance_name,
cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE counter_name IN
(N'Batch Requests/sec', N'Page life expectancy',
N'Memory Grants Pending')
FOR JSON PATH);Choose Two Captures with a Shared Lifetime
Run the comparison blocks in order in one SSMS window. The first one, in the next section, saves the latest two captures in #BaselinePair. SampleOrder 1 is newer, and 2 is older. Replace that selection with chosen CaptureId values when reviewing a particular change.
The startup check rejects a restart boundary. Wait statistics also reset when someone clears them manually. Negative differences expose some resets, but a reset followed by heavy activity can hide the drop. Record manual resets and discard those intervals too.
Read Waits as Interval Evidence
The next block builds that pair, stops on a restart, and then subtracts older waits from newer waits. Keep signal time separate so you can distinguish the runnable queue from the rest of the wait. Multiple workers wait together, so accumulated wait time exceeds wall-clock time without indicating a calculation error.
The query retains zero differences. Those rows show what did not change. Review background waits separately instead of treating the largest total as an automatic tuning target. I check workload volume before blaming the first wait in a sorted list.
Rows with decreasing counters are excluded from these calculations. Inspect the saved values when expected rows disappear. Treat that disappearance as a question about sample continuity, not proof that a problem vanished. A monitoring gap also makes the resulting interval longer than the schedule suggests. Use the saved timestamps to establish the actual duration.
DROP TABLE IF EXISTS #BaselinePair;
SELECT TOP (2) *,
ROW_NUMBER() OVER (ORDER BY CaptureId DESC) AS SampleOrder
INTO #BaselinePair
FROM dbo.PerformanceBaseline
ORDER BY CaptureId DESC;
IF (SELECT COUNT(*) FROM #BaselinePair) <> 2
THROW 50000, 'Collect two baseline snapshots first.', 1;
IF (SELECT MIN(SqlStartTime) FROM #BaselinePair)
<> (SELECT MAX(SqlStartTime) FROM #BaselinePair)
THROW 50001, 'Discard this interval because SQL Server restarted.', 1;
SELECT CaptureId, CaptureTimeUtc, SqlStartTime, SampleOrder
FROM #BaselinePair
ORDER BY SampleOrder DESC;
;WITH W AS
(
SELECT p.SampleOrder, j.*
FROM #BaselinePair AS p
CROSS APPLY OPENJSON(p.WaitJson) WITH
(
wait_type nvarchar(60), waiting_tasks_count bigint,
wait_time_ms bigint, signal_wait_time_ms bigint
) AS j
)
SELECT a.wait_type,
a.wait_time_ms - b.wait_time_ms AS WaitDeltaMs,
a.signal_wait_time_ms - b.signal_wait_time_ms AS SignalDeltaMs,
a.waiting_tasks_count - b.waiting_tasks_count AS WaitCountDelta
FROM W AS a
JOIN W AS b ON b.wait_type = a.wait_type AND b.SampleOrder = 2
WHERE a.SampleOrder = 1
AND a.wait_time_ms >= b.wait_time_ms
AND a.signal_wait_time_ms >= b.signal_wait_time_ms
AND a.waiting_tasks_count >= b.waiting_tasks_count
ORDER BY WaitDeltaMs DESC;Calculate Latency from File Deltas
A lifetime file average hides a recent problem. Divide the interval’s read stall by its read count. Do the same for writes. NULL means no operations occurred in that direction. It does not mean storage completed operations instantly.
Keep the physical path in the match so a replaced file does not inherit another file’s history. Treat restores, detachments, and file replacement as fresh boundaries even when identifiers match. Review data and log files separately. A log flush and a large data read serve different work.
;WITH F AS
(
SELECT p.SampleOrder, j.*
FROM #BaselinePair AS p
CROSS APPLY OPENJSON(p.FileJson) WITH
(
database_id int, file_id int, physical_name nvarchar(260),
num_of_reads bigint, io_stall_read_ms bigint,
num_of_writes bigint, io_stall_write_ms bigint
) AS j
)
SELECT a.database_id, a.file_id, a.physical_name,
a.num_of_reads - b.num_of_reads AS ReadDelta,
a.num_of_writes - b.num_of_writes AS WriteDelta,
1.0 * (a.io_stall_read_ms - b.io_stall_read_ms)
/ NULLIF(a.num_of_reads - b.num_of_reads, 0) AS ReadLatencyMs,
1.0 * (a.io_stall_write_ms - b.io_stall_write_ms)
/ NULLIF(a.num_of_writes - b.num_of_writes, 0) AS WriteLatencyMs
FROM F AS a
JOIN F AS b ON b.database_id = a.database_id
AND b.file_id = a.file_id
AND b.physical_name = a.physical_name AND b.SampleOrder = 2
WHERE a.SampleOrder = 1
AND a.num_of_reads >= b.num_of_reads
AND a.num_of_writes >= b.num_of_writes
AND a.io_stall_read_ms >= b.io_stall_read_ms
AND a.io_stall_write_ms >= b.io_stall_write_ms;Rank CPU without Hiding Cache Changes
This ranking uses CPU consumed between captures by matching cached statement rows. CPU totals are microseconds. Divide by 1,000 for milliseconds when presenting them. Execution deltas distinguish more calls from more CPU per call.
Plans evicted between samples disappear from this comparison. New plans lack an older matching row. Recompiles get separate identities through compilation time and generation number. Report that coverage limit alongside the performance baseline. Saved snapshots preserve evidence, but they cannot recover activity that vanished before collection.
;WITH Q AS
(
SELECT p.SampleOrder, j.*
FROM #BaselinePair AS p
CROSS APPLY OPENJSON(p.QueryJson) WITH
(
plan_handle varchar(130), creation_time datetime2(3),
plan_generation_num bigint, statement_start_offset int,
statement_end_offset int, total_worker_time bigint,
execution_count bigint, statement_text nvarchar(2000)
) AS j
)
SELECT TOP (20) a.statement_text,
a.total_worker_time - b.total_worker_time AS CpuDeltaUs,
a.execution_count - b.execution_count AS ExecutionDelta,
1.0 * (a.total_worker_time - b.total_worker_time)
/ NULLIF(a.execution_count - b.execution_count, 0)
AS CpuUsPerExecution
FROM Q AS a
JOIN Q AS b ON b.plan_handle = a.plan_handle
AND b.creation_time = a.creation_time
AND b.plan_generation_num = a.plan_generation_num
AND b.statement_start_offset = a.statement_start_offset
AND b.statement_end_offset = a.statement_end_offset
AND b.SampleOrder = 2
WHERE a.SampleOrder = 1
AND a.total_worker_time >= b.total_worker_time
AND a.execution_count >= b.execution_count
ORDER BY CpuDeltaUs DESC;Compare Counters across the Performance Baseline
Batch Requests/sec uses a cumulative counter in this DMV. Divide its delta by elapsed seconds. Page life expectancy and Memory Grants Pending are gauges. Compare their captured values directly. Subtracting every counter just because it contains a number gives convincing nonsense.
;WITH C AS
(
SELECT p.SampleOrder, p.CaptureTimeUtc, j.*
FROM #BaselinePair AS p
CROSS APPLY OPENJSON(p.CounterJson) WITH
(
object_name nvarchar(128), counter_name nvarchar(128),
instance_name nvarchar(128), cntr_value bigint, cntr_type int
) AS j
)
SELECT a.object_name, a.counter_name, a.instance_name,
CASE WHEN a.cntr_type = 65792 THEN b.cntr_value END AS BeforeGauge,
CASE WHEN a.cntr_type = 65792 THEN a.cntr_value END AS AfterGauge,
CASE WHEN a.cntr_type IN (272696320, 272696576)
AND a.cntr_value >= b.cntr_value
THEN 1.0 * (a.cntr_value - b.cntr_value)
/ NULLIF(DATEDIFF_BIG(millisecond,
b.CaptureTimeUtc, a.CaptureTimeUtc) / 1000.0, 0)
END AS IntervalRatePerSecond
FROM C AS a
JOIN C AS b ON b.object_name = a.object_name
AND b.counter_name = a.counter_name
AND b.instance_name = a.instance_name
AND b.cntr_type = a.cntr_type AND b.SampleOrder = 2
WHERE a.SampleOrder = 1;Collect a comparable interval after the tuning change. Compare both sets of deltas and rates with the same workload context. Lower CPU during lower traffic proves little. Unchanged file latency still matters when query CPU improves. Say exactly which part changed.
Missing counter rows need investigation. They are different from a captured zero. Two gauge readings also miss spikes between samples. Keep Windows Performance Monitor running when short bursts matter, using a collection interval that captures them. These saved SQL counters provide context for tuning, while continuous monitoring supplies the detail between those points.
Keep enough scheduled history to cover normal peaks and maintenance. Trim older captures under a retention policy after preserving the comparison you need. Check collection job failures too. Your performance baseline needs continuous evidence, not one lucky sample.
Related reading on this blog: Measure CPU Pressure: Detect CPU Pressure and 3 ChatGPT Tricks to Tune SQL Server: SQL in Sixty Seconds 203.
A performance baseline is not a feeling, it is evidence you can compare.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
