Why SQL Server Performance Tuning Matters
I've had a good week this week. I've been working on SQL Server performance tuning for a large client here in Melbourne. I've been spending a week a month doing that for them.
Today's results are why I love this work. No matter how much you tune your .NET code, you don't get returns like you can with database tuning. I've turned on statistics IO to document the change in the proc I worked on today. How cool is this? It used to take 9,383,786 logical page reads to execute the proc. It now takes 11 (yes eleven). Eat your heart out upper-layer coders :-)
Original proc
Table 'TableA'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 696588, logical reads 3093369, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableC'. Scan count 0, logical reads 2089764, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableD'. Scan count 698103, logical reads 2097440, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableE'. Scan count 5, logical reads 2103203, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableF'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableG'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Modified proc
Table 'TableA'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableE'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(table names changed to keep the client's details private)