SQL Server Performance Analysis: A Baseline-First Method

Performance analysis and performance diagnosis are not the same job. Diagnosis starts when something is already slow and works backwards to a cause under time pressure. Analysis is the quieter work that comes first: measuring what the server does when nothing is wrong, so that when something is, you have a “before” to compare the “after” against.

That “before” is the baseline, and it is the single thing that turns opinion into evidence. “The server feels slow today” is unanswerable. “The top query by total time last Tuesday ran in two seconds and today it runs in nine, and its plan count went from one to four” is a bug report. A baseline is not one magic number; it is the normal shape of a workload across a day and a week, captured in enough detail that a departure from it stands out.

This guide builds that baseline from sources SQL Server already keeps, and shows how to read it: what to measure, how to capture it on a schedule, how to rank what you find, and how to decide what is worth your time. It is the proactive counterpart to diagnosing a problem that is already happening, and it leans on waits as its first signal — covered end to end in the wait statistics reference. Where those guides go deep on a live problem, this one stays deliberately broad and repeatable.

Measuring everything is the same as measuring nothing: you end up with a wall of numbers and no ranking. A useful baseline is a small set of signals, each one chosen because a change in it changes a decision. Seven cover the overwhelming majority of real workloads.

Notice what is not on the list: raw CPU percentage as a headline number, cache hit ratios read in isolation, and any single reading taken once. None of them survive contact with a real question. The seven above all share one property — they are worth recording over time, because their value is in the trend, not the snapshot.

Most of the baseline that matters is per query: which statements cost the most, whether a plan changed, how a query's runtime moved week to week. Building that by hand from the plan cache is possible but fragile — the cache is evicted under memory pressure and cleared on restart, so the history you most want is the first thing to disappear. Query Store solves this by persisting query, plan and runtime statistics to the database itself, where they survive restarts and failovers.

Turn it on deliberately rather than leaving the defaults to chance, and set it to keep the window you actually compare against:

-- Turn Query Store on and set it to actually keep what you need.
-- SQL Server 2016 (13.x) and later. On SQL Server 2022 (16.x) it is already ON in
-- READ_WRITE for NEW databases; databases created earlier, restored, or upgraded
-- in place keep whatever setting they had, so check before you assume.
ALTER DATABASE [YourDatabase] SET QUERY_STORE = ON
(
    OPERATION_MODE              = READ_WRITE,
    MAX_STORAGE_SIZE_MB         = 2048,   -- size for your retention, then watch it
    QUERY_CAPTURE_MODE          = AUTO,   -- skip trivial/ad-hoc noise (default on 2019+; ALL on 2016/2017)
    CLEANUP_POLICY              = (STALE_QUERY_THRESHOLD_DAYS = 30),  -- how far back the history reaches
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES     = 60      -- the runtime-stats aggregation grain
);

-- Confirm it is on and, just as important, still in READ_WRITE. A full store
-- flips itself to READ_ONLY and quietly stops collecting.
SELECT actual_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb,
       query_capture_mode_desc, stale_query_threshold_days
FROM sys.database_query_store_options;

Two settings earn the attention. MAX_STORAGE_SIZE_MB has to be large enough for your retention — a store that fills flips itself to READ_ONLY and stops collecting, which is a baseline with a hole in it starting from the day it filled. And QUERY_CAPTURE_MODE at AUTO keeps the trivial one-off statements out, so the history stays about the queries that repeat. The second query above is the one to re-run on a schedule: it is how you catch a store that has quietly gone read-only.

Query Store covers the per-query layer. The instance-level signals — waits, file latency, memory — live in cumulative DMVs that reset on restart and have no time dimension of their own. To give them one, snapshot them into a permanent table on a schedule. Waits are the clearest example, and the pattern is identical for the others.

Create a small table in a utility database. The server_start_time column is what later lets you tell a quiet period apart from a counter reset:

-- A small home for baseline snapshots. Put it in a DBA/utility database, not in
-- master and not in the database you are analysing. SQL Server 2012 (11.x)+.
CREATE TABLE dbo.wait_snapshot
(
    captured_at          datetime2(0) NOT NULL,
    wait_type            nvarchar(60) NOT NULL,
    waiting_tasks_count  bigint       NOT NULL,
    wait_time_ms         bigint       NOT NULL,
    signal_wait_time_ms  bigint       NOT NULL,
    server_start_time    datetime2(0) NOT NULL,   -- lets you detect a restart later
    CONSTRAINT pk_wait_snapshot PRIMARY KEY (captured_at, wait_type)
);

Then capture one row set per wait type on a schedule — every fifteen to thirty minutes is a sensible starting grain. This is the scheduled, long-lived cousin of the one-off temp-table delta in the wait guide; use that one inside a single investigation, and this one to build history:

-- One snapshot. Schedule it with SQL Server Agent (Elastic Jobs on Azure SQL
-- Database) every 15-30 minutes. Reading the DMV is a memory read, so the cost
-- of sampling is negligible against the workload you are measuring.
INSERT dbo.wait_snapshot
    (captured_at, wait_type, waiting_tasks_count,
     wait_time_ms, signal_wait_time_ms, server_start_time)
SELECT
    SYSDATETIME(),
    ws.wait_type,
    ws.waiting_tasks_count,
    ws.wait_time_ms,
    ws.signal_wait_time_ms,
    si.sqlserver_start_time
FROM sys.dm_os_wait_stats AS ws
CROSS JOIN sys.dm_os_sys_info AS si
WHERE ws.waiting_tasks_count > 0
  AND ws.wait_type NOT IN (
        -- Background and timer waits: the server idling on purpose. These counters
        -- only ever climb, so storing them would bury the real signal under rows
        -- that describe nothing. Same ignore list as the wait statistics guide --
        -- keep the two in step.
        N'BROKER_EVENTHANDLER',        N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',           N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',         N'CHECKPOINT_QUEUE',
        N'CHKPT',                      N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',           N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',         N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',      N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',            N'DISPATCHER_QUEUE_SEMAPHORE',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',       N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',            N'HADR_WORK_QUEUE',
        N'LAZYWRITER_SLEEP',           N'LOGMGR_QUEUE',
        N'ONDEMAND_TASK_QUEUE',        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_ASYNC_QUEUE',            N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_SHUTDOWN_QUEUE',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',            N'SLEEP_DCOMSTARTUP',
        N'SLEEP_SYSTEMTASK',           N'SLEEP_TASK',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES',
        N'WAIT_XTP_HOST_WAIT',         N'WAITFOR',
        N'XE_DISPATCHER_WAIT',         N'XE_TIMER_EVENT',
        N'CXCONSUMER'                  -- real work, but not a problem signal on its own
    );

The capture drops the background and timer waits before they are ever stored, using the same idle-wait ignore list that the wait statistics guide filters on when it reads the live DMV. Those counters only ever climb, so leaving them in would bury a real trend under rows that record the server idling on purpose.

Once two or more snapshots exist, the trend falls out of a window function. Reading consecutive captures with LAG gives the resource wait added in each interval, and comparing server_start_time across the pair discards the rows that straddle a restart:

-- Resource wait added between each snapshot and the one before it, per wait type.
-- The snapshots are cumulative, so a negative delta means the instance restarted
-- between captures; server_start_time lets you drop those rows instead of
-- mistaking a counter reset for a quiet period.
WITH snaps AS (
    SELECT
        captured_at, wait_type, wait_time_ms, signal_wait_time_ms, server_start_time,
        LAG(wait_time_ms)        OVER (PARTITION BY wait_type ORDER BY captured_at) AS prev_wait_ms,
        LAG(signal_wait_time_ms) OVER (PARTITION BY wait_type ORDER BY captured_at) AS prev_signal_ms,
        LAG(server_start_time)   OVER (PARTITION BY wait_type ORDER BY captured_at) AS prev_start
    FROM dbo.wait_snapshot
)
SELECT
    captured_at,
    wait_type,
    CAST(((wait_time_ms - prev_wait_ms)
        - (signal_wait_time_ms - prev_signal_ms)) / 1000.0 AS decimal(16, 2)) AS resource_sec
FROM snaps
WHERE prev_wait_ms IS NOT NULL
  AND server_start_time = prev_start          -- same uptime, so the delta is real
  AND wait_time_ms - prev_wait_ms > 0
ORDER BY captured_at DESC, resource_sec DESC;

Two weeks of fifteen-minute snapshots is a few hundred thousand small rows — trivial to store, fast to query, and enough to answer “is this normal for a Tuesday morning?” with a query instead of a shrug.

With Query Store collecting, the most valuable question in analysis becomes a single query: over the last week, which statements owned the most time? The answer is almost never the query with the worst average. It is the moderate query that runs constantly — and that is the one to fix first, because the total time it costs the server is the total time you get back.

-- Top queries by TOTAL elapsed time over the last 7 days. Total, not average:
-- a 5 ms query run ten million times outranks a 9-second report run nightly, and
-- it is usually the one to fix first. Query Store, SQL Server 2016 (13.x)+.
-- Durations and CPU time are microseconds; logical reads are 8 KB pages.
SELECT TOP (25)
    q.query_id,
    OBJECT_NAME(q.object_id)                                           AS [object],
    SUM(rs.count_executions)                                           AS executions,
    CAST(SUM(rs.avg_duration * rs.count_executions) / 1000000.0
         AS decimal(18, 1))                                            AS total_sec,
    CAST(SUM(rs.avg_duration * rs.count_executions)
         / NULLIF(SUM(rs.count_executions), 0) / 1000.0
         AS decimal(18, 2))                                            AS avg_ms,
    CAST(SUM(rs.avg_cpu_time * rs.count_executions) / 1000000.0
         AS decimal(18, 1))                                            AS total_cpu_sec,
    SUM(CAST(rs.avg_logical_io_reads AS bigint) * rs.count_executions) AS total_reads,
    MIN(qt.query_sql_text)                                             AS query_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, SYSUTCDATETIME())
GROUP BY q.query_id, q.object_id
ORDER BY total_sec DESC;

The ordering is by total_sec on purpose: average duration times execution count. Keep the CPU and read totals in the same result so you can see why a query is near the top — a query that is heavy on reads points at indexing, one that is heavy on CPU at the plan or the expression. A worked example of this exact pattern, a report whose total time doubled after a plan flipped, is in a nightly report that doubled in runtime.

A single top-consumers list is a photograph. Analysis is really about the difference between two of them — this week against last, this month against the one before. The question is not “what is slow?” but “what changed?”, and a baseline is the only thing that can answer it.

At the coarsest level, compare the shape of the whole workload day by day. A gentle upward slope in total daily time is capacity planning arriving early; a step change on a particular date is a release or a data-volume threshold you can go and find:

-- Workload shape, one row per day for the last four weeks. Read it as a trend:
-- a Tuesday that is quietly 30% heavier than the Tuesday before it is the kind of
-- drift a baseline exists to catch, long before it turns into an incident.
SELECT
    CAST(i.start_time AS date)                                   AS [day],
    SUM(rs.count_executions)                                     AS executions,
    CAST(SUM(rs.avg_duration * rs.count_executions) / 1000000.0
         AS decimal(18, 1))                                      AS total_sec,
    CAST(SUM(rs.avg_cpu_time * rs.count_executions) / 1000000.0
         AS decimal(18, 1))                                      AS total_cpu_sec
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
WHERE i.start_time >= DATEADD(day, -28, SYSUTCDATETIME())
GROUP BY CAST(i.start_time AS date)
ORDER BY [day];

Read this alongside the top-consumers query. When the daily total jumps, the top-consumers list for the days on either side tells you which query carried the jump. When a query climbs the ranking without the daily total moving, something got slower rather than busier — usually a plan change, which is where the diagnosis guide's regression technique takes over.

Indexes are the part of a schema that rots quietly. Every index speeds some reads and slows every write to its table, and the balance drifts as the workload changes — yesterday's essential index is today's pure write tax. Usage statistics make the balance visible.

-- Reads vs writes per index, for the CURRENT database. Run it in the database you
-- are analysing: sys.dm_db_index_usage_stats is scoped by database_id, and a row
-- exists only once an index has been touched since the service last started.
SELECT
    OBJECT_SCHEMA_NAME(i.object_id)                        AS [schema],
    OBJECT_NAME(i.object_id)                               AS [table],
    i.name                                                 AS index_name,
    i.type_desc,
    ISNULL(us.user_seeks, 0) + ISNULL(us.user_scans, 0)
        + ISNULL(us.user_lookups, 0)                       AS reads,
    ISNULL(us.user_updates, 0)                             AS writes,
    us.last_user_seek, us.last_user_scan, us.last_user_lookup
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
       ON us.object_id   = i.object_id
      AND us.index_id    = i.index_id
      AND us.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsMsShipped') = 0   -- user objects only
  AND i.type IN (1, 2)                                 -- clustered and non-clustered
ORDER BY writes DESC, reads ASC;

-- Check how long the counters have been accumulating before you read anything
-- into a zero. Right after a restart, every index on the instance looks unused.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Read it from the top: indexes with many writes and no reads are the candidates to question. But the second query is the one that keeps you honest. sys.dm_db_index_usage_stats counts only since the service last started, so a “never read” index on an instance that rebooted this morning is telling you about this morning, not about the workload. Let the counters accumulate across a full business cycle — including the month-end and quarter-end jobs that use indexes nothing else touches — before you treat a zero as real. The PAGEIOLATCH waits traced to a missing index case study shows the other direction — reads that an index would have served and did not.

Analysis that does not end in a decision is a hobby. The output of everything above is a ranked list of candidates; the job now is to sort them by return on effort, not by how interesting they are. Two axes do it: how much time a fix gives back, and how much it costs to make.

The ranking by total time already did most of the impact axis for you. The effort axis is where judgement comes in, and the trap is the bottom-right quadrant: a fascinating, hard problem on a query that barely costs anything. A baseline is what lets you see it for what it is and leave it alone.

None of this works as a one-off. A baseline captured once and never looked at again is just old data. The value compounds when the reading is a habit, and the habit splits cleanly into a short weekly pass and a longer monthly one.

The point of the cadence is not thoroughness for its own sake. It is that the first time you see a new query at the top of the list, it is a small change you can still reason about — not a crisis you are reconstructing after the fact.

Every query in this guide runs against tools that ship with SQL Server. You do not need anything else to do performance analysis well; you need to know which built-in answers which question.

Whatever you add on top — built in or third party — judge it on four things: does it read without changing anything, does it keep enough history to compare periods, does it rank by total impact rather than a single snapshot, and does it show the evidence behind a recommendation rather than just the recommendation. SQLPerformance AI is one read-only SQL Server performance analyzer built around those four, running the collection and ranking steps on this page over a connection that only reads. The reason to name the criteria, though, is that they hold for whatever tool you settle on.

The DMV queries here run on SQL Server 2012 and later. The per-query sections depend on Query Store, so they need 2016 or later, and a few capabilities arrived in specific versions worth knowing about.