Part 4. Performance Tuning and Optimization
Edited by Kalen Delaney
There are many different aspects to performance tuning for SQL Server, including tracking down hardware and network issues, monitoring the SQL Server service’s use of memory and processor resources, analyzing the causes of blocking, and investigating suboptimal index usage and query plans, to name only a few areas. Performance problems can result in a general slowdown of SQL Server and the applications running against it, or can just impact individual queries or processes, or even individual users.
Because there are so many areas that could be the source of performance problems, there is no one best way to troubleshoot poor performance. Most SQL Server professionals have a few areas of the product where they tend to focus, so those people might start their troubleshooting efforts in one of those areas.
Microsoft provides a host of tools to help you track down the source of performance problems. The earliest versions of SQL Server enabled you to see your query plans (albeit, not graphically) through an option called SHOWPLAN or to examine the system’s behavior using Performance Monitor. Slightly later versions started including the ability to trace the activities your SQL Server was involved in, starting with the basic ability to monitor the client-to-server communication and progressing to the ability to see every action of SQL Server, either internal or external. The most recent version includes the amazing ability to set up a data warehouse of performance data that can easily be analyzed using a set of built-in reports. The tool even provides recommendations for how to remedy the problems encountered. So does this mean we don’t need to understand how to troubleshoot on our own, because SQL Server can now do everything for us? Of course not. The tools have always given you a head start, and made things easier when starting on your SQL Server journey. But the more you know, the better you’ll be able to determine which tool can give you the information you need, and what you need to do to solve the problems, including problems that are not detectable by the tools.
This section includes a wonderful selection of articles from 10 different experts, each describing one area of performance troubleshooting. There is a focus on indexes because that’s an area where you can see immediate and drastic performance improvements. But the section doesn’t just cover indexing options; it includes articles on deadlocking, using metadata to diagnose system resource problems, examining disk I/O behavior, and using SQL Server’s ability to correlate the tracing tool with Performance Monitor.
I wish to thank all the SQL Server MVPs for their ongoing generosity in providing support to the SQL Server community, and especially to the authors for helping to make this book a reality.
Kalen Delaney has been working with SQL Server for almost 22 years, and writing about it for almost 20. In addition to writing deep technical books such as Inside SQL Server and SQL Server 2008 Internals, she writes courseware and delivers advanced SQL Server training around the world.
Kalen has been invited by Microsoft to present special internal training on many occasions. She has spoken at Microsoft Technical Education Conference (TechEd) and presented at every PASS (Professional Association for SQL Server) Summit since the organization’s inception. She is delighted that her love for SQL Server can be put to such good use in the publication of this book.