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

The Book (And the balding blogger)

Yeah its signed, but I use it a lot anyway, looks well worn, no?

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 KehayiasSQLSarg 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:

Share

Tags: , , , ,

18 Comments

Leave a comment
  1. Christian Bolton October 12, 2010 at 06:16 #

    Thanks for the very detailed review Mike! I’m chuffed to bits that you like the book and very grateful that you’ve spent the time on such a thoughtful review.

    As most authors will tell you, there’s no money in writing technical books so to get great feedback like this (especially from a well-respected MVP) helps to drive momentum for the next version of the book.

    We’ve already got some great ideas brewing for a SQL11 version!

    Cheers,

    Christian

    • Mike Walsh October 12, 2010 at 07:07 #

      Thanks for the great comment, Christian. Hope the review is still acceptable even though I am not an MVP. :-)
      It is a great book and I really enjoy picking it up.

  2. Janice Lee October 12, 2010 at 08:17 #

    Wow, what a well-written review. Mike, I too have been reading this book. I agree…it is an impressive read. It’s a solid fundamentals book–and definitely delivers what its title claims it would deliver.

    • Mike Walsh October 13, 2010 at 10:22 #

      Thanks, Janice! Glad you agree.

  3. James Rowland-Jones October 13, 2010 at 08:46 #

    I can only echo Christian’s sentiments – this is an awesome write up and I am delighted that you have enjoyed the book.

    Thanks very much – James

    • Mike Walsh October 13, 2010 at 10:21 #

      No problem guys. You all wrote a fantastic resource. I can’t wait to see what you do for SQL11.

  4. KenJ November 3, 2010 at 22:14 #

    As Mr. Conductor would say, this review was “really useful” :)

    I’ve had the book for a few weeks, but now it’s going straight to the top of the pile. Thanks!

Trackbacks/Pingbacks

  1. Flashcache - October 12, 2010

    RT @billramo: RTM @mike_walsh: Updated the post about my Review of Pro. SQL 2008 Internals/Troubleshooting – http://bit.ly/duffou I 2nd …

  2. sarvesh singh - October 12, 2010

    RT @TopsyRT: Prof. SQL Server 2008 Internals & Troubleshooting # http://bit.ly/b6DoGF

  3. Ted Krueger - October 12, 2010

    RT @mike_walsh: Finally! Blogged a Review of Pro. SQL 2008 Internals/Troubleshooting – http://bit.ly/duffou

  4. mike - October 12, 2010

    RT @mike_walsh: Finally! Blogged a Review of Pro. SQL 2008 Internals/Troubleshooting – http://bit.ly/duffou

  5. Brent Ozar - October 13, 2010

    Reading @mike_walsh's review of our book: http://bit.ly/ah42wk

  6. Kevin Boles - October 13, 2010

    RT @BrentO: Reading @mike_walsh's review of our book: http://bit.ly/ah42wk <– it IS a VERY good book!

  7. I’m Mike Walsh and I’m Asking For Your Vote | StraightPath Consulting's SQL Server Blog - November 4, 2010

    [...] and the Professional Development Category. The posts from me for consideration are –  Book Review of Professional SQL Server 2008 Internals and Troubleshooting (a great book ), a Professional Development Post reminding people to share knowledge and help [...]

  8. The Twelve Days of SQL Series | Brent Ozar - Too Much Information - December 8, 2010

    [...] Mike Walsh on the best book ever written – OK, not really – it’s actually a book review of our book, but forget about that for a minute.  Look at the effort he put into writing the book review – examining chapter by chapter, explaining what you’ll learn.  Now THAT is a useful book review.  I aspire to write reviews that complete. [...]

  9. christianbolton - March 31, 2011

    RT @mike_walsh: I forget, the most useful SQL book on the market is also on the list right? http://bit.ly/duffou :-) <–damn right it is :D

  10. We're Here to Help - Straight Path Solutions, a SQL Server Consultancy | Straight Path Solutions, a SQL Server Consultancy - March 26, 2013

    [...] here was to be, well, helpful. He wanted to be useful and helpful (actually the reason I highly recommended the Professional SQL Server 2008 Internals and Troubleshooting book on this blog, and why I was honored to tech edit the 2012 version and get my name on the first [...]

Leave a Reply