Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

Chapter 14. Analyzing and Optimizing Query Performance

I hope you have seen that writing T-SQL is straightforward and fairly easy to learn. However, writing T-SQL is different from writing efficient T-SQL. Making sure that the code you write executes quickly and efficiently is very important. Too many times, I have seen developers create a very large and complex script with absolutely no thought to what the impact of the code will be on the database engine. This can lead to complicated, hard-to-diagnose performance problems down the road, especially if the inefficient code is encapsulated in one or more programming objects, such as stored procedures and functions.

This chapter will only scratch the surface of the massive topic of optimizing query performance because the primary focus is to teach you how to write good queries in the first place. Optimizing query performance also includes many other facets of database technologies, such as table indexing and statistics.

The best approach to writing efficient T-SQL is to understand how SQL Server retrieves data, and then writing your code to match the technology. This chapter provides a brief description of how the database engine processes queries and retrieves data, as well as some basic recommendations on which techniques to avoid and which techniques to leverage in your quest to write good queries. I won't go into low-level specifics here; the goal is for you to understand the basics of data retrieval so that you can leverage this ...