W h e n y o u ’ r e t a l k i n g a b o u t d a t a b a s e q u e r i e s , p e r f o r m a n c e i s a s u b j e c t i v e t e r m . W h i l e o n e e n d - u s e r 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 per- formance include:
Database design. Probably one of the most important factors influencing both query perfor- mance 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 sec- ond. 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 fragmentation increases, data will become spread out over more data pages. The more data pages your query needs to retrieve, the slower the query.