[Case Study] Tuning SQL Performance and making TSQL run by 16 times faster! How did we do it?

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.

sql performance tuning improvements chart

2. Tuning Stored Procedure [LoadProblems_MK]

First screenshot  shows gains.

Second screenshot  shows OLD and NEW run times.

sql server profiler graph

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

sql server profiler graph

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.

indexing case study graph

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.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *