Author: Ian W Stirk
Publisher: Manning, 2011
Pages: 375
ISBN: 978-1935182733
Aimed at: DBAs and developers
Rating: 5
Pros:Well written, packed with useful code samples
Cons: No coverage of Performance Dashboard
Reviewed by: Kay Ewbank

Kay Ewbank described this book as "a fun read" and "packed full of really useful code samples" so it merits inclusion as one of our best books of 2011.

It’s always fun to read a book written by someone who’s a true enthusiast for a subject, and this book is (unlikely though it might sound from the title) a fun read. If you write applications that make use of SQL Server, you ought to know about DMVs. Dynamic Management Views are one of those useful little hidden features of SQL Server that can make a real difference because they tell you what’s going on behind the scenes in your queries. Get to grips with them and you can take a query that’s crawling and turn it into one that flies. You can also monitor what’s running on your database, and diagnose query problems. The subtitle to this book is ‘Better Queries with Data Management Views’, and that’s a good summary of what you get from this book.

The book is packed full of really useful code samples - over 100, which given the whole book is just over 300 pages long gives you a fair idea of just how packed full I mean.

The first part of the book is titled “the DMV gold mine” and covers what DMVs are, what problems they can solve, some examples, and getting started with DMVs. The examples should be enough to convince you this is good stuff - finding your slowest queries, missing indexes, which SQL statements are running now, and quickly finding a cached plan.

Having got you hooked, Stirk then goes through some of the code that will prove useful when you’re creating DMVs. Essentially, this section covers 16 things you will need to do in your DMVs such as restricting output to a particular database, using the TOP command, and building dynamic SQL.

The main section of the book takes you through each way DMVs can help - problems with indexes, improving poor query performance, operating system DMVs, common language runtime (CLR) DMVs, resolving transaction issues, and database level DMVs.

In many ways the chapters on problems with indexes and improving poor query performance are the heart of the book. The index chapter looks at how you can identify missing, unused, high maintenance, fragmented, and most frequently used indexes. Query performance looks at aspects such as long-running queries, blocked queries, CPU intensive queries, I/O hungry queries. You’re also shown how to find queries with missing statistics, those that carry out table scans, those that have been run during a particular interval, currently running queries, and even those that are running more slowly than normal.

The chapter on operating system DMVs looks at server waits, what causes them, and more importantly what you can do about them. The chapter on CLR DMVs starts off with a quick explanation of how CLR has been incorporated into SQL Server. He then goes on to show how to create a simple CLR class and using the SQL CLR regular expression functions before looking at time-consuming CLR queries and how to handle them.

The final two chapters look at the self-healing database and useful scripts. The idea of the self healing database is that SQL Server can correct its own problems before they become really noticeable. Of course, for it to do this you need to create SQL Server agent jobs that run on a schedule, and this chapter looks at automatically recompiling slow routines, carrying out index maintenance, automatically disabling or dropping indexes and adding missing ones. Personally, I find the idea of SQL Server doing anything on its own deeply disturbing, but those of you of an optimistic disposition may want to try the ideas. The chapter on useful scripts is much less disturbing, and there are 12 scripts of varying degrees of usefulness, including ones to show where your queries really spend their time, estimating when system jobs will finish, and finding who’s doing what and when.

Having finished the book, the one thing I’d like to have seen included (and this is more as a DBA administrator than a developer) is how DMVs can be used with SQL Server’s Performance Dashboard. Many of the scripts here could be used as custom reports for the dashboard, and a quick chapter showing how to do this would have been a nice to have. However, that is just being picky, to be honest. Let’s face it, if just one of the code samples in this book helps you crack a query performance problem, the book will have earned its keep. It’s well written, short on waffle and long on useful info. What more could you ask?