Baselines: Knowing What Normal Looks Like

A SQL Server baseline records what normal work looks like before somebody asks whether the server got slower. Keep timestamps, workload context, and counter meanings together, or yesterday’s numbers won’t explain today’s complaint.

Define the Period You Want to Understand

Normal isn’t one average for the entire week. A morning order rush differs from an overnight load.

Month-end can differ again. Start by naming the business periods you need to compare. Ask the application owner when users care most about response time.

Choose a manageable collection interval and review its overhead. Five-minute samples can be a useful starting point for broad trends. They won’t explain every brief blocking incident. Use a separate incident capture when you need detail between those samples.

SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    @@SERVERNAME AS server_name,
    sqlserver_start_time
FROM sys.dm_os_sys_info;

Store the server identity, timestamp, and startup time with each collection. Those fields make later comparisons much safer. If the instance restarted between samples, cumulative counters belong to different lifetimes. A negative difference is a reset clue, not negative work.

Record Workload Alongside Resource Use

CPU usage alone doesn’t tell you whether the server was productive. Pair resource observations with a measure of work, such as application requests or completed business operations. Batch Requests per second can provide engine context, but it isn’t automatically the same as orders or users.

SELECT
    object_name, counter_name,
    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'Total Server Memory (KB)', N'Target Server Memory (KB)');

Store cntr_type instead of discarding it. Some counters exposed here are cumulative raw values even when their names include per second.

They need interval calculations. Others are current values. Applying the same difference formula to every counter produces a convincing but misleading report.

Capture host CPU and available memory through your approved Windows monitoring method as well. Note whether the machine hosts other workloads. SQL Server’s internal view doesn’t explain every process competing for the same physical resources.

Keep Raw Wait Samples

SELECT
    wait_type, waiting_tasks_count,
    wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats;

Retain the raw samples and calculate interval changes later. That lets you revise filtering without losing the original evidence. Exclude normal background waits carefully when presenting a summary. Keep the exclusion rules visible so another person understands what the chart leaves out.

Compare similar periods with similar throughput. A larger wait total during a busier window isn’t automatically a regression. Ask whether waiting grew faster than useful work or whether response requirements were missed. Baselines help frame that question rather than answer it by themselves.

Permissions for these views vary by SQL Server release. Give the collector the documented diagnostic permissions rather than sysadmin by default. Record collection failures separately. A missing sample should appear as a gap, not as a healthy zero.

Collect File Activity Separately

SELECT
    database_id, file_id,
    num_of_reads, num_of_writes,
    num_of_bytes_read, num_of_bytes_written,
    io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

Use the database and file identifiers to connect samples with an inventory of file names and types. Preserve that inventory when files change. Data and log files deserve separate interpretation. Averages across every file can hide a problem concentrated in one busy log.

Calculate interval stall time divided by interval operation count when examining average IO latency. Handle zero operations explicitly. Don’t divide a new total by an old count or mix samples taken at different times. Small arithmetic mistakes can become large infrastructure arguments.

Keep Enough History to Compare Like With Like

Collect across at least the business cycles relevant to your application. A few quiet weekdays don’t establish a month-end baseline. Retain detailed samples long enough for investigation, then summarize older periods if storage requires it. Keep the aggregation method with the retained data.

Annotate deployments, index changes, maintenance, and infrastructure moves. Those events explain shifts that otherwise look mysterious. Include collection configuration changes too. If you change the interval or filters, the old and new summaries can stop being directly comparable.

Page life expectancy is especially easy to oversimplify. Interpret it with workload, memory size, and changes over time. Don’t turn one remembered threshold into a universal failure condition. A baseline should reduce folklore rather than give it a graph.

Use the Baseline to Test a Claim

When someone reports a slowdown, choose the matching normal period and compare workload as well as resources. Look for changed wait patterns, file activity, and query behavior. Then investigate the strongest difference. The baseline narrows the search without pretending to identify every root cause.

I would rather keep a small collection with understood counters than store everything without a plan. Review whether each metric has helped answer a question. Keep enough raw evidence to verify the calculations. A useful baseline should make the next discussion more precise.

A baseline is not a promise that tomorrow looks the same, it is evidence of what yesterday’s work required.

This post was rewritten from scratch in September 2026. The original, published on 2008-11-12, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.