Before vs. After Perf Tuning Report #2 - 67,871% improvement
Real example #2
Today I am sharing another MS SQL Performance tuning report from recent tuning.
After making couple of small tweaks to the SQL Server stored procedure we were able to get few improvement:
Run time duration improved by: 10,788%
CPU improved by: 10,903%
Disk improved by: 46,180%
Overall stored procedure improvement: 67,871%
What exactly was changed?
-Split a subquery in four parts using UNION
-Replaced ISNULL function in WHERE clause
I may start calling these “Scalpel tuning”.
Why does disk improvement matter for stored procedure speed?
Its 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 cars? The speed is still the same, because 55 cars dont overload that highway. What if you add another 500 or 5000? Now you 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 is important. The less hits there is to storage, the more capacity there is available. And the more future cars we can put on it.
Questions? Comments? Write below.