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.

 

Banner

 

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?

 

Banner


Query Store For SQL Server 2019

Author: Tracy Boggiano and Grant Fritchey
Publisher: Apress
Pages:219
ISBN: 978-1484250037
Print: 1484250036
Kindle: B07YNL3X4X
Audience: Users and developers of SQL Server queries
Rating: 5
Reviewer: Kay Ewbank

This book looks at the new Query Store feature in SQL Server and how you can use it to iden [ ... ]



Teach Yourself Java in 21 Days, 8th Ed (Sams)

Author:  Rogers Cadenhead
Publisher: Sams
Pages: 672
ISBN:  978-0672337956
Print: 0672337959
Kindle:B082J486LS
Audience: Beginning Java Programmers
Rating: 4
Reviewer: Mike James
Java! Its still more than relevant and at edition 8 this must be a classic?


More Reviews

Last Updated ( Tuesday, 13 September 2016 )