The server feels slower than last month, but yesterday's counter values are gone. A performance counter baseline keeps selected samples so you can compare workload and pressure over a defined reporting interval.
Select Counters With Clear Meanings
Begin with counters that answer specific questions. Batch requests describe incoming command batches. Compilations help explain compilation activity, while lock waits identify requests that had to wait for locks.
Page life expectancy describes buffer-pool retention in seconds. Treat it as a snapshot gauge rather than a cumulative operation total. Its trend belongs beside workload context, not a universal pass-or-fail threshold.
I first inspect counter names and instance scope on the actual server. This sample selects the overall buffer manager and total lock instance. Per-node analysis needs separate Buffer Node samples on NUMA systems.
SELECT RTRIM(object_name) AS CounterObject,
RTRIM(counter_name) AS CounterName,
RTRIM(instance_name) AS CounterInstance,
cntr_type, cntr_value
FROM sys.dm_os_performance_counters
WHERE (RTRIM(object_name) LIKE N'%:SQL Statistics'
AND RTRIM(counter_name) IN (N'Batch Requests/sec', N'SQL Compilations/sec'))
OR (RTRIM(object_name) LIKE N'%:Buffer Manager'
AND RTRIM(counter_name) = N'Page life expectancy')
OR (RTRIM(object_name) LIKE N'%:Locks'
AND RTRIM(counter_name) = N'Lock Waits/sec'
AND RTRIM(instance_name) = N'_Total')
ORDER BY CounterObject, CounterName, CounterInstance;Inspect cntr_type before assigning an interpretation. Types 272696320 and 272696576 require successive samples for rates. Type 65792 exposes a snapshot value rather than an interval average.
This collector doesn't implement ratio counters requiring a separate base value. Adding those requires their documented calculation. A counter name ending in a percentage isn't enough information.
On SQL Server 2022 and later, these server performance DMVs require VIEW SERVER PERFORMANCE STATE. Older versions use VIEW SERVER STATE. Configure the collector's execution identity with the required permissions before installation.
Store the Performance Counter Baseline With Engine Lifetime
Store each capture's UTC timestamp and monotonic millisecond reading. Also retain the engine startup timestamp and startup tick value. Those fields help distinguish normal increments from a new engine lifetime.
A header and its counter rows should commit together. An incomplete capture needs a failure, not a convincing timestamp with missing values. The procedure below rolls back when it cannot collect the expected set.
Use a dedicated monitoring database for these objects. Keep that database name available for the Agent step. The object names below assume an empty test database during initial review.
CREATE TABLE dbo.CounterCapture
(
CaptureId bigint IDENTITY PRIMARY KEY,
SampleUtc datetime2(3) NOT NULL,
EngineStartLocal datetime2(3) NOT NULL,
EngineStartTicks bigint NOT NULL,
SampleTicks bigint NOT NULL
);
CREATE INDEX IX_CounterCapture_Utc
ON dbo.CounterCapture (SampleUtc);
CREATE TABLE dbo.CounterValue
(
CaptureId bigint NOT NULL REFERENCES dbo.CounterCapture (CaptureId),
CounterObject nvarchar(128) NOT NULL,
CounterName nvarchar(128) NOT NULL,
CounterInstance nvarchar(128) NOT NULL,
CounterType int NOT NULL,
CounterValue bigint NOT NULL,
PRIMARY KEY (CaptureId, CounterObject, CounterName, CounterInstance)
);
GO
CREATE PROCEDURE dbo.CaptureCounters
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
INSERT dbo.CounterCapture
(SampleUtc, EngineStartLocal, EngineStartTicks, SampleTicks)
SELECT SYSUTCDATETIME(), sqlserver_start_time,
sqlserver_start_time_ms_ticks, ms_ticks
FROM sys.dm_os_sys_info;
DECLARE @CaptureId bigint = CONVERT(bigint, SCOPE_IDENTITY());
INSERT dbo.CounterValue
SELECT @CaptureId, RTRIM(object_name), RTRIM(counter_name),
RTRIM(instance_name), cntr_type, cntr_value
FROM sys.dm_os_performance_counters
WHERE (RTRIM(object_name) LIKE N'%:SQL Statistics'
AND RTRIM(counter_name) IN
(N'Batch Requests/sec', N'SQL Compilations/sec'))
OR (RTRIM(object_name) LIKE N'%:Buffer Manager'
AND RTRIM(counter_name) = N'Page life expectancy')
OR (RTRIM(object_name) LIKE N'%:Locks'
AND RTRIM(counter_name) = N'Lock Waits/sec'
AND RTRIM(instance_name) = N'_Total');
IF (SELECT COUNT(*) FROM dbo.CounterValue WHERE CaptureId = @CaptureId) <> 4
THROW 51070, 'The required counter set was not collected.', 1;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
END;
GOThe four-row requirement describes the chosen counter set. On my SQL Server 2025 test instance, the selection returned exactly four rows. Verify that selection on your instance before deployment. Localized counter names need an approved mapping if these English names aren't present.
These DMV reads occur close together, not at one atomic server-wide measurement instant. The method supports an operational trend. It doesn't promise transaction-level synchronization between every counter.
Turn Cumulative Values Into Interval Rates
For a cumulative counter, subtract the previous value from the current value. Divide that difference by the actual elapsed sample time. The stored millisecond readings avoid assuming every job run occurred precisely on schedule.
Never subtract across an engine restart. Also reject decreasing counters, changed types, or nonpositive intervals. This example rejects gaps longer than thirty minutes as an explicit coverage policy.
GO
CREATE VIEW dbo.CounterIntervals
AS
WITH Paired AS
(
SELECT h.CaptureId, h.SampleUtc, h.EngineStartLocal,
h.EngineStartTicks, h.SampleTicks,
c.CounterObject, c.CounterName, c.CounterInstance,
c.CounterType, c.CounterValue,
LAG(h.SampleUtc) OVER w AS PreviousSampleUtc,
LAG(h.EngineStartLocal) OVER w AS PreviousEngineStart,
LAG(h.EngineStartTicks) OVER w AS PreviousStartTicks,
LAG(h.SampleTicks) OVER w AS PreviousTicks,
LAG(c.CounterType) OVER w AS PreviousType,
LAG(c.CounterValue) OVER w AS PreviousValue
FROM dbo.CounterCapture AS h
JOIN dbo.CounterValue AS c ON c.CaptureId = h.CaptureId
WINDOW w AS (PARTITION BY c.CounterObject, c.CounterName, c.CounterInstance
ORDER BY h.CaptureId)
), Checked AS
(
SELECT *, CASE WHEN CounterType IN (272696320, 272696576)
AND PreviousType = CounterType
AND PreviousEngineStart = EngineStartLocal
AND PreviousStartTicks = EngineStartTicks
AND CounterValue >= PreviousValue
AND SampleTicks - PreviousTicks BETWEEN 1 AND 1800000
THEN 1 ELSE 0 END AS ValidRateInterval
FROM Paired
)
SELECT CaptureId, SampleUtc, PreviousSampleUtc,
CounterObject, CounterName, CounterInstance, CounterType, CounterValue,
ValidRateInterval,
CASE WHEN ValidRateInterval = 1 THEN CounterValue - PreviousValue END
AS OperationDelta,
CASE WHEN ValidRateInterval = 1 THEN SampleTicks - PreviousTicks END
AS ElapsedMilliseconds
FROM Checked;
GO
SELECT SampleUtc, CounterName, CounterInstance, CounterValue,
OperationDelta * 1000.0 / NULLIF(ElapsedMilliseconds, 0) AS OperationsPerSecond,
ValidRateInterval
FROM dbo.CounterIntervals ORDER BY SampleUtc, CounterName;The WINDOW clause needs SQL Server 2022 and compatibility level 160 or higher. Check that setting before creating the view. At level 150, my test failed with Incorrect syntax near 'WINDOW'. Repeating the same OVER definition is an alternative on earlier supported versions.
I inspect rejected intervals before trusting a monthly comparison. A NULL rate marks unavailable interval evidence, not zero activity. A counter that resets and overtakes its old value between samples remains harder to detect.
For a performance counter baseline, fifteen-minute rates smooth short bursts. A snapshot gauge can miss a drop between captures entirely. Increase approved collection detail when investigating brief incidents.
Schedule the Performance Counter Baseline Without Running It
Use SQL Server Agent where that service is available and running. The job owner needs access to the monitoring database and required DMV permissions. The T-SQL step runs the collector in that database.
The following block is a parse-only test of the scheduling script. It creates no job and starts no collector. Parse-only checks syntax without compiling object references or validating permissions.
SET PARSEONLY ON;
GO
DECLARE @MonitoringDatabase sysname = DB_NAME();
DECLARE @JobId uniqueidentifier;
EXEC msdb.dbo.sp_add_job
@job_name = N'Counter Baseline', @enabled = 0, @job_id = @JobId OUTPUT;
EXEC msdb.dbo.sp_add_jobstep
@job_id = @JobId, @step_name = N'Capture selected counters',
@subsystem = N'TSQL', @database_name = @MonitoringDatabase,
@command = N'EXEC dbo.CaptureCounters;',
@on_success_action = 1, @on_fail_action = 2;
EXEC msdb.dbo.sp_add_jobschedule
@job_id = @JobId, @name = N'Every Fifteen Minutes', @enabled = 0,
@freq_type = 4, @freq_interval = 1,
@freq_subday_type = 4, @freq_subday_interval = 15,
@active_start_time = 0, @active_end_time = 235959;
EXEC msdb.dbo.sp_add_jobserver @job_id = @JobId;
GO
SET PARSEONLY OFF;
GOFor approved installation, remove the parse-only wrapper and run from the monitoring database. The resulting job and schedule remain disabled. Enable both after validating a manual capture and reviewing the execution identity.
Keep parser testing separate from an execution test. Parsing can't establish that a counter exists or that the job can collect it. It also doesn't prove the scheduled job will run successfully. In my test, the parse-only run reported no errors and created no job.
Compare Weekly Windows in the Performance Counter Baseline
This comparison defines the current week as the trailing seven days ending at one captured UTC instant. Its earlier window ends one calendar month earlier. Both windows have seven days, rather than sharing weekday labels.
Month subtraction follows calendar adjustment at short month ends. Use a different approved boundary rule when weekday alignment matters. Don't call four weeks ago one calendar month ago without explaining the difference.
DECLARE @ThisEnd datetime2(3) = SYSUTCDATETIME();
DECLARE @ThisStart datetime2(3) = DATEADD(day, -7, @ThisEnd);
DECLARE @PriorEnd datetime2(3) = DATEADD(month, -1, @ThisEnd);
DECLARE @PriorStart datetime2(3) = DATEADD(day, -7, @PriorEnd);
;WITH Periods AS
(
SELECT * FROM (VALUES
(N'Current seven days', @ThisStart, @ThisEnd),
(N'Seven days one month earlier', @PriorStart, @PriorEnd))
AS p(PeriodName, IncludedStart, ExcludedEnd)
)
SELECT p.PeriodName, p.IncludedStart, p.ExcludedEnd,
m.CounterObject, m.CounterName, m.CounterInstance,
SUM(CONVERT(decimal(28,4), m.OperationDelta) * 1000.0)
/ NULLIF(SUM(CONVERT(decimal(28,4), m.ElapsedMilliseconds)), 0)
AS WeightedOperationsPerSecond,
MIN(CASE WHEN m.CounterType = 65792 THEN m.CounterValue END) AS GaugeMinimum,
AVG(CASE WHEN m.CounterType = 65792
THEN CONVERT(decimal(28,4), m.CounterValue) END) AS GaugeSampleAverage,
COUNT(m.ElapsedMilliseconds) AS ValidRateIntervals,
SUM(m.ElapsedMilliseconds) / 3600000.0 AS CoveredRateHours,
SUM(CASE WHEN m.CounterType IN (272696320, 272696576)
AND m.ValidRateInterval = 0 THEN 1 ELSE 0 END) AS RejectedIntervals
FROM Periods AS p
LEFT JOIN dbo.CounterIntervals AS m
ON m.SampleUtc >= p.IncludedStart AND m.SampleUtc < p.ExcludedEnd
AND (m.CounterType = 65792 OR m.PreviousSampleUtc >= p.IncludedStart)
GROUP BY p.PeriodName, p.IncludedStart, p.ExcludedEnd,
m.CounterObject, m.CounterName, m.CounterInstance
ORDER BY m.CounterObject, m.CounterName, p.IncludedStart;The rate uses total valid increments divided by total valid elapsed time. It doesn't average differently sized interval rates equally. Gauge averages describe sampled readings, not continuous behavior between them.
Only rate intervals fully inside the selected window qualify. Missing history therefore reduces coverage instead of becoming a zero. Review capture timestamps and gaps alongside the comparison.
Keep Collection Healthy and Bounded
Review Agent failures and confirm captures continue to arrive. Set retention long enough to support the required comparisons. Index and prune history through the approved monitoring process as it grows.
Store history separately for each server if you centralize collection. Startup values and counter identities are server-specific. Never subtract samples originating from different instances.
A higher batch rate can accompany a busier workload rather than a regression. Compilations and lock waits also need workload context. The baseline points to questions for query-level investigation.
Keep the covered interval duration beside every reported rate. A valid average over a short observed portion doesn't represent an entire week. Compare coverage before interpreting the two periods as equivalent evidence.
Use capture failures and missing intervals as monitoring signals themselves. Check the job's history when expected samples disappear. Restart detection protects arithmetic, while collection alerts protect the usefulness of retained history.
Read the Baseline Before Giving a Verdict
Which reporting window and workload should the comparison represent? Define those before choosing the earlier samples. A remembered Tuesday afternoon is a poor measurement instrument.
Use a performance counter baseline to retain evidence with visible coverage limits. Verify collection and interval validity before interpreting a change. Then investigate the queries responsible for the observed workload.
Related reading on this blog: Reading Performance Counters in T-SQL Without Misreading Them and Real-Time Performance Views That Make Troubleshooting Easier.
A counter baseline is not a performance verdict, it is recorded evidence for a controlled comparison.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
