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.
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
See more SQL Server Performance Optimization examples here.
