Real Example #1
Some customers care about SQL Performance Tuning so much, that they use our database administrators for continuous Microsoft SQL Server performance tuning.
Once we tune, I noticed sometimes it was hard to communicate the benefit achieved.
We struggled a bit with reporting, which would communicate that was done. Quickly. So non-SQL Server person could understand.
We now send performance tuning reports that look like this:
Microsoft SQL Server Performance Tuning Report
1. Tuning Stored Procedure [LoadArchitecturalItems_MK]
First screenshot shows gains
Second screenshot shows OLD and NEW run times
Image above shows red/green block.
Red shows SQL Server Stored Procedure performance BEFORE tuning
Green – SQL performance AFTER tuning.
Just so you know we not blowing smoke.
2. Tuning Stored Procedure [LoadProblems_MK]
First screenshot shows gains.
Second screenshot shows OLD and NEW run times.
3. Tuning Stored Procedure: LoadViolationMK_2
– Added a filter for @ClientID in a CTE/Subquery.
– Replaced two temp tables to variable tables.
– Removed option recompile query hint.
This is our newest method of reporting performance tuning results
Here tuned and un-tuned stored procedure calls are mixed, but you can still see the actual performance numbers.
4. Multiple indexing changes
The column that matters here, is the last one.
avgDuration #1 – shows duration with old indexes before IX changes.
avgDuration #2 – shows duration after new indexing is deployed.
The last column “Delta avg duration” is the one that matters the most. It shows if stored procs are slower or faster.
You can see that a lot of them became better.
It’s a big table. Yep, I agree.
Let me walk you through it.
Avg CPU #1 column shows – CPU cycles needed to run this stored proc BEFORE tuning.
Avg CPU #2 columns shows – CPU cycles needed to run this stored proc AFTER tuning.
Delta Avg CPU column shows – the difference between BEFORE tuning vs. AFTER. Ifs its smaller number than #1 – then speed was improved.
Avg Reads #1 column shows – Storage read operations needed to run this stored proc BEFORE tuning.
Avg Reads #2 column shows – Storage read operations to run this stored proc AFTER tuning.
Delta Avg Reads – the difference between BEFORE vs. AFTER. If the number is smaller (1 vs 2) then speed was improved.
Duration – same thing. That’s the column that matters most. Users don’t care how many CPU or Storage operations it took to run something. They care how long they had to wait.
The final column is the most important – is shows total improvement across all parameters (CPU, Storage and Duration).
So this is how we report our SQL Server Performance tuning results.
Does it take extra time? Yes, absolutely.
Does the client get a better sense of what was accomplished? Yes, absolutely.
Agree/Disagree? Write a comment below.
See more SQL Server Performance Optimization examples here.
