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.
• Share of total wait matters more than raw milliseconds. pct_of_total_wait 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: The Collector and the Delta, Run Twice

The collector step run twice, about four minutes apart, so the delta script has two snapshots to compare:

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_total_wait
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_total_wait (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_total_wait 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.