Performance Tuning Report – 67,871% gain

Real example

Today I am sharing another MS SQL Performance tuning report from recent tuning. 

After making a couple of small tweaks to the SQL Server stored procedure we were able to get few improvements:

Run time duration improved by: 10,788%

CPU improved by: 10,903%

Disk improved by: 46,180%

Overall stored procedure improvement: 679x faster!

What exactly was changed?


-Split a subquery into four parts using UNION

-Replaced ISNULL function in WHERE clause

That’s it.

I may start calling these “Scalpel tuning”.

Microsoft SQL performance tuning chart
scalpels

Why does disk improvement matter for stored procedure speed?

It’s simple. The less you access the disk; the more disk capacity is left over. 

It works just like a highway. Say you have 3 lane highway. And 5 cars use it every 1min. What if you add 50 vehicles? The speed is still the same because 55 cars don’t overload that highway. What if you add another 500 or 5000? Now you are starting a slowdown in traffic. They all still get home. But not at the same speed anymore.

Same with SQL Servers. That’s why speed tuning most critical resources are essential. The fewer hits there is to the storage, the more capacity there is available. And the more future cars we can put on it.

Questions? Comments? Write below.

Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

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

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: