Microsoft has made the barrier-to-entry for using the Dynamic Management views (DMVs) so needlessly high that any DBA needs to judge whether it is worth the effort to learn them.

Some of the interfaces have a wild, unfinished feel to them. The sys.dm_tran_active_transactions DMV, for example, returns information regarding current transactions on a given instance, but it is not untypical in that many of the columns that it returns contain attributes that are either unsupported or of no interest to the end user. Why not hide these undocumented or unsupported columns in the implementation, rather than spoil a very useful DMV with valueless information?

This is not an aberration amongst DMVs. The counter values returned by sys.dm_os_performance_counters DMV are sparsely documented and need to be decoded before we can use them. The resulting scripts perform such gymnastics to get the data out in a useable form that one cringes. The central proposition – query a view, return SQL Server performance counter values into a table, interrogate them using SQL – is attractive, but it's almost as if Microsoft is willing DBAs not to go there.

I’ve just spent most of the last two months working with Louis Davidson and Tim Ford on their book, Performance Tuning with SQL Server DMVs, and my conclusion is that the DMVs really are worth the extra effort. Finally, with the indexing DMVs, we have a way to devise an index maintenance strategy that extends beyond "if in doubt, rebuild". With the I/O and operating system related DMVs, we can return incredibly valuable data regarding CPU, I/O and memory pressure points, on a given instance.

Most notable of all, in my opinion, are DMVs that expose wait statistics (e.g. sys.dm_os_wait_stats), detailing where, why and how long users' sessions have been forced to wait for a resource to become free, before proceeding. Such views not only expose useful data but also lead the way towards a very effective methodology for tracking down performance issues, based simply on where SQL Server is spending the most time waiting. If you haven't done so already, I urge you to read Tom Davidson's excellent white paper on use of waits and queues as the basis for a performance tuning methodology. Now, if only SQL Server would expose these wait stats session-by-session, rather than aggregated across all sessions, since SQL Server last restarted, then we'd really be getting somewhere!

I'd love to hear about your battles with the DMVs, if and how you use them, and how you'd like to see them improved.

Cheers,

Tony.