CHAPTER 28 ■ PERFORMANCE TUNING
Appropriate indexing. In addition to watching for fragmentation, you need to make sure only useful indexes are kept on your tables. Your table indexes should be based on your high priority or frequently executed queries. If a query is executing thousands of times a day and is com- pleting in two seconds, but could be running in less than one second with the proper index, adding this index could reduce the I/O pressure on your SQL Server instance significantly. You should create indexes as needed, and remove indexes that aren’t being used (this chap- ter shows you how to do this). Each index on your table adds overhead to data modification operations, and can even slow down SELECT queries if SQL Server decides to use the less effi- cient index. When you’re initially designing your database, it is better for you to keep the indexes at a minimum (having at least a clustered index and nonclustered indexes for your foreign keys). Instead, wait to add additional indexes until after you have a better idea about the queries that will be executed against the database.
Up-to-date statistics. As I discussed in Chapter 22, the AUTO_CREATE_STATISTICS database option enables SQL Server to automatically generate statistical information regarding the distribution of values in a column. If you disable this behavior, statistics can get out of date. Since SQL Server depends on statistics to decide how to execute the query, SQL Server may choose a less than optimal plan if it is basing its execution decisions on stale statistics.
In this chapter, I’ll demonstrate the commands and techniques you can use to help troubleshoot and evaluate your query performance. You’ll also learn how to address fragmented indexes and out-of- date statistics, and evaluate the usage of indexes in the database. I’ll introduce you to a few graphical interface tools such as SQL Server Profiler, graphical execution plans, and the Database Engine Tun- ing Advisor. This chapter will also review a few miscellaneous performance topics, including how to use sp_executesql as an alternative to dynamic SQL, how to apply query hints to a query without changing the query itself, and how to force a query to use a specific query execution plan.
Query Performance Tips
Before I start the discussion of the commands and tools you can use to evaluate query performance, I’d first like to briefly review a few basic query performance tuning guidelines. Query performance is a vast topic, and in many of the chapters I’ve tried to include small tips along with the various content areas (such as stored procedures, views, and triggers). Since this is a chapter that discusses query performance independently of specific objects, the following list details a few query-performance best practices to be aware of when constructing SQL Server 2005 queries (note that indexing tips are reviewed later in the chapter) :
In your SELECT query, only return the columns that you need. Don’t underestimate the impact of narrow result sets. Fewer columns in your query translate to less I/O and network bandwidth.
Along with fewer columns, you should also be thinking about fewer rows. Use a WHERE clause to help reduce the rows returned by your query. Don’t let the application return twenty thou- sand rows when you only need to display the first ten.
Keep the FROM clause under control. Each table you JOIN to in a single query can add additional overhead. I can’t give you an exact number to watch out for, as it depends on your database’s design, size, and columns used to join a query. However, over the years, I’ve seen enormous queries that are functionally correct, but take much too long to execute. Although it is con- venient to use a single query to perform a complex operation, don’t underestimate the power of smaller queries. If I have a very large query in a stored procedure that is taking too long to execute, I’ll usually try breaking that query down into smaller intermediate result sets. This usually results in a significantly faster generation of the final desired result set.