[case study] SQL Performance Tuning and making TSQL run 7,675 (not a typo) times faster! How did we do it?

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
old: 687,157
new: 90
improvement: 7,635 times less IO
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.

We tune slow SQL Servers every day. For over a DECADE now. Contact us!

Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

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