Professional SQL Server® 2005 Performance Tuning

9.3. End Game: Performance Tuning T-SQL

This tuning business can be a slippery slope for analytical types. You can get so distracted by analyzing the data and the thrill of reducing costs that you spend hours optimizing things that have minimal user impact. Focus on the things that have the greatest impact to the end user. Ultimately, that means make it faster. Do you want to make things faster? Reduce the I/O in the query. Focus particularly on I/O that is caused when disk heads are moving around. Assuming that the hardware guys have optimized disk placement, we are left with making changes to our T-SQL requests that make use of good indexing schemes and as few passes as possible on these indexes. Measure results, measure results again, give up some minor gains in efficiency for maintainability, add some common sense, and you've got it.

The methodology that we are using involves reading the query execution plan, measuring actual results or key performance indicators (KPIs), and examining index and schema designs for performance enhancements. When performance tuning T-SQL this is our general process flow:

1. Continue to educate yourself on fundamentals of database design, indexes, and optimizer logic. This is really a prerequisite and an iterative task that through familiarity will enable you to see patterns well before you can back it up with analytical data.

2. Run the query and study the execution plan. Study is the operative word. The more you study, the easier the patterns will ...