Introduction
Performance is frequently one of the last things on peoples’ 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? And now you’ve identified what and why, 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 metrics 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; how to test that 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. 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. Almost all the tuning advice in the book is directly applicable to SQL Azure, as well as to the more earthbound SQL Server 2012.
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.
Who This Book Is For
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.
How This Book Is Structured
The purpose of this book 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 AdventureWorks2008R2. 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 with some of the queries than what is listed in this book. But, I stuck with the older version because it’s likely to be a little more stable. To a degree, this 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 reading of Chapter 1 through Chapter 17.
Downloading the code
You can download the code examples used in this book from the Source Code section of the Apress website (http://www.apress.co). Most of the code is straight T-SQL stored in a .sql file, which can be opened and used in any SQL Server T-SQL editing tool. There are a couple of PowerShell scripts that will have to be run through a PowerShell command line.
Contacting the Author
You can contact the author, Grant Fritchey, at [email protected]. You can visit his blog at http://scarydba.com.