Some customers care about SQL Performance Tuning so much that they use our database administrators for continuous Microsoft SQL Server performance tuning.
This one of those clients.
Problem Summary
These stored procedures were running very frequently (multiple times per minute) and consuming many server resources (CPU, storage reads, and duration).
The stored procedures were performing multiple index scans and causing too much Disk Operations.
Improvements achieved
An overall disk improvement up to 35X! See the details below:
dbo.SP_GetXXXX in [SpringXXXX] database.
Before
After
Do you want more?
Look below, the chart with the full time:
What changes made this query run faster?
- Added 1x non-clustered indexes.
Technical Background
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 cars? 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.
Need your SQL Server Performance Tuned? We have a service that does just that!
We would love to help you!
