Professional SQL Server® 2005 Performance Tuning

9.11. Summary

This chapter has covered a lot of ground. You may not be at checkmate with the optimizer, but you should be able to hold your own with the information that we've covered. You started with an in-depth dive into the details of the optimizer and the operators that it uses to execute T-SQL statements. This enabled you to read the execution plans and use this information to troubleshoot what may be going wrong with the way the optimizer is handling your T-SQL requests.

In the second half of this chapter, you looked at what you could do tactically to performance-tune T-SQL statements by examining the data provided to you in the execution plans and the profiler. You looked at approaches of rewriting and reorganizing T-SQL to get the most performance gains. You developed a troubleshooting protocol that looked at tuning T-SQL predicates and the effect of indexing on common query tasks. For the problems that you'll see daily, you examined some common T-SQL pattern and programming idioms to see best practices examples from a performance perspective. For the odd scenario, you followed an example of how to use statement-level hints to improve a bad plan-caching issue. There are no easy ways to succeed at chess; you have to simply play the game and learn from experience. The same rules apply when performance tuning T-SQL statement in SQL Server 2005. The best decisions are made with the most accurate data.