Calculate compilations per second from cumulative SQL Server counters, compare batch rates, and investigate single-use plans with context.
Reading Deadlock Graphs From the system_health Session
Read deadlock graphs from system_health, identify victims and resource owners, reproduce a two-session cycle, and choose a targeted fix.
Stale Statistics: Detecting and Updating Them
Detect stale statistics with update properties, review modification thresholds, and schedule targeted refreshes with recorded outcomes.
Scheduler ID Values in dm_os_schedulers: What the Big Numbers Mean
Scheduler ID values in dm_os_schedulers run from 0 up to a few thousand, and then jump past one million. This post groups the rows into user, dedicated admin connection and hidden schedulers, and shows the filter that keeps CPU scripts honest. It also shows why filtering on the number alone is fragile.
Active and Inactive VLFs: List Them for Every Database
To list active and inactive VLFs for every database, count the rows of sys.dm_db_log_info by vlf_active and read the log reuse wait next to them. This post builds a demo log with 24 VLFs, shows how an open transaction and small autogrowth steps add more, and fixes the count with one shrink and one regrow.
KEEP PLAN Hint: Cut Temp Table Recompiles in SQL Server
The KEEP PLAN hint gives a temp table the same recompile threshold as a permanent table. This post counts statistics recompiles with Extended Events for a plain query, a KEEP PLAN query and a KEEPFIXED PLAN query. It shows where the hint helps, where it does nothing and what the stronger hint costs.
Proving a Tuning Change Worked With Before and After Numbers
Prove a tuning change with before and after numbers from Query Store for CPU, reads, duration, and comparable execution volume.