Have you given thought as to how you might be able to glean more information out of your old compatibility views, such as dbo.sysprocesses? Or maybe you’ve needed a lightweight way to do some troubleshooting on an ailing system? Or perhaps you’re begging to figure out what the hell all these acronyms mean – DMO, DMV, DMF…

Enter Performance Tuning with SQL Server Dynamic Management Views by Louis Davidson ( Blog | @drsql ) and Tim Ford ( Blog | @SQLAgentMan ). Davidson’s and Ford’s book is aimed squarely at the DBA that already has a few years of experience, especially if she’s a little set in her ways. One vocal point that this book makes over and over is that Dynamic Management Objects (DMOs) are a new way to get at the metadata that is stored about your databases and the servers on which they reside. I think I may have counted at least a dozen instances where Davidson and Ford make that argument. And while the book is aimed at mid-to-senior level DBAs, it can be used as an introductory guide to DMOs as well. I don’t think that anyone who is starting out with SQL Server would have any trouble following the book’s examples.

Making the Argument

Davidson and Ford show how one would use DMOs instead of relying on compatibility views, such as dbo.sysprocesses. In its place, you might use any combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests. While at the offset it may seem quicker to query a single view, Davidson and Ford make the argument that using the new DMOs allow for greater flexibility in querying for system data. In a similar fashion, they show how DMOs have surplanted sp_who, sp_who2, and the Activity Monitor, which has been known for its buggy-ness.

Personally, I utilize DMOs at the beginning of troubleshooting. The systems I administer are slammed for resources, and so I can’t simply run traces because it will cause immediate and noticeable performance degradation. Using the examples from Davidson and Ford, the performance impact using DMOs is no worse than normal ad-hoc querying, which my system can handle.

My favorite parts of the book are the references to other sources of reading. These references are liberally sprinkled throughout the text. For example, when reviewing wait statistics Davidson and Ford cite a link to a SQLCAT whitepaper on SQL Server 2005 Waits and Queues. Roughly half my bookmarks are reminders to “go read this article.” I love these additional knowledge references and I find them valuable as I continue to grow my experience.

It’s a Little Rough

My copy of the book is a first edition and it’s a little rough around the edges, as first editions tend to be. In two examples, the script and the corresponding output weren’t matched. Sometimes the outputs were truncated for space, which is understandable, but these simply did not go together.

As for the scripted examples, I could tell that they were being authored by two different people. Experience shows that each DBA and developer has her own individual style so it’s to be expected, but I feel that it’s a minor issue of consistency for any technical work. Sometimes referring to an column alias as [message] in one script and then as [object] in the very next can cause a double-take when reading through the book.

Recommendations

I do whole heartedly recommend the book, despite it’s first edition roughness. Like it or not, DMOs seem to be the way of the future – Microsoft gave us 89 in SQL Server 2005 and 136 in SQL Server 2008: how many more are waiting in Denali? Despite the rough patches, Davidson’s and Ford’s book lays down the framework for utilizing these metadata objects from basic troubleshooting to index maintenance. What’s more, all the scripts that are printed in the book are available for download as well, meaning you’ll spend a lot less time typing them up and more time playing around with them on your servers. If you place a high value on your time, that reason alone may be worth the purchase of the book.

I’d recommend that you purchase the book. DMOs in and of themselves aren’t the most difficult concept in SQL Server, but learning to harness and interpret that information requires an experienced guide.