Performance Tuning Report – 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!
Mark Varnas

Mark Varnas

I love making performance tuning SQL Servers fast and making them more stable. And I channel that obsession into our SQL Managed Services and new content here. When I'm not writing about SQL, I spend time outside hiking, skiing, mountain biking, or trying a new recipe.

Leave a Reply

Your email address will not be published. Required fields are marked *

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: