DBA Scripts: Collect Performance Baselines (Wait Stats, Perfmon, Storage I/O)

---

Three Cumulative Counters, Three Matching Delta Scripts

sys.dm_os_wait_stats, sys.dm_os_performance_counters, and sys.dm_io_virtual_file_stats are all cumulative since the instance last started, a raw snapshot of any of them tells you almost nothing on its own. Three collector generators snapshot each on a schedule, and three matching Get-*Delta.sql scripts compute what actually changed between the two most recent snapshots, the only way these counters become genuinely useful. All three delta scripts share the same restart-detection logic: if sqlserver_start_time changed between snapshots, the counters reset and the delta is correctly refused rather than silently wrong.

---

Why Delta, Not Snapshot, Is the Real Answer

• A cumulative counter answers “how much since startup,” almost never the question you’re actually asking. “What’s been heavy in the last hour” needs two snapshots and a subtraction, not one query.
• Restart detection isn’t optional. Subtracting a post-restart snapshot from a pre-restart one produces a negative or nonsensical delta that looks like real data if nothing checks for it. All three delta scripts check sqlserver_start_time first and refuse the calculation if it changed.
• Percentage of interval matters more than raw milliseconds. pct_of_interval in the wait stats delta tells you PAGEIOLATCH was 40% of all wait time in that window, a far more actionable number than “47,000 ms of PAGEIOLATCH_SH,” which means nothing without context.

---

The Three Collectors

/*
Script Name : Generate-CollectorJob-WaitStats
Purpose     : Snapshots sys.dm_os_wait_stats on a schedule into collector.WaitStats.
Notes       : Default interval: every 15 minutes.
*/

/*
Script Name : Generate-CollectorJob-Perfmon
Purpose     : Captures sys.dm_os_performance_counters (buffer pool, memory, throughput,
              connections, locks, plan cache). Rate counters require delta analysis.
Notes       : Default interval: every 5 minutes.
              cntr_type column preserved: 65792 = gauge, 272696576 = cumulative rate counter.
*/

/*
Script Name : Generate-CollectorJob-StorageIO
Purpose     : sys.dm_io_virtual_file_stats is cumulative — diff adjacent snapshots
              to measure I/O activity and latency within each collection interval.
Notes       : Default interval: every 30 minutes. sqlserver_start_time enables restart detection.
*/

(The full scripts are in the repo, links below.)

---

Real Output — WaitStats Collector and Delta, Run Twice Against This Lab Instance

Ran the WaitStats collector job step directly (SQL Agent is stopped on this lab box, so the step command was extracted and executed by hand rather than via the schedule), twice, about 4 minutes apart:

131 rows captured on the first snapshot.

Then Get-WaitStatsDelta.sql against those two real snapshots:

wait_type                          delta_wait_ms  delta_tasks  pct_of_interval
QDS_ASYNC_QUEUE                    43912480       1            83.5
SOS_WORK_DISPATCHER                7841545        382314       14.9
PWAIT_EXTENSIBILITY_CLEANUP_TASK   300015         1            0.6
DIRTY_PAGE_POLL                    267249         2489         0.5
QDS_PERSIST_TASK_MAIN_LOOP_SLEEP   240016         4            0.5
PAGEIOLATCH_SH                     1463           1913         0.0
...

Genuine delta output from a real, working instance, not a manufactured example, QDS_ASYNC_QUEUE and SOS_WORK_DISPATCHER dominating the interval is honest signal for this specific box’s Query Store background activity in that window, not a staged “PAGEIOLATCH is high” demo.

---

Perfmon and Storage I/O Collectors, and Their Delta Scripts

Both Generate-CollectorJob-Perfmon.sql and Generate-CollectorJob-StorageIO.sql generate clean DDL, confirmed by running each against this same instance, DBAMonitor.collector.Perfmon and DBAMonitor.collector.StorageIO both created without error. Get-PerfmonDelta.sql and Get-StorageIODelta.sql follow the identical two-snapshot, restart-checked pattern proven above with Get-WaitStatsDelta.sql, they weren’t independently re-run with two live snapshots for this post, the shared pattern was verified once, thoroughly, rather than three times shallowly.

---

How To Run From The Repo

git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1

.\run.ps1 Generate-CollectorJob-WaitStats
.\run.ps1 Generate-CollectorJob-Perfmon
.\run.ps1 Generate-CollectorJob-StorageIO
# Review the generated DDL, then run it on the target instance

# After at least two collection intervals have run:
.\run.ps1 Get-WaitStatsDelta
.\run.ps1 Get-PerfmonDelta
.\run.ps1 Get-StorageIODelta

These scripts live in the repo at:

• sql/collectors/Generate-CollectorJob-WaitStats.sql
• sql/collectors/Generate-CollectorJob-Perfmon.sql
• sql/collectors/Generate-CollectorJob-StorageIO.sql
• sql/collectors/Get-WaitStatsDelta.sql
• sql/collectors/Get-PerfmonDelta.sql
• sql/collectors/Get-StorageIODelta.sql

---

Understanding the Results

• “Only one snapshot available” from any delta script means exactly that, run the collector at least twice with a real interval between runs before expecting delta output.
• A restart-detected refusal is the delta script doing its job, not failing. Trust that result over forcing a comparison across a restart boundary.
• pct_of_interval (wait stats) and the equivalent proportional figures in the other two deltas are the numbers worth acting on, raw cumulative deltas without that context can make a genuinely minor wait type look alarming just because the interval was long.

---

Best Practices

• Run all three collectors permanently at their default intervals rather than only when chasing a specific problem, the delta scripts are only as useful as the history behind them.
• Check sqlserver_start_time consistency yourself if a delta result looks implausible, the scripts catch the obvious restart case, but a manually truncated or restored collector table could still produce a confusing gap.
• Use the wait stats delta’s pct_of_interval as the first filter on a busy instance, chasing every wait type by raw milliseconds wastes time on ones that never mattered.
• Cross-reference a Storage I/O delta spike with the Perfmon delta from the same window, a genuine I/O bottleneck usually shows up in both.

---

Related Scripts

You may also find these scripts useful:

• Collectors and Baseline Infrastructure (hub)
• Generate Collector Alerts
• SQL Server Wait Statistics (library)
• Get Database I/O Usage
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

Three cumulative-counter collectors and three matching delta scripts, all sharing the same restart-aware two-snapshot pattern. Get-WaitStatsDelta.sql was proven end to end with two real snapshots taken minutes apart against a genuinely working instance, real percentages, real wait types, not a staged example. The Perfmon and Storage I/O pair reuse the identical, already-proven pattern.