Duration improvement after tuning
QUERY #1
QUERY #2
QUERY #3
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_extra_6__INC] on [oehdraud_sql]
Query #2. UPDATE bins set bin_flag=1:
Created index IX__bin_flag__INC on [<removed>_ldbld_bins]
Query #3. Tuning procedure <removed>_DealerPortal_UpdateCustomerLevel:
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 (or 4,100%) |
| CPU (ms)* | 83 | 15 | 5,53x (or 553%) |
| Disk (number of reads)* | 767 | 601 | 1,28x (or 128%) |
SELECT oeh.user_def_field…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 (or 205%) |
| CPU* | 39 | 15 | 2,6x (or 260%) |
| Disk* | 996 | 985 | 1,01x (or 101%) |
UPDATE bins set bin_flag=1…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 (or 802%) |
| CPU* | 67 | 16 | ~7x (or 670%) |
| Disk* | 323 | 111 | ~3x (or 291%) |
[redacted]_DealerPortal_UpdateCustomerLevelIn a nutshell, by tweaking these three queries, we slashed the total execution time (Duration) by a whopping 51 times!
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.
Speak with a SQL Expert
In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide operational peace of mind
