Prof. SQL Server 2008 Internals & Troubleshooting

The bottom top line: This book needs to be in your library. If you are a DBA, a developer, a line manager or a support person who works with SQL Server 2005 or 2008 this book needs to be in your library.

Full Disclosure

I got this book for free courtesy of one of the authors (SQL Server MVP, Master, Blogger, Presenter, etc. – Brent Ozar (@BrentO on twitter)). It was a review copy and it even had a corny little note (something about my eyes and the way the light hits them.. I can’t repeat the rest). That’s not why I like this book – I like it because it is

incredibly useful and practical. More on that throughout but a quick note – I can’t really think of another way to describe most of the chapters other than really useful. To me, it is a very high compliment. This is a book (as you can see in the picture) that gets a lot of use. It is a book that is actually a tool in the toolbox for solving problems. Yes it is great for learning as well but most of the content can be put to use straight away by a DBA of any level.

Overview

Finally! A book that combines internals and “hit the ground running” practical tips and tricks. This book seems to really have the perfect balance of knowledge beyond what most have in the career field and real life, day job applications of that knowledge. I enjoy reading about storage best practices in other books but this book actually takes you on a journey to test that configuration out, for example (instead of just suggesting you test it and moving on). In depth where it needs to be with items like memory architecture or locking and latching, you’ll get academic knowledge of SQL Server out of this one, for sure. Yet incredibly useful with tools you can obtain for free in diagnosing your own problem, this book may even save you some consulting dollars (at least in the beginning phases of incidents). You can see more about the book and its authors at the official site. You can order it from the site or I’ll have links at the bottom (yes, I get a small percentage of any sales made through my Amazon links at bottom. Helps me bring you the blog 😉 ).

Where Else Can You Spend $40 And Get:

• An inside understanding of the “basics” – Chapters like:
  • SQL Server Architecture – Looking at SQL through the example of what a query experiences you’ll meet the Checkpoint process, Recovery Models, the various engines that make up SQL Server and understand the way SQL was built and the way it organizes access to your data. No rabbit trails, just the things that will make you a better developer or DBA if you understand. Want to go even deeper? Understand these concepts first and then pick up the Internals book, go to a week long course with Paul Randal and Kimberly Tripp (I will be once they come my way again) or a Brent Ozar deep dive.
  • Understanding Memory – From the hardware of physical memory to 32 bit vs 64 bit architecture, to Virtual Address Space and how SQL organizes and manages memory this chapter gives you what you need to help lead memory architecture and setup decisions with your SQL environments.
  • Working with Storage – Great chapter by Brent. He covers the terminology you need to know working with a SAN, the RAID levels you’ll work with and how to configure (and prove/test!) your storage setup for your performance needs. I love that this chapter includes time spent on testing your setup. Talking in theory about how to setup is great. Even suggesting you test is great. When I first started out, I had no clue how to actually do this testing. This chapter is, well, really useful.
  • The CPU And Query Processing – Again, a look from the standpoint of a resource – the CPU. But it isn’t just a walk through the CPU’s architecture and what they do (though it does start off with that). It is, instead, a discussion on the aspects of CPUs (Multicore/Hyperthreading, Processor Cache, Multiprocessing, NUMA) that affect SQL Server, and why. The chapter then finishes with a great look at query optimization and execution including many of the important concepts there (like statistics, plan compilation, etc.)
• The “How and Why” Behind Some Common Issues You Are Having with chapters like:
  • Locking and Latches – Every chapter is great in this book but this chapter is a very in depth look at locking and latching within SQL Server. Again the angle on the attack in the chapter isn’t just “learn it and know it academically” it is “here’s what is happening which might explain why you are seeing this problem”.  This chapter will help you decide on an isolation level, weigh out the pros and cons of the optimistic isolation levels in SQL Server (Snapshot Isolation and Read Committed Snapshot Isolation) and explain what is happening here. It finishes with a great treatment of latching. I learned a lot reading this chapter and I think you (and your developers!) will also.
  • SQL Server Waits and Extended Events – I wrestled where to include this. It is basic information in the sense that knowing how to interpret waits is key to many performance issues. This chapter is also very (warning) useful for actively troubleshooting your environment. Like the rest of the chapters it starts out with the basic understanding behind what a wait is and it covers some of the most common waits you encounter, what they mean and throws you some tools to further investigate these. It also introduces (and goes beyond introduction) extended events – a great feature of SQL Server 2008 for investigating and resolving issues in your environment. The author of this chapter (Jonathan Kehayias – SQLSarg on twitter for those who have difficulty spelling, as I do) gave an excellent presentation on Extended Events last year at the SQL PASS Summit. He knows his stuff here and he shows you how to unlock the potential in this feature. He also links you to a great free (a theme in this book) tool for managing your extended events through a UI.
  • Knowing TempDB – How many times you have seen your tempdb grow out of control without understanding why? How many times have you wondered whether or not to use a Table Variable or a Temp Table? This chapter first explains the organization and use of the TempDB and then it tells you about some of the problems you’ve probably encountered or heard about and how to deal with them. Again, a really useful chapter. (Can you tell that my kids watch Thomas the Tank Engine sometimes? The engines like hearing that from Mr. What’s his name
• Teach You How To Troubleshoot with chapters like:
  • Defining Your Approach To Troubleshooting – “I thought this was an advanced book!?” I have many pet peeves (sometimes I am an offender of my pet peeves, but I’m a work in progress). One of them is troubleshooting skills or a lack thereof. It is my only (to date) SQL Server Central article topic even. I’ve worked with a lot of folks who have more expertise in a skill set than I but they can’t troubleshoot their way out of a paper bag. They lack a consistent methodology and click around for their answer. This chapter discusses an approach and a way to setup future troubleshooting engagements with proper SLAs and expectations. It goes on to give practical SQL Server related advice (perf counters, an overview of tools and techniques, tips for SSAS troubleshooting, etc.)
  • Viewing Server Performance with PerfMon and the PAL Tool – Great! I blogged about using PAL awhile back. It is great to see it discussed in a book. These are free tools that you can use to identify, diagnose and fix many issues in SQL Server. This chapter is a simple recipe for starting to baseline your environment or troubleshoot an active problem. So for $40, this chapter gets you some of the basic first things I’ll do when on a site and I need to collect data to understand what is happening. It doesn’t just stop there on the collecting. It talks about some of the more common bottlenecks you run into and then goes into detail on how to extract and use perfmon data in various formats.
  • Tracing SQL Server With SQL Trace and Profiler – Again, you already have this tool. Keeping in line with the rest of the book, this will give you the architecture behind the tool and show you the implications that has on how to use it (and when and why to use it in its various flavors or implementations). It covers querying the black box trace, granting rights to run trace, using trace for auditing and tips and tricks on how to analyze your data.
  • Consolidating Data Collection with SQLDIAG and The Perfstats Script – Again, more practical information about free tools that you can use to analyze issues in your environments. These are tools designed for use by Microsoft support originally. They were designed to gather the important information and review it quickly to identify issues. Well they are available to you and this chapter shows you how to use them.

I’m getting up in the word count here. There are more chapters that are practical for us as well. Including some great chapters showing us things like using the SQL Server Managemnet Studio reports interface (with standard reports and freely available performance dashboard reports) to figure out what is going on in your environment. Not just how to use the reports but what to do with the data. Analyzing the data with another free and powerful tool called SQL Nexus. There is also a chapter on the RML utilities (Replay Markup Language) to replay a problematic (or normal) workload through your system (again.. free tools). These RML utilities can also help you test a Service Pack or run a load through an environment repeatedly after making various changes, etc.

I probably forgot a chapter or two here but I think you get the point – Great book. Recently someone asked what books you had to have for SQL Server. I personally think it is this book and one of Itzik’s books on T-SQL Querying (SQL Server 2008 version) to start.

The Bad?

I can’t really find anything bad about this book, honestly. It is a great size, a great level for learning and using. I would say if you want to get a bit deeper once you are done with this book that a book like SQL Server 2008 Internals would be a great book to also look through. It adds a lot more depth to various topics covered well in this book. I like learning and going deeper with my knowledge so I tend to be in one or both of these books. If I had to pick only one, I think I’d go with this particular one because of the immediate practical utility in many situations. It would be a tough decision, because I enjoy reading about many levels of how and why, but this book gives me a lot of that also.

p.s. the comment wasn’t really about my eyes (just in case you were wondering..) It was a nice compliment on something I try to do that Brent is much better at – Community Involvement.

Get You Some

If you don’t have it, it is a great investment: