When you're talking about database queries, performance is a subjective term. While one end-user could be happy with a query that takes ten seconds to execute, another user might not be happy unless all queries run under one second. While performance requirements are certainly diverse, so are the methods used to improve performance. A few key factors that impact SQL Server query performance include:
- Database design. Probably one of the most important factors influencing both query performance and data integrity, design decisions impact both read and modification performance. Standard designs include OLTP-normalized databases, which focus on data integrity, removal of redundancy, and the establishment of relationships between multiple entities. This is a design most appropriate for quick transaction processing. You'll usually see more tables in a normalized OLTP design, which means more table joins in your queries. Data warehouse designs, on the other hand, often use a more denormalized Star or Snowflake design. These designs use a central fact table, which is joined to two or more description dimension tables. For Snowflake designs, the dimension tables can also have related tables associated to it. The focus of this design is on query speed and not on fast updates to transactions.
- Physical hardware. I once spent a day trying to get a three second query down to one second. No matter which indexes I tried to add, or query modifications I made, I couldn't get its duration lowered. This was because there were simply too many rows required in the result set. The limiting factor was the I/O. A few months later, I migrated the database to the higher-powered production server. Lo and behold, the query executed consistently in less than one second. This underscores the fact that CPU, memory, and RAID subsystems can have a significant impact (both positive and negative) on query performance. As a matter of fact, memory is one of the most critical resources for the SQL Server, allowing it to cache data and execution plans for faster data retrieval and query execution. If you're prioritizing where your server dollars go, don't be stingy with the memory.
- Network throughput. The time it takes to obtain query results can be impacted by a slow or unstable network connection. This doesn't mean that you should blame the poor network engineers whenever a query executes slowly-but do keep this potential cause on your list of areas to investigate.
- Index fragmentation. As data modifications are made over time, your indexes will become fragmented. As ragmentation increases, data will become spread out over more data pages. The more data pages your query needs to retrieve, the slower the query.
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 completing 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 chapter 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 efficient 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 basing its execution decisions on stale statistics.
Read more about Performance Tuning in this sample chapter from Joe Sack's SQL Server 2005 T-SQL Recipes or click the image to the left to purchase this book from Amazon.