SQL Server 2005 Practical Troubleshooting: The Data Engine by August Hill, Cesar Galindo-Legaria, Sameer Tejani, Santeri Voutilainen, Slava Oks, Wei Xiao, Bart Du

SQL Server 2005 Practical Troubleshooting: The Data Engine

  • Author: August Hill, Cesar Galindo-Legaria, Sameer Tejani, Santeri Voutilainen, Slava Oks, Wei Xiao, Bart Du
  • Softcover: 480 pages
  • Publisher: Addison-Wesley(Dec 8 2006 12:00AM)
  • Book dimensions: 9 x 6.9 x 1.1 inches
  • ISBN: 0321447743
  • ITBookworm score: 3.5 /5

Score Reasoning

I really wanted to score this book higher, but the material is just so inconsistent. Had all the chapters been on the same level, this book has the potential for 5-stars.


I suppose let me start by saying that I wanted more. I guess that’s what it really boils down to. In Ken Henderson’s own words, this is the dream team of SQL Server knowledge. And with names like Bob Ward, Bart Duncan and Slava Oks I can see how. The problem is that if you’re going to get the dream team to do a book, I would hope that they would give us more. There are some chapters that I found to be a little light on the dream team knowledge that I expected from a line-up like this. There’s no other way to say it really. So all that being said, the big question is what did I think of the material that did make the book? Well, a lot of it was excellent. There are a couple chapters that are extremely informative. Ch. 4 alone may be worth the price of the entire book. I learned tons from what these guys had to say, I only wish they said more of it. The format was a bit off as well. Some chapters really hit home with the theme of the title and actually gave you some solid troubleshooting advice and actual scenarios that you could follow along with yourself. Others simply gave theory of the concepts being discussed with little practical troubleshooting. The only reason I’m scoring this book below 4-stars is because of its inconsistency. I know there’s probably going to be a sequel for the next release of SQL Server, and all of these guys will be much more experienced at writing by then, so I think we’ll see a much more polished work. Be sure to see my chapter reviews below for a full synopsis of what’s really discussed. I do love the book though. It has actually helped me out of 2 troubleshooting issues that would have been much harder had I not been able to just look them up here, so this book is definitely worthy of staying on my shelf. Seriously, it may be inconsistent, but it’s well worth the read. You won’t regret buying this one. I see this book being used as a desktop reference that you use when your server goes down for whatever reason. Of course, there’s more to it than that. There is lots of advice on query tuning, and how the tuning engine works, so you can learn something from just reading the chapters. It’s not necessary to read them in order though. Each one stands just fine on its own. In fact, for this review, I didn’t read them I order. There were chapters I found so interesting that I read them a couple times and then looked up specific facts as I forgot them.

Who is this book really for?

This book isn’t for the faint of heart or the beginner. Experienced DBAs will get the most out of this.

Writing Style

I can’t really say that this book has much of a style. It’s very inconsistent because it was written by several authors, and none of them were really experienced writers. They’re excellent techs, but as writers go, they’re excellent techs. I’m not saying the writing’s bad, it’s just that the level of explanation and even material isn’t the same in every chapter. So here I’d say that some chapters are really thorough while others are pretty slim.


The book is put together very well. I’ve been abusing this thing for a couple months now and the spine is still in excellent condition. This is a good solid book that will last a long time.

Table of Contents

Chapter 1: Waiting and Blocking Issues

Here we start out with wait types. The biggest thing I get out of books like this is the little things like picking up an extra trace flag, or a new way to use a DMV. Invariably I almost always end up learning something extra about the topic that I didn’t know before… just some little unexpected bit of knowledge. This chapter definitely has that element. You get right into identifying blocking and there’s a nice discussion on why not to use sysprocesses sys.dm_exec_requests. This discussion sets the tone for the book really in that it gives you just enough information to let you know that the caveat exists, but not enough to really be useful. I would have liked to see at least one example of the solutions so I could really solidify the limitations of these troubleshooting methods. You then move into statistically identifying blocking. It identifies a couple DMVs and has some nice code there to illustrate the discussion. There’s really some good discussion here. I don’t think you’ll be bored. Next you get into a discussion on the cause of blocking. You’ll mainly deal with DMVs here, and while it’s interesting discussion (esp if you’ve never had to troubleshoot stuff like this in Yukon), it doesn’t go into a lot of depth. OK, now we come to latches, and like pretty much every other resource I’ve ever read, it does a pretty inadequate job of explaining what they do, and when and how they do it. You’ll definitely pick up a couple new things here, but if you’ve never heard of latches, don’t expect this to give you the inside scoop. You do however get into a brief discussion of latch classes and their descriptions. It’s typically only a brief description, and it does a good job at times, and not so much at others. Locks are next and there are more DMVs to be learned. It’s got some good lock information here. I like it. It discusses the different types of locks and even gives some code to demonstrate the point. External wait types are also discussed and while it doesn’t go into as much detail as the section on locking, you’ll still learn something. There are plenty of other discussions to be had here as well. IO wait types, other wait types, deadlocking, etc. And there’s lots more code to be had too. This is a big chapter and in my opinion, one of the better ones.

Chapter 2: Data Corruption and Recovery Issues

This one starts out with storage internals. There’s some really good info on the resource database, so don’t miss this discussion. Then you move directly into catalog views and base system tables. We’re still on the basics here so don’t expect too much. Right away it goes into a query of sys.objects and lists pages and pages of result set from this query. It’s a little tedious. Now we go into allocation structures and there’s a nice diagram of the allocation architecture. After that it goes into database checksums, fast recovery, and deferred transactions. They’re all very short discussions so don’t expect more than a survey of each one. Next there’s a very short discussion on backup/restore enhancements. Again, it’s not anything detailed. The next discussion is definitely worthwhile though… DBCC CheckDB enhancements. I did pick up a couple extra tidbits here. Data recovery best practices are next. This is a list of things you can do to both minimize your exposure to a disaster, and to maximize your efforts afterwards. Now we come to the real meat of the chapter… the data recovery troubleshooting scenarios. I’m not gonna go into what all it discusses, but I think this is what this book was meant to be. It goes into many many scenarios of data corruption, failures, errors, etc. There’s some really good stuff here and you owe it to yourself to read this at least a couple times.

Chapter 3: Introduction to Windows Memory Management

First let me start by saying that this is a really good chapter, but I really feel he could have gone into some more explanation on some things. In fact, if it weren’t for Ken Henderson’s internals book, I wouldn’t have understood some of the concepts on the explanations in this chapter alone. OK, getting past that, it’s not that Slava did a bad job, because he didn’t. He’s obviously an expert, and he really has some good discussions, so let’s get into them. He starts out talking about VAS, which is a topic that is almost unheard of in any other book. If you don’t know what VAS is, then you should get this book because as it turns out it’s very important. Then there’s a pretty decent table that shows process entities that can consume user mode VAS. Then he gets directly into external virtual memory and internal physical memory. A really useful section on memory pressure is next. I think that anyone wanting to understand memory in Windows should read this section especially for the table on memory pressure and corresponding resource types. It’s not a large table, but I think it breaks it down into its simplest parts and allows you to understand it better. In the next section however, he just barely touches on NUMA, so if this is important to you, you should go somewhere else. He gets right into SQLOS after that. He does a pretty good job of explaining the different parts, but I really think he fails to put them back together into a whole picture for you. This is the big shortcoming of the chapter, and where Ken Henderson’s book really shines. So if you find yourself in the middle of this book and not really understand how everything fits together, then try Ken’s book and it should shed some light on some of these concepts. Of course, now that I’ve said that, I have to go ahead and say that the way he drops DMVs out there to tell you how to monitor or troubleshoot what he’s talking about at the time is probably where this chapter shines. DMVs are so misunderstood, and there’s so little known about them that any info we get is just excellent. Notice though that I’m not really going into too many specifics on what discussions he engages in. That’s mostly because there are just too many individual topics covered. I will go ahead and say though that you should really study each of these topics and even if the chapter doesn’t go into tons of detail on some of them, you’ll at least get some exposure so you can seek out further study. Next, there’s a pretty big section on troubleshooting different memory issues. In some cases I do think that he assumes a little too much knowledge, but that just proves that this book isn’t for beginners.

Chapter 4: Procedure Cache Issues

Ok, this is Bart Duncan’s chapter. He’s local here in Dallas, and he was the first author of this book I interviewed. Not only was he a great interview, but seriously just a great guy. After talking to Bart, there’s just no doubt that he really knows what he’s doing. Ok, so let’s see how well he did as a writer. He starts out talking about procedure cache architecture. The discussion is integral to understanding the rest of the chapter. He does a good job of describing the types of caches objects, but I would have liked to have seen a little more discussion. Just like in the last chapter, Bart sprinkles some good DMVs throughout his writing… just gold. Procedure cache specifics are next and this is really the meat of the chapter. Bart covers everything from plan sharing to different types of cache plans. He also goes into compilation and parameterization. These are pretty hot topics and definitely worth the exposure he gives them. As for parameterization, I like the discussion, but it kinda left me wanting more. Don’t get me wrong here… I learned some things I didn’t know before, and since parameterization is such an unknown topic, even having read this chapter puts me way out in front of everyone else. Still, I think he could have explained it just a little better and given a more complete picture of how it all fits together. One thing I do like about the discussion though is that he gives some coded examples to show exactly what he’s talking about. So you can at least recognize what parameterization issues look like. Next he talks about what cache lookups look like. He gets into plan reuse and some common problem cache problems. Throughout, he sprinkles some good DMVs in there as well as some SQL code to show you how to use them. He even gives you some of the differences between a couple of the DMVs and the traditional methods for getting the same info… good stuff. Parameter sniffing is the next big topic and Bart gives a pretty good discussion. And again, there’s some good SQL code to show you what’s going on. Unlike most of the other chapters, Bart gives you some exercises to actually teach you how to deal with some of these things. He does it by topic, so plan reuse, and high CPU would be handled separately. This is one of my favorite chapters and it honestly makes the price of the book worth it.

Chapter 5: Query Processor Issues

This chapter starts out with the same query processor basics you see in many other books. It then gets into a pretty quick discussion on hashing and sorting. Again, there’s nothing really new yet but it’s good groundwork. Then there’s another fairly short section on query compilation. And again, so far, there’s nothing new. Most of the material to this point is covered in plenty of other sources. Next you get into large queries and computed columns. They’re pretty high-level discussions, but there are a couple points worth picking up, so it’s worth reading. There are a couple other topics discussed next including autoparameterization and cursors. And while these are decent discussions, if you’re a fan of Ken’s books, you won’t really pick up anything new here. Now you get into a little bit on compilation time and sp_executesql. It’s good discussion, but honestly, quite a bit of it was already covered in Bart’s chapter. One of the problems I see with this chapter so far is that it’s too high level. It just touches on topics, and doesn’t really give you anything to learn… just a couple notes on some general concepts and that’s it. It gets into indexing next and I’ll give him one thing, the author actually uses the proper plural of index. Even SQL Server BOL doesn’t get that one right. There’s some good stuff here, but again, it’s too high-level. This book is supposed to be for seasoned DBAs, and this isn’t cutting it so far. The last half of this chapter concentrates on troubleshooting with DMVs. Honestly, if you already know the basics of query compilation you can just skip to this part. I’m not gonna go into specifics of what’s actually discussed, but it is worthwhile discussion.

Chapter 6: Server Crashes and Other Critical Failures

OK, so Bob Ward wrote this one, and I actually had a chance to talk to Bob about his work here. We were sitting in Ken Henderson’s office and had a very cool talk about not only his chapters in this book, but also some other specific SQL Server topics. Anyway though, this chapter is definitely something you’ll want in your corner when have a real crash of some kind. Before I get into specifics of what it covers, I’m gonna take a couple mins to tell you how to get the most out of it. For the most part, reference material isn’t worth much if you don’t know what it covers, so the best way to use this chapter is to read it more or less front to back once. Then, when you have that initial reading out of the way, just kinda keep it around for the future in case you ever need to look up something specific. I say this because again, if you don’t know what’s in the chapter, you could be wasting your time when you have a real crash. And you’d be surprised what will stick with you and you may not even need to look it up at all. I would also give it a quick gander every 2-3mos just to kinda keep abreast of what it says. Now that the preaching is out of the way, here’s the chapter review. As with many of the other chapters we start out here with some fundamentals. Startup parameters are first, followed by a walkthrough of the error log on startup. It’s actually a pretty good walkthrough for beginners because a lot of times they’re not all that sure what all the lines in the log mean, so this is a good start. Then there’s just a little intro to SQL and Windows exceptions which is all mostly common sense I guess, but since it’s so short it’s not annoying. Yukon enhancements are covered next, and it starts out right away talking about DMVs. This is a really good discussion because it goes into problems with the SQL2K way of doing things and shows what DMVs are here to solve. It also gives some top DMVs and how they map to the old way. Most of these are covered in Bart’s chapter, but you know what, you really can’t hear this stuff too many times. Next you get into a quick discussion on the DAC and some quick caveats for its use. Then there are a couple quick hits on dump enhancements and the SAC. Troubleshooting different types of SQL startup failures gets some pretty good play here. There are plenty of screenshots and explanation to get you going in the right direction. The next major section is SQL stack dumps. I’m up in the air on this one because he provides some decent discussion on the topic and explains a couple things, but that’s also rounded out by quite a few pages of stack dump code. However, if you’re really into that kinda thing, then you’ll be in heaven here. He really does cover a lot though. He gets into short stack dumps, exception signatures, etc. He also goes into dump on error. Next you get into some steps for troubleshooting a server hang. He gives you some quick things to check to help you determine the problem. He also gives some perfmon counters as well as some more DMVs. That pretty much finishes up the chapter.

Chapter 7: Service Broker Issues

Here you start out with a quick overview of what service broker is for, and some common SB objects and terms. Then you get right into some of the primary diagnostic tools and methods for troubleshooting SB. This is given in the form of a table with some small paragraphs to explain each of the items in the table. Then you get an SB troubleshooting walkthrough that obviously steps you through troubleshooting a scenario. It’s really kinda hard for me to judge this chapter since I really don’t know hardly anything at all about SB, but with all the code given, it has to be worth something… doesn’t it? Next you get into some other SB diagnostic tools like perfmon, checkdb, and views. Each one is discussed with some decent detail. There’s a pretty detailed description of a lot of the SB DMVs. It’s really a complete list of diagnostic tools, so be sure to catch this one.

Chapter 8: SQLOS and Scheduling Issues

Here we start out with SQLOS architecture and how it lays out with the physical components of the server. Then there’s a quick discussion of schedulers and tasks and workers, then we’re zipped off to configuration and troubleshooting. Now each of the above sections gets some detailed play and the more I read this chapter, the more I liked it. There’s some really good information here, and before I thought that this whole SQLOS thing was really just sort of a marketing ploy to make it sound a lot cooler than SQL2K, but it really is pretty cool. And in the middle of all this, yep, you guessed it… more DMVs. Ya gotta love it. There are some pretty specific things in there and I just loved. The discussion on load balancing tasks between schedulers is right up there. So is the discussion on tasks and workers, max worker thread configuration, and lightweight pooling. There’s really so much more in there, but I’m not going to go into any more simply because I wouldn’t do it justice. It’s a good chapter and has some good solid basics. It’s worth a couple reads.

Chapter 9: Tempdb Issues

Aside from troubleshooting, supporting, coding, and configuration, tempdb seems be the least understood part of SQL Server by the general DBA public. Here you start out with some of the very basics of tempdb, but you get into improvements in Yukon immediately. There’s actually a pretty good discussion on this one, so it’s an excellent place to start for understanding tempdb in Yukon and how it will behave differently from SQL2K. Next you get into how space is consumed in tempdb. It goes into some definitions of the different terms used inside tempdb. Practical troubleshooting is next, and it tells you far more than you’ve ever heard before. There are only a handful of pages on the topic, but it’s still pretty good.

Chapter 10: Clustering Issues

This chapter starts out with a couple quick definitions of clustering terms and then jumps right into tools you’ll need to troubleshoot clustering issues. This is more of a problem-solution kinda chapter, so you’re actually walking through a specific issue one step at a time. Once those basics are out of the way, you start getting into application performance in your clustering scenario. Then you get to add a node to the cluster. There’s a bulleted list of the steps to follow. Then it talks about failing over… why it failed over, and why it took so long. It’s a pretty short discussion, but it’s interesting. It finishes with a couple last minute pieces of advice. This is the shortest chapter in the book. It comes in at about 10 pages. I personally would have liked to see more. There’s a lot that got left out and that makes this chapter inconsistent with the rest of the book. This chapter won’t make you an expert in clustering, and it won’t really even tell you all that much. What it will do though is give you a couple basics and show you how to go about troubleshooting a relatively simply clustering scenario.

-Sean McCown, ITBookworm.com

Tags: , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *