Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DMVs: are they worth the effort?

By Tony Davis,

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.

Total article views: 418 | Views in the last 30 days: 7
 
Related Articles
FORUM

Issues with performance counter

Performance counters for SQL Server are missing

BLOG

Performance Monitor Counters for SQL Server performance testing

I posted  “Window Perfmon scripting, SQL Server perfmon and how to perfmon” which refers to a perfor...

FORUM

How to reset performance counter SQL Server:Databases - Log growths

How to reset performance counter SQL Server:Databases - Log growths

BLOG

Reinstall SQL Server Performance Counters

I’ve had several instances over the last few months where I had to reinstall the SQL Server Performa...

BLOG

Querying Performance Counters in SQL Server

In a previous post, there was a comparison between sysperfinfo and sys.dm_os_performance_counters wh...

Tags
database weekly    
dmv    
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones