Chapter 17. Performance Considerations

for RuBoard

Happiness isn't anything more than having something to look forward to, and, conversely, unhappiness is borne of dread , of things you don't look forward to. The trick is in arranging things so that you have more of the former than the latter.

H. W. Kenton

Normally, when I approach the subject of performance tuning, I attack it from the perspective of things you can do to speed up your code. People love stuff like thatoften it's why they buy books like this in the first place. There are few things as satisfying as seeing a simple technique dramatically speed up your code.

It seems these days that everyone's in a hurry. We all want fast-food-style technology tidbits as quickly as we can get them. Never mind depth of understanding or a grasp of the overall philosophy employed in a technology. We just want the goods, and we want them yesterday .

So, I suppose the most direct approach to discussing SQL Server performance tuning might be to list common performance problems and their resolutions . We could go through the various mistakes people make when writing Transact-SQL code and designing SQL Server databases, and talk about ways of remedying them. In short order, we could come up with a fairly full "toolbox" of techniques that you could whip out on a moment's notice to address a variety of performance tuning scenarios.

This is certainly the approach I took in the Performance Tuning chapter of my previous book, The Guru's Guide to Transact-SQL . I think there's a place for this, and I find solutions cookbooks just as useful as the best textbooks . That said, it seems to me that understanding the reasoning behind the how-to is actually more important and will benefit you more in the long run than simply having a truckload of tips dumped on you, particularly when it comes to performance tuning. As I said in the Preface, understanding the philosophy behind a technology is more important than learning its syntax. Your ability to tune your Transact-SQL stored procedures is a product of your knowledge and understanding of SQL Server itselfhow it works, what it does when it processes a query, what resources it needs to formulate an efficient plan, and so forth. So, in this chapter, I'll discuss the internals of SQL Server query processing. I'll talk about the various query stages and what happens at each one. As you come to understand how SQL Server query processing works for yourself, you'll develop your own methods of speeding up T-SQL code. And you'll use techniques that are sensible and safe because you'll have a good understanding of how SQL Server was designed to work.

for RuBoard