SQL Server DMVs in Action
SQL Server DMVs in Action

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?



Data Structures and Algorithms with JavaScript

Author:  Michael McMillan
Publisher: O'Reilly
Date: March 24, 2014
Pages: 246
ISBN: 978-1449364939
Print: 1449364934
Kindle: B00IV3J23Y
Audience: Intermediate JavaScript programmers
Rating: 3.5
Reviewer: Ian Elliot

A book explaining the standard data structures in the worlds most popul [ ... ]

Rise of the Robots: Technology and the Threat of a Jobless Future

Author: Martin Ford
Publisher: Basic Books
Pages: 352
ISBN: 978-0465059997
Print: 0465097537
Kindle: B00PWX7RPG

Audience: Everyone
Rating: 4.5
Reviewer: Ian Stirk

This New York Times bestseller discusses the impact of automation on jobs and the economy in the near future. What me [ ... ]

More Reviews

Last Updated ( Tuesday, 13 September 2016 )

RSS feed of book reviews only
I Programmer Book Reviews
RSS feed of all content
I Programmer Book Reviews
Copyright © 2018 i-programmer.info. All Rights Reserved.
Joomla! is Free Software released under the GNU/GPL License.