Duration improvement after tuning
Tuning SQL Server to cut down on CPU time and disk read operations makes everything run smoother and faster, boosting both performance and reliability.
Problem
Three slow stored procedures significantly affected the SQL Server's performance.
Solution
Here is what we did to make these queries run much faster:
Query #1. SELECT that runs every 30 minutes:
Created index [IX_ref_4__INC] on [ordhdrlog_sql]
Query #2. UPDATE:
Created index IX__bin_status__INC on [whs_load_bins]
Query #3. Tuning procedure PartnerPortal_UpdateAccountTier:
Replace two temporary tables with Common Table Expressions (CTE)
Before vs. After
Query #1
| Duration (ms) | CPU (ms) | Disk (8k page reads) | ||||
|---|---|---|---|---|---|---|
| Before | After | Before | After | Before | After | |
| Run 1 | 203 | 4 | 93 | 16 | 943 | 601 |
| Run 2 | 125 | 4 | 73 | 15 | 592 | 601 |
| AVG | 164 | 4 | 83 | 15 | 767 | 601 |
| Before | After | Improvement | |
|---|---|---|---|
| Duration (ms)* | 164 | 4 | 41x |
| CPU (ms)* | 83 | 15 | 5.53x |
| Disk (number of reads)* | 767 | 601 | 1.28x |
SELECT...Query #2
| Duration (ms) | CPU (ms) | Disk (8k page reads) | ||||
|---|---|---|---|---|---|---|
| Before | After | Before | After | Before | After | |
| Run 1 | 44 | 21 | 47 | 16 | 979 | 985 |
| Run 2 | 49 | 27 | 47 | 15 | 1,003 | 985 |
| Run 3 | 41 | 21 | 31 | 16 | 991 | 985 |
| Run 4 | 39 | 17 | 31 | 16 | 1,012 | 985 |
| AVG | 43 | 21 | 39 | 15 | 996 | 985 |
| Before | After | Improvement | |
|---|---|---|---|
| Duration* | 43 | 21 | 2.05x |
| CPU* | 39 | 15 | 2.6x |
| Disk* | 996 | 985 | 1.01x |
UPDATE...Query #3
| Duration (ms) | CPU (ms) | Disk (8k page reads) | ||||
|---|---|---|---|---|---|---|
| Before | After | Before | After | Before | After | |
| Run 1 | 584 | 149 | 47 | 16 | 325 | 76 |
| Run 2 | 1,290 | 180 | 31 | 16 | 325 | 130 |
| Run 3 | 1,352 | 73 | 125 | - | 320 | 129 |
| AVG | 1,075 | 134 | 67 | 16 | 323 | 111 |
| Before | After | Improvement | |
|---|---|---|---|
| Duration* | 1,075 | 134 | ~8x |
| CPU* | 67 | 16 | ~4x |
| Disk* | 323 | 111 | ~3x |
PartnerPortal_UpdateAccountTierIn a nutshell, the first query now runs 41 times faster.
The other two run 2 times and 8 times faster.
Final thoughts
When you tune SQL Server to use less CPU time, you're basically giving your server a break, letting it do more work with less effort.
This means your queries fly faster, and you can get more done at once without the server breaking a sweat.
Plus, it's a win for your budget too, because your hardware lasts longer and runs smoother.



