My promise in every engagement is a plain explanation for each change, a way to undo it, and every script we use. You get an agreed scope and an action plan, without a promised percentage of speed.
This is part 2 of my six-part consulting series. It explains the promise and shows how to check performance evidence yourself.
Define the Promise of Every Engagement Before Speed
The same change doesn't produce the same outcome on every server. Workload, existing configuration, and the cause of the complaint differ. A percentage promised before diagnosis hides those differences.
I ask what slow means to the person using the application. A server counter alone doesn't describe that person's wait. Start with the screen, report, or operation that needs improvement.
My commitment is to the work and its explanation. I don't promise a result or a refund based on a performance number. Once payment is made and the meeting is scheduled, it isn't refundable.
That rule belongs in the booking decision. It shouldn't appear as a surprise after the work. The useful question is what the engagement provides and how your team can evaluate it.
What Every Engagement Leaves With Your Team
The Comprehensive Database Performance Health Check costs USD 2,432 at a fixed price. It provides up to four hours, usually two to four. There is no hourly meter running for that Health Check.
These are the working commitments your team can inspect. They concern process and retained work. They don't turn a diagnostic session into a guaranteed speed target.
- We work on Zoom while you share the screen and run everything.
- No usernames or passwords are handed over, no remote control is taken, and nothing is copied off your systems.
- Every finding gets a plain explanation, and you decide to do it, study it, or skip it.
- Every change comes with a way to undo it, and I recommend testing it in a similar environment first.
- You keep every script, checklist, and prompt we use.
- You leave with an action plan either way, including work that remains.
Most fixes start within the first 75 minutes. Most changes need no downtime and no restart. Those service descriptions don't promise that a particular server's problem will finish within the session.
I keep the decision attached to the finding. A helpful recommendation still needs your authorization. Work that requires more testing belongs in the plan instead of being forced through a live meeting.
Separate Session Work From Follow-Through
Some actions fit the agreed session after testing and approval. Others need application changes, a maintenance window, or another team's review. Record those dependencies while the evidence is still visible.
Give each remaining task a named owner and an acceptance check. Keep the reason for the task beside its proposed action. That prevents a useful diagnosis from becoming an unexplained list of settings.
Your team's later work deserves the same care as the changes made during the call. Use the retained scripts to repeat the evidence capture. Review the outcome against the agreed symptom and baseline.
In every engagement, the retained work should make the next decision easier. An action plan records the path forward even when implementation remains. A meeting ending on time doesn't finish somebody else's approval queue.
Measure One Busy Interval Before Changing Anything
Wait counters accumulate since startup or their last explicit reset. A raw total mixes old and current activity. Capture two samples during the same defined workload interval and subtract them.
This script waits one minute between captures. Run it during a representative busy period under an approved diagnostic account. It changes no server setting and writes only session-local temporary results.
DECLARE @StartTime datetime2(3) = SYSUTCDATETIME();
DECLARE @EngineStart datetime = (SELECT sqlserver_start_time FROM sys.dm_os_sys_info);
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #WaitBefore FROM sys.dm_os_wait_stats;
SELECT database_id, file_id, num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms
INTO #IoBefore FROM sys.dm_io_virtual_file_stats(NULL, NULL);
WAITFOR DELAY '00:01:00';
SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #WaitAfter FROM sys.dm_os_wait_stats;
SELECT database_id, file_id, num_of_reads, num_of_writes,
io_stall_read_ms, io_stall_write_ms
INTO #IoAfter FROM sys.dm_io_virtual_file_stats(NULL, NULL);
IF @EngineStart <> (SELECT sqlserver_start_time FROM sys.dm_os_sys_info)
THROW 51110, 'The engine lifetime changed. Capture a new baseline.', 1;
IF EXISTS
(
SELECT 1 FROM #WaitBefore AS b
LEFT JOIN #WaitAfter AS a ON a.wait_type = b.wait_type
WHERE a.wait_type IS NULL OR a.wait_time_ms < b.wait_time_ms
OR a.waiting_tasks_count < b.waiting_tasks_count
OR a.signal_wait_time_ms < b.signal_wait_time_ms
)
THROW 51111, 'Wait counters reset or disappeared. Discard this interval.', 1;
SELECT TOP (20) a.wait_type,
a.wait_time_ms - COALESCE(b.wait_time_ms, 0) AS IntervalWaitMs,
a.signal_wait_time_ms - COALESCE(b.signal_wait_time_ms, 0) AS IntervalSignalMs,
a.waiting_tasks_count - COALESCE(b.waiting_tasks_count, 0) AS IntervalWaitCount
FROM #WaitAfter AS a
LEFT JOIN #WaitBefore AS b ON b.wait_type = a.wait_type
WHERE a.wait_time_ms > COALESCE(b.wait_time_ms, 0)
ORDER BY IntervalWaitMs DESC;
SELECT @StartTime AS CaptureStartUtc, SYSUTCDATETIME() AS CaptureEndUtc;Keep the before and after samples available in that connection. Don't clear wait counters between them. Downward-counter checks catch visible resets, but a reset that overtakes the earlier total can escape that check.
Wait time adds across workers and isn't a stopwatch for the application. Background waits also need interpretation. Treat the largest delta as an investigation lead rather than an automatic root cause.
On a quiet test server, background waits such as SOS_WORK_DISPATCHER fill the top of this list. The script's own WAITFOR shows up there too. Filter those out before you read anything into the result.
For these SQL Server diagnostics, older versions require VIEW SERVER STATE. SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE. Read-only means no configuration change, not unrestricted permission or zero processing cost.
Add Queries, File Latency, and User Timing
Inspect cached statement totals for CPU and logical reads separately. Their counters cover the life of the cached plan. They aren't automatically restricted to the preceding minute.
SELECT TOP (10) sql_handle, statement_start_offset, statement_end_offset,
creation_time, last_execution_time, execution_count,
total_worker_time AS TotalCpuMicroseconds, total_logical_reads
FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC;
SELECT TOP (10) sql_handle, statement_start_offset, statement_end_offset,
creation_time, last_execution_time, execution_count,
total_worker_time AS TotalCpuMicroseconds, total_logical_reads
FROM sys.dm_exec_query_stats ORDER BY total_logical_reads DESC;Use Query Store for a retained database history when it's enabled and capturing the relevant workload. Select comparable runtime intervals there. Cached totals disappear with eviction or cache clearing, so preserve their lifetime context.
Use the file samples from the earlier block to calculate interval latency. A zero operation count has no measured average latency. NULL communicates that absence more accurately than a reported zero milliseconds.
SELECT a.database_id, a.file_id,
(a.io_stall_read_ms - b.io_stall_read_ms) * 1.0
/ NULLIF(a.num_of_reads - b.num_of_reads, 0) AS AverageReadMs,
(a.io_stall_write_ms - b.io_stall_write_ms) * 1.0
/ NULLIF(a.num_of_writes - b.num_of_writes, 0) AS AverageWriteMs
FROM #IoAfter AS a
JOIN #IoBefore AS b ON b.database_id = a.database_id AND b.file_id = a.file_id
WHERE a.num_of_reads >= b.num_of_reads AND a.num_of_writes >= b.num_of_writes
AND a.io_stall_read_ms >= b.io_stall_read_ms
AND a.io_stall_write_ms >= b.io_stall_write_ms;Record the user-facing operation's duration too. Use the same inputs and timing method before and after. Distinguish database execution from network delay and application rendering when interpreting the result.
Keep an explicit workload description with the baseline. Name the operation and inputs, without copying private values into a shared report. Record whether the capture includes a scheduled batch or an unusual maintenance task.
A per-execution average from cached totals still reflects every execution represented by that plan entry. It doesn't replace an interval measurement. Compare the same statement, plan context, and workload before interpreting a changed average.
Avoid Comparisons That Change the Question
Monday morning and Friday night represent different workloads. Matching time of day also needs comparable activity and inputs. Record concurrent load and recent changes beside the capture.
A restart resets important DMV evidence and clears the plan cache. A deliberate cache clear also changes compilation conditions. Don't interpret totals from different lifetimes as matching before-and-after measurements.
Repeat the chosen capture after the change using the same method. Preserve both sets of findings, including disappointing results. A smaller server counter doesn't prove the customer's screen improved.
Watch the operation that users identified, even when a server-wide average improves. Another query can dominate the server totals while the reported symptom persists. Keep the acceptance check attached to the original complaint.
If source data changed between tests, record that change as another input. Different row populations alter work even under identical SQL. A before-and-after chart should disclose that difference rather than treating it as proof of tuning.
State which findings are measured and which still need investigation. Retain the queries used to obtain the evidence. Your team should be able to repeat the capture without relying on a remembered explanation.
Review remaining work with its owner before closing the session. An approved next step can involve more testing instead of an immediate change. That distinction keeps the written plan realistic and the decision visible.
Questions to Ask Before Every Engagement
What is promised? Plain explanations, an undo path, every script, and an action plan are the commitments. The Health Check's price is fixed, without an hourly meter.
Do we keep the scripts and have an undo path? Yes, your team keeps the materials and every change gets an undo path. Findings remain your decision to do, study, or skip.
What happens if the time runs out, and what is the refund rule? You leave with an action plan either way. Payment isn't refundable once made and the meeting is scheduled.
For every engagement, ask those questions before evaluating a sales claim about speed. For help applying the process, see the Consulting page. Bring the symptom and the baseline you want to compare.
Related reading on this blog: Query Store Wait Stats: Why a Query Was Slow, Not Just That It Was and Script to List Database File Latency.
A useful consulting promise is not a percentage on a slide, it is work and evidence your team can inspect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
