SQL Server Index Tuning Example
Speed improved by 600%
Some SQL Server Consulting clients chose to tune databases periodically.
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.
Here is a recent example of performance tuning slow MS SQL Server Stored Procedure. We now send performance tuning reports that look like this:
dbo.SP_CUSTINFO_XXXX in [SpringXXXX] database.
Before
After
Improvements achieved
See how after Nov 23rd, it reduced the CPU, IO, etc.
What changes do we make?
- Added a new non-clustered index.
A single IX change may affect SQL performance in multiple places, multiple queries, multiple stored procedures, SQLAgent jobs, SSIS packages, etc.
Technical Background
Most SQL Servers bottleneck is on Disk access (or disk “reads”).
It’s not CPU or RAM – which most customers often suspect first.
And that makes much sense. Here is why.
Inefficient queries scan (or read) a lot of data. Data read in is stored in RAM. As more data is read in, “older” data is pushed out from RAM. If there isn’t enough RAM to keep ALL data in memory (which is often not possible), SQL Server has to read from disk – and that is the slowest operation SQL Server can do.
When the query can be tuned to read 10 rows vs. 10M – less CPU and RAM automatically are necessary. Therefore, tuning for less disk “reads” is often the primary goal.
Nothing is more critical to the end-user than Speed (or Duration of the query), in any case.
Tuning to reduce CPU/RAM resources is helpful too.
When queries are tuned to need less CPU & RAM, the same server now has more capacity. That’s means that the same server can process double or triple the load. Which means it extends the lifespan of the same server. This means hardware upgrades can be pushed out further into the future.
