SQLServerPedia

Hello from Vancouver, BC!  I’m enjoying the proceedings at the annual DevTeach conference and looking forward to some great sessions today.

I was recently given the privilege to tech review a new book put out by Manning Publications, written by Rod Colledge, called SQL Server 2008 Administration in Action.  I liked the book so much that I was thrilled to be given the chance to write the forward for the book.  Let me assure you that this admin book is unlike any other admin book for SQL Server that you might normally reach for.  Unlike other reference books, this one provides a sort of very conversational set of best practices and collected wisdom, much like you might get if you were able to yell a question over the cube wall to a much more experienced DBA/mentor.  The book itself is not free, but several of the chapters are free.  I believe the book is absolutely worth the money.  But at a minimum, grab the free chapters.

Another free SQL resource you might want to consider is the new 485-page e-book written by Jacob Sebastian called SQL Server XML Schema Collections.  It explains XSD and XML schema collections in great detail, something that has always mystified me.  Take some time to pull this one down and add it to your library. 

Cheers,

-Kev

My gauge of an amazing book is simple: if I’ve got a question, and I reach for the book BEFORE I search the web, then it’s an amazing book.

Several times in the last two weeks, I reached for this book first.

The Book’s Audience: Database Developers and Performance Tuners

SQL Server 2008 Query Performance Tuning Distilled

This book is for people who:

• Spend ten hours or more a week asking themselves, “How can I make this application run faster?”
• Have the ability to change the database schema or queries
• Know T-SQL well enough to get the data results they want (but not necessarily the fastest way to get those results)

This book is not for people who:

• Ask themselves, “How can I make this server run faster?”
• Don’t have the ability to change schema or code (for example, people managing third-party apps from vendors or consultants)

Notice that neither of these categories drew a line between developer or DBA.  I’ve worked with developers who knew how to query SQL Server to get the data, and their applications worked great - but just not as fast as they’d like.  They’re going to love this book.

What Query Performance Tuning Means

Query performance tuning is the art of reading a query’s execution plan, figuring out why it’s not fast, and then determining the most cost-effective way to make it faster.  Anybody can throw more indexes in and just hope it speeds up, but as the book illustrates, sometimes that can hurt more than help.

To do a good job, the tuner needs to know about indexing, statistics, execution plans, compilations, blocking, deadlocks, and query design issues that can force a query to perform poorly.  Some of this stuff is covered in abstract terms in college classes, but for the most part, all of us - developers, DBAs, sysadmins - are pretty much unprepared to guess what’s going on inside the SQL Server engine.

When you first design and deploy an application, that’s a great thing: you don’t need to know what’s going on inside the black box.  SQL Server handles a lot of load with the default settings, with pretty much any application design, before things start to creak and groan.  I’ve seen people build amazingly big SQL Server applications without any knowledge of how indexes or execution plans work.  True story: I walked into one shop to help with a performance problem, and the database administrator didn’t know that a table could have more than one index.  For the most part, their servers were still performing within their needs - but of course, we achieved some fantastic results with some simple performance tuning.  That DBA - and frankly, all of us - would have benefitted from a copy of this book.

Appropriate for Both Junior and Senior Levels

If you fall into this performance tuner target audience, regardless of your seniority level, you’re going to find this book’s price an extremely worthwhile investment. The book’s authors, Grant Fritchey and Sajal Dam, strike a great balance between bringing you up to speed versus diving into advanced concepts.

Chapter 4 on Index Analysis is a great example.  It spends the first few pages bringing the beginners up to speed on what an index is and how B-trees work.  Then it gradually layers on an explanation of how you would approach index design and why the width of your index matters.  The explanation includes queries that prove the concepts, with screenshots of results where appropriate.  The discussion ramps up to more advanced topics like covering indexes, filtered indexes, and compression.

When I talk about index tuning to groups of DBAs, the discussion inevitably turns to statistics - and sure enough, the book starts covering those a couple of chapters later.  I really like the organization of this book because it progresses in the same way that I’d recommend training for a performance tuner.  If you need to make an application run faster, read the book in order.  Don’t be tempted to jump to, say, execution plan analysis - you’ll make poor decisions without understanding the concepts discussed earlier.

I’ve been performance tuning applications for years, trying to wring every last dollar’s worth out of my hardware to make our applications run faster, and I keep learning things as I go through this book.  Normally, I try to read the entire book cover to cover before posting a review, but in this case, it’s going to be quite a while before I finish the book.  I just keep reading a chapter, catching enough things I didn’t know before, and then stopping to apply that knowledge and test it out in my lab.  I highly recommend it.

You can buy SQL Server 2008 Query Performance Distilled from Amazon, and there’s a Kindle version too.

Let’s start with the obvious: yes, there’s a man on the cover smoking something.  It’s probably not a coincidence that when I started writing this review, the iTunes Genius started playing Poison’s “Nothin’ But A Good Time.” There’s a reason they call it the Genius, and yes, I do have Poison in my MP3 collection, and yes, I paid for it.  (It was in my CD collection back when I listened to physical media, and it was in my tape collection before CDs came out.)

Sssssmokin!

It’s Online, But It’s Not Books Online

Rod Colledge covers a wide range of material here, but the surprising part is that it doesn’t read like a copy/paste of Books Online.  Seems like the thicker a book is, the more it feels like a copy of BOL, and I can certainly understand why - it’s tough to produce a big volume of material with a personality of its own.  Big books require multiple authors, and then sometimes editors set about stripping the personality out to make it blend together.  Nothing against Books Online - I rely on it all the time for help with syntax and minute details.  Thing is, I don’t want to read a book cover to cover when it’s full of syntax and minute details.  Right now, Manning is offering an Early Access Edition via PDF, and I hope the casual language survives the editing process.

If anything, this book is probably a little light on syntax and implementation details - for example, Instant File Initialization is covered, but not in enough detail to explain step by step exactly how to configure it in Windows.  I don’t have a problem with this approach: I like reading books to understand concepts, and I rarely sit with the book propped open next to me and type the code in off the printed page.  I’m fine with doing a quick web search to get the exact content I need from sites like BOL.

Target Audience: Production Database Administrators and Performance Tuners

I recently write a book review of SQL Server 2008 Management in Action by Ross Mistry and Hilary Cotter, and I called it a great book for production DBAs and accidental DBAs.  I would categorize this book differently: it’s more focused at production DBAs who want to dive deeper into SQL Server.  Accidental DBAs will find this book too detailed and deep for their needs.

For example, in Chapter 14, Monitoring and Automation, Rod talks about deadlocks, including how to create them, how to monitor for them, and how to create a a SQL Server Profiler trace to catch blocked processes.  He also shows how to use the RML utilities to clean up your Profiler traces and get better insight out of them.  These types of topics are probably outside of what an accidental DBA would want to accomplish, but it’s exactly the kind of thing that a full-time production DBA has to get involved with sooner or later.

Both of these books, however, take the same approach of focusing on administration, not development.  If you want to learn how to write T-SQL, how to use functions, or how to design a schema, this is not the book for you - look for a SQL Server development book instead.  If you spend your day managing more than 25 instances of SQL Server, this is a good book for you.

Areas for Improvement

I’d quibble about the book’s organization.  For example, Chapter 2 is titled Storage System Sizing, but it encompasses a lot of aspects of storage from RAID levels, direct attached storage, SANs, solid state drives, etc.  Chapter 3 is called Physical Server Design, but it continues with storage topics like disk configurations and RAID array stripe sizes.  My advice: ignore the table of contents, and just dig through the entire book.  It’s worth it.

Appendix A lists the Top 25 DBA Worst Practices, including things like “Using RAID 5 volumes for write intensive applications.”  They’re great advice, but I might include a pointer to the section of the book that explains why it’s a worst practice.  It’d help the DBA drill down to learn more, and there’s certainly enough information in the book to back up what Rod suggests.  It’s not an issue of just tossing out suggestions without backing them up - if you read this book cover to cover, you’ll understand the reasoning behind each suggestion.

Overall: Good Resource for Curious Production DBAs

I’d describe the perfect buyer as someone who’s been working with SQL Server for a year or two, and who’s facing a lot of challenges on the job around configurations they haven’t seen before.  They might be tasked with building their first disaster recovery plan, not sure whether to choose log shipping or database mirroring, and they want to know what types of production issues they’ll face with each of those options.  They can read this book’s High Availability section and feel like they’re having a conversation with a friend who’s been there and done that.

I’d also recommend this book to someone preparing to get their MCITP certifications in SQL Server.  If you devour the material in this book, then you’re going to have the kind of skills and knowledge that it takes to get certified.  If you find this book too intimidating or too technically detailed, then you’re probably not going to like the certification process either - and the cost of this book beats the cost of most full-blown MCITP training materials.

Manning is running a sale for SQLServerPedia readers until March 15th.  I’ve got the 35% off coupon code on the home page of SQLServerPedia.