Query performance tuning is an important part of today's database applications. Often you can achieve large savings in both time and money with proper query performance tuning.
Hardware performance is constantly improving, which can lead to an attitude suggesting that other methods of performance tuning are no longer important. 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. 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.
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, here is a quick list of the query optimization aspects I cover in this book:
-
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
-
Analyzing and resolving fragmentation
-
Optimizing execution plan caching
-
Analyzing and avoiding stored procedure recompilation
-
Minimizing blocking and deadlocks
-
Analyzing the effectiveness of cursor use
-
Applying performance-tuning processes, tools, and optimization techniques to optimize SQL workload
Before jumping straight in to these topics, let's first examine why we go about performance tuning the way we do. In this chapter, I discuss the basic concepts of performance tuning for a SQL Server database system. I detail the main performance bottlenecks and show just how important it is to design a database-friendly application, which is the consumer of the data, as well as how to optimize the database. Specifically, I cover the following topics:
-
The performance-tuning process
-
Performance vs. price
-
The performance baseline
-
Where to focus efforts in tuning
-
The top 11 SQL Server performance killers
|