Here is what can be done during a session of SQL performance tuning.
Improvement after tuning
Minimize indexing for faster SQL Server performance; excess indexes slow it down.
Problem
The stored procedure below was one of the most called on the client’s SQL Server.
The procedure was overusing resources:
- Duration: 227 ms
- Average CPU: 50 ms
- Disk, number of disk reads: 2,496
The solution
Here is how we improved that:
- Dropped the
[IX_ClientID_OrderDate] - Added the index
[IX_ClientID_OrderDate_WorkOrderNumber]
That’s it! That simple!
Before vs. after
Here's the before-and-after snapshot that showcases the efficiency gains:
We took 5 separate runs to analyze after the fix:
| Duration (ms) | CPU (ms) | Disk (8k page reads) | ||||
|---|---|---|---|---|---|---|
| Before | After | Before | After | Before | After | |
| Run 1 | 289 | 1 | 63 | 1 | 2,496 | 8 |
| Run 2 | 99 | 1 | 31 | 1 | 2,496 | 8 |
| Run 3 | 114 | 1 | 31 | 1 | 2,496 | 8 |
| Run 4 | 314 | 1 | 63 | 1 | 2,496 | 8 |
| Run 5 | 322 | 1 | 62 | 1 | 2,496 | 9 |
| AVG | 227 | 1 | 50 | 1 | 2,496 | 8 |
Disk, number of reads
| Before Tuning | After Tuning | Improvement (%) | Improvement (X) | |
|---|---|---|---|---|
| Duration* | 227 | 1 | 22,700 | 227 |
| CPU* | 50 | 1 | 5000 | 50 |
| Disk* | 2,496 | 8 | 31,200 | 312 |
| Overall improvement: | 58,900% faster | 589x faster |
[GetNextWorkOrderNumberByGroupID] runs.Clearly, with just a few tweaks to indexing, the query execution time plummeted by 589 times!
Duration, ms
Why do you merge two indexes into a single one?
Because indexing is not free. The fewer indexes you can get away with, the faster your SQL Server runs.
Indexes can greatly improve query performance in the SQL Server, but they may hamper the performance too.
So, it’s very important to understand your overall workload and find a good balance between making your queries efficient and not paying too much for that.
