Three audits, and what they found
Categories:
4 minute read
Three real audits, anonymised. The measurements are those of the collections, on the dates given.
One distinction is held throughout this page, and it matters: what was measured is written as measured, what the analysis computes as an expected gain is written as expected. No figure for a gain observed after a fix is put forward, because none of these three engagements has yet had a control collection.
An industrial group: one query, and 7.8 % of the processor
Capture of 18 June 2026, twenty-six minutes, 178,419 calls, 1,565 invoice documents processed. The application is a third-party package, and its SQL is generated.
Measured: a single query consumes 24.19 seconds of CPU over 24.2 seconds of duration. So it waits for nothing, it pegs one core, and since its CPU does not exceed its elapsed time it does not even spread across the others. It performs 9,637,784 logical reads to return 1,053 rows, which is 9,152 reads per row and 23 milliseconds of CPU per row. On its own it accounts for 7.8 % of all server CPU in the window.
Also measured, and it changes the conclusions: the instance is entirely cached, with 204 physical reads against 14.7 million logical ones. Those nine million reads are not disk, they are in-memory page walks. The gain to expect is therefore CPU and elapsed processing time, not I/O, which is a real win but a narrower one than the raw volume suggests.
Expected, as computed by the analysis: the rewrite recovers nearly all of those 24 seconds. Enabling read committed snapshot isolation removes 51 % of the measured blocked time and would have prevented 51 of the 66 captured deadlocks, at a version store cost of tens of megabytes. Removing the server-side cursors is worth 9 to 14 % of batch elapsed time and 63 % of the network round trips, but only 3 % of CPU.
That last spread is the whole point of an audit. Intuition presents the three changes as one bundle, and one of them is worth five times the others.
A software vendor: the new feature that addresses 2.5 % of the problem
Analysis of 28 July 2026, against a capture of blocked processes between 8 am and 8 pm.
Measured: 786 distinct blocking incidents and 122.7 minutes of blocked wait over the window. The question put was about a feature announced in SQL Server 2025 and presented as the answer to blocking.
Also measured, incident by incident: it addresses 20 of the 786, and 104.8
seconds out of 7,361, which is 2.5 % of incidents and 1.4 % of the time. The
leading cause of blocking, 41.7 % of wait time, is a queue on compile locks,
which the mechanism does not touch. The second, 39.1 %, is a queue on MERGE
statements, which are explicitly excluded from it.
The real finding of that engagement is elsewhere, and it carries no price tag: 2,032 procedures out of 3,525, that is 58 %, force uncommitted reads, on health data. The blocking was hidden, not resolved.
An audit is as useful for ruling a solution out as for putting one forward. The negative result is the one that saved a migration.
A line-of-business instance: paid for in 2022, running as 2016
Collection of 27 August 2026, 106 seconds, 82 queries completed, no missing permission. This is the instance whose full report is published as a sample deliverable.
Measured:
- the four tempdb files write at 1,694.7, 1,718.7, 1,720.0 and 1,862.0 milliseconds on average. A write to tempdb takes one and seven tenths of a second;
- 225,687,712 forwarded record reads since the restart, across 18 heaps, of which 198,152,342 on a single table of 46,464,519 rows;
- all 482 tables of the application database are heaps, none has a primary key, and there is no foreign key, no check constraint, no view and no stored procedure;
- the four database-scoped configurations brought by the 2019 and 2022 releases are enabled and inert, the compatibility level having stayed at that of 2016.
That last point has the best ratio of cost to return in the whole engagement. The licence paid for is the 2022 one, the optimizer running is the 2016 one, and the gap closes with a single command.
One reservation, carried in the report as it is here: 1,192 storage units out of 1,193 are uncompressed, for 52,285 Mb reserved and 645 million rows, but the gain from compression was not estimated. The command that quantifies it was not run during the collection, so no percentage is claimed.
What these three have in common
None of the problems above is a hardware problem, and none would have been seen without measurement. In all three cases the instance already held everything needed to say what was wrong.