Database Reference
In-Depth Information
Before jumping straight into 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. It's important
to have a process you follow in order to be able to find and identify performance problems, fix those problems, and
document the improvements you've made. Without a well-structured process, you're going to be stabbing in the
dark, hoping to hit a target. 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 versus price
•
The performance baseline
•
Where to focus efforts in tuning
•
The top 13 SQL Server performance killers
What I don't cover within these pages could fill a number of other topics. The focus of this topic is on T-SQL query
performance tuning, as the title says. But, just so you're clear, there will be no coverage of the following:
•
Hardware choices
•
Application coding methodologies
•
Server configuration (except where it impacts query tuning)
•
SQL Server Integration Services
•
SQL Server Analysis Services
•
SQL Server Reporting Services
•
PowerShell
The Performance Tuning Process
The performance tuning process consists of identifying performance bottlenecks, prioritizing the identified issues,
troubleshooting their causes, applying different resolutions, and quantifying performance improvements—and then
repeating the whole process again and again. It is necessary to be a little creative, since most of the time there is no
one silver bullet to improve performance. The challenge is to narrow down the list of possible causes and evaluate the
effects of different resolutions. You can even undo previous modifications as you iterate through the tuning process.
The Core Process
During the tuning process, you must examine various hardware and software factors that can affect the performance
of a SQL Server-based application. You should be asking yourself the following general questions during the
performance analysis:
•
Is any other resource-intensive application running on the same server?
•
Is the capacity of the hardware subsystem capable of withstanding the maximum workload?
•
Is SQL Server configured properly?
