THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Blogging the DMV's

Well, I am working on a project that is going to be a book on dynamic management views (and functions, but DMF is an interesting acronym, and objects, as in DMO, has a well known other meaning.  What I will post will be one object in the following format:

Name of object

Type: Function or View

Parameters: If it is a function

Scope: Really denotes how long the data's life is for.  Many of the dynamic management functions are used to record some event, like an index being use for a query.  There is not usually (well, I haven't finished cataloging them, but so far there is never) any way to reset the values without rebooting the server.  There are tools to use to mitigate the issue, (like Tom Davidson's DMVStats tool that Kalen mentions here,) but the first time you look at the data in these objects it will be a bit confusing if you don't know how long the data has been gathered.

Columns: A listing for all of the columns that are output by the DMV, including a description

Example Usage: A query or two regarding possible uses

Then finally, there will be a section of the book that has a list of ways you can use the DMV's to diagnose common things about your server.

The book will be very reasonably priced (or not priced) and you will hear more about that over the next few weeks.  What I am asking of you (and you over there to the left) is for your comments/likes, dislikes, and especially query ideas.  I will include your query in the book (or coalesce it with others) and everyone who comments constructively will be mentioned in the acknowledgements of the book.  I will also include any links you might have to DMV related queries in the Bibliography (which already has 16 items in it from just getting started.)

I will be posting these to my http://sqlblog.com/blogs/louis_davidson/default.aspx site only, since comments there can be entered without a passport.  I will make mention of it on my http://drsql.spaces.live.com blog and provide more loose commentary about the writing process. 

Published Tuesday, July 10, 2007 12:14 AM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Denis Gobo said:

Louis,

That is a great idea, the DMVs are so powerful and a lot of people still don't use them but are messing around with sp_who2 and DBCC USEROPTIONS

I did 2 of these DMVs

One is sys.dm_exec_sessions:

http://sqlservercode.blogspot.com/2006/08/sysdmexecsessions.html

The other one is sys.dm_db_index_usage_stats

http://sqlservercode.blogspot.com/2006/09/sysdmdbindexusagestats.html

I know you linked to the sys.dm_db_index_usage_stats  post before

Feel free to use whatever you need

Denis

July 10, 2007 4:51 AM
 

Regan Galbraith said:

Looking forward to seeing the results of this.

Some of the DMV's do seem to be re-settable without resorting to server restarts. For example, the REPORT Object Execution Statistics (which I believe drives off of DMV's), appears to get re-set after a DBCC FreeProcCache

July 10, 2007 8:03 AM
 

SSQA.net said:

Documentation on DMVs that are re-settable with that DBCC FREEPROCCACHE would help a lot to manage or get optimum test results.

July 13, 2007 9:24 AM
 

drsql said:

Regan/SSQA.net:  This will definitely be a part.  I will list this case (and any others I discover :) in the documentation for the DMV.

July 27, 2007 9:40 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement