Database Reference
In-Depth Information
Chapter 1
SQL Query Performance Tuning
Query performance tuning remains an important part of today's database applications. Yes, hardware performance
is constantly improving. Upgrades to SQL Server—especially to the optimizer, which helps determine how a query
is executed, and the query engine, which executes the query—lead to better performance all on their own. At the
same time, SQL Server instances are being put on virtual machines, either locally or in hosted environments, where
the hardware behavior is not guaranteed. Databases are going to platform as a service systems such as Amazon RDS
and Windows Azure SQL Database. You still have to deal with fundamental database design and code generation.
In short, query performance tuning remains a vital mechanism for improving the performance of your database
management systems. The beauty of query performance tuning is that, in many cases, a small change to an index
or a SQL query can result in a far more efficient application at a very low cost. In those cases, the increase in
performance can be orders of magnitude better than that offered by an incrementally faster CPU or a slightly
better optimizer.
There are, however, many pitfalls for the unwary. As a result, a proven process is required to ensure that you
correctly identify and resolve performance bottlenecks. To whet your appetite for the types of topics essential to
honing your query optimization skills, the following is a quick list of the query optimization aspects I cover in
this topic:
Identifying problematic SQL queries
Analyzing a query execution plan
Evaluating the effectiveness of the current indexes
Avoiding bookmark lookups
Evaluating the effectiveness of the current statistics
Understanding parameter sniffing and fixing it when it breaks
Analyzing and resolving fragmentation
Optimizing execution plan caching
Analyzing and avoiding statement recompilation
Minimizing blocking and deadlocks
Analyzing the effectiveness of cursor use
Applying in-memory table storage and procedure execution
Applying performance-tuning processes, tools, and optimization techniques to optimize
SQL workloads
 
Search WWH ::




Custom Search