Performance is frequently one of the last things on anyone's minds when they're developing a system. Unfortunately, that means it usually becomes the biggest problem after that system goes to production. You can't simply rely on getting a phone call that tells you that procedure X on database Y that runs on server Z is running slow. You have to have a mechanism in place to find this information for yourself. You also can't work off the general word slow. Slow compared to what? Last week? Last month? The way it ran in development? And once you've identified something as running slow, you need to identify why. Does it need an index? Does it have an index that it isn't using? Is it the CPU, the disk, the memory, the number of users, the amount of data? Now you've identified what and why, but you have to do something about it. How? Rewrite the query? Change the WHERE clause? The questions that will come your way when you start performance tuning are endless.
This book provides you with the tools you need to answer those questions. I'll show you how to set up mechanisms for collecting performance data on your server for the SQL Server instances and databases living there. I'll go over the more tactical methods of collecting data on individual T-SQL calls. Along the way, I'll be discussing index structure, choice, and maintenance; how best to write your T-SQL code; and a whole slew of other topics. One of my goals when writing this book was to deliver all these things using examples that resemble the types of queries you'll see in the real world, at your desk. The tools and methods presented are mostly available with SQL Server Standard Edition, although some are available only with SQL Server Enterprise Edition. These are called out whenever you might encounter them.
The main point is to learn how to answer all those questions that are going to be presented to you. This book gives you the tools to do that and to answer those questions in a methodical manner that eliminates much of the guesswork that is so common in performance optimization today. Performance problems aren't something to be feared. With the right tools, you can tackle performance problems with a calmness and reliability that will earn the respect of your peers and your clients and that will contribute directly to their success.
This book is for just about anyone responsible for the performance of the system. Database administrators, certainly, are targeted because they're responsible for setting up the systems, creating the infrastructure, and monitoring it over time. Developers are too, because who else is going to generate all the well-formed and highly performant T-SQL code? Database developers, more than anyone, are the target audience, if only because that's what I do for work. Anyone who has the capability to write T-SQL, design tables, implement indexes, or manipulate server settings on the SQL Server system is going to need this information to one degree or another.
The purpose of this was to use as many "real-looking" queries as possible. To do this, I needed a "real" database. I could have created one and forced everyone to track down the download. Instead, I chose to use the sample database created by Microsoft, called AdventureWorks2008. This is available through CodePlex (http://www.codeplex.com/MSFTDBProdSamples). I suggest keeping a copy of the restore handy and resetting your sample database after you have read a couple of topics from the book. Microsoft updates these databases over time, so you might see different sets of data or different behavior of some of the queries than that listed in the book. To a degree, the book builds on the knowledge presented from previous chapters. However, most of the chapters present information unique within that topic, so it is possible for you to jump in and out of particular chapters. You will still receive the most benefit by a sequential read of Chapter 1 through Chapter 16.
Chapter 1, "SQL Query Performance Tuning," introduces the iterative process of performance tuning. You'll get a first glimpse at establishing a performance baseline, identifying bottlenecks, resolving the problems, and quantifying the improvements.
Chapter 2, "System Performance Analysis," starts you off with monitoring the Windows system on which SQL Server runs. Performance Monitor and user-defined functions are shown as a mechanism for collecting data.
Chapter 3, "SQL Query Performance Analysis," defines the best ways to look "under the hood" and see what kind of queries are being run on your system. It provides a detailed look at the Profiler and trace tools. Several of the most useful dynamic management views and functions used to monitor queries are first identified in this chapter.
Chapter 4, "Index Analysis," explains indexes and index architecture. It defines the differences between clustered and nonclustered indexes. It shows which types of indexes work best with different types of querying. Basic index maintenance is also introduced.
Chapter 5, "Database Engine Tuning Advisor," covers the Microsoft tool Database Engine Tuning Advisor. The chapter goes over in detail how to use the Database Engine Tuning Advisor; you're introduced to the various mechanisms for calling the tool and shown how it works under real loads.
Chapter 6, "Bookmark Lookup Analysis," takes on the classic performance problem, the key lookup, which is also known as the bookmark lookup. This chapter explores various solutions to the bookmark lookup operation.
Chapter 7, "Statistics Analysis," introduces the concept of statistics. The optimizer uses statistics to make decisions regarding the execution of the query. Maintaining statistics, understanding how they're stored, learning how they work, and learning how they affect your queries are all topics within this chapter.
Chapter 8, "Fragmentation Analysis," shows how indexes fragment over time. You'll learn how to identify when an index is fragmented. You'll see what happens to your queries as indexes fragment. You'll learn mechanisms to eliminate index fragmentation.
Chapter 9, "Execution Plan Cache Analysis," presents the mechanisms that SQL Server uses to store execution plans. Plan reuse is an important concept within SQL Server. You'll learn how to identify whether plans are being reused. You'll get various mechanisms for looking at the cache. This chapter also introduces new dynamic management views that allow more access to the cache than ever before.
Chapter 10, "Stored Procedure Recompilation," displays how and when SQL Server will recompile plans that were stored in cache. You'll learn how plan recompiles can hurt or help the performance of your system. You'll pick up mechanisms for forcing a recompile and for preventing one.
Chapter 11, "Query Design Analysis," reveals how to write queries that perform well within your system. Common mistakes are explored, and solutions are provided. You'll learn several best practices to avoid common bottlenecks.
Chapter 12, "Blocking Analysis," teaches the best ways to recognize when various sessions on your server are in contention for resources. You'll learn how to monitor for blocking along with methods and techniques to avoid blocked sessions.
Chapter 13, "Deadlock Analysis," shows how deadlocks occur on your system. You'll get methods for identifying sessions involved with deadlocks. The chapter also presents best practices for avoiding deadlocks or fixing your code if deadlocks are already occurring.
Chapter 14, "Cursor Cost Analysis," diagrams the inherent costs that cursors present to set-oriented T-SQL code. However, when cursors are unavoidable, you need to understand how they work, what they do, and how best to tune them within your environment if eliminating them outright is not an option.
Chapter 15, "Database Workload Optimization," demonstrates how to take the information presented in all the previous chapters and put it to work on a real database workload. You'll identify the worst-performing procedures and put them through various tuning methods to arrive at better performance.
Chapter 16, "SQL Server Optimization Checklist," summarizes all the preceding chapters into a set of checklists and best practices. The goal of the chapter is to enable you to have a place for quickly reviewing all you have learned from the rest of the book.
You can download the code examples used in this book from the Source Code section of the Apress website (http://www.apress.com). Most of the code is straight T-SQL stored in .sql files, which can be opened and used in any SQL Server T-SQL editing tool. There is one PowerShell script that will have to be run through a PowerShell command line.
You can contact the author, Grant Fritchey, at [email protected]. You can visit his blog at http://scarydba.wordpress.com.