Performance Tuning Report #11 – 7,675x faster!

SQL Server Tuning Case Study

This is the case when tuning sometimes can not be done perfectly.

We are trying to change DB code, so nothing else needs to change.

perf-tuning-report-11

In some cases, even though the result set is exactly the same, rows may come back in the different order back to the app.

In those cases, we have to work with the client application team to review that different order of the rows is not going to break the application.

I know a lot of you will say “it shouldn’t” – but for the large enterprise apps you never know what type of stupidity is inside them. It may not. Or it may.

The point is to be careful.

Here are more details on what we did.

 

 

Problem: spPartsReceived stored procedure takes 40-60s to execute and times out in the app.
 
Why: SP is performing multiple index scans and causing too much Disk Operations.
 
Change: Re-written code is attached spPartsReceived_new_version.sql line 56 to 61 and line 66 to 68.
Testing: Test value of  3026685 was used for both old and new SP. The result set is the same, but the data row ordering is different.
Next steps: We recommend making sure that data order difference is not going to cause problems before implementing the change.
Performance gains:
Execution time
Old execution = 40s
New = (under) 1s
Improvement: 40 times faster
Reads
old: 687,157
new: 90
improvement: 7,635 times less IO
Actual
Table ‘tblRcptheader’. Scan count 9, logical reads 28,911, physical reads 0
Table ‘tblRcptitem’. Scan count 9, logical reads 363,865, physical reads 0
Table ‘tblLineItems’. Scan count 9, logical reads 294,381, physical reads 0
New version 
Table ‘tblRcptheader’. Scan count 5, logical reads 27, physical reads 0
Table ‘tblRcptitem’. Scan count 1, logical reads 27, physical reads 0

Table ‘tblLineItems’. Scan count 5, logical reads 36, physical reads 0

IO-tuning-report-11
Need your SQL Server Performance Tuned? We have service that does just that!
Would love to help you!

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

4.5/5

42

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.