THE SQL Server Blog Spot on the Web

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

Louis Davidson

DMV Progress and Upcoming Events (PASS/Devlink)

If you have noticed my series of posts on the DMV's on, you may have wondered why these posts are coming so slowly.  I mean, if I am actually going to get a book of all of them, with examples, done in a couple of months, I certainly had better be further along than I seem to be.

Thankfully, I am farther along than this.  The problem is that I just don't have any others done to the level that I feel merits being posted.  On the bright side, the DMV's are really quite amazing for their depth.  On the down side, the DMV's are really quite amazing for their depth.  There are many many many columns returned, and some of them are still not really clear enough for me to post.  So I am going back and forth over and over trying to get all of the columns understood enough to write about them. 

The PASS Community Summit is coming up quick.  I will be there working on the opening day(s) for some training and the Quizbowl (this year is hopefully Jeopardy for nerds.  Email me at if you want a chance to possibly be a contestant.)   I will be at the conference for the rest of the week learning and chilling out. 


Finally, on October 13, I will be speaking at the devLink technical conference in Nashville, TN.  It is pretty cool really, and for 50 bucks, a two day conference is a pretty dang good deal (and please no flame comment wars about these two different conferences and money values.  I will delete any comments like that :)  There are lots of good speakers who will be there, and it is a bit of an honor to speak. I will be giving two presentations, sadly back to back, on Saturday (also kind of sad, as Elvis Costello and Bob Dylan are in Columbus OH, that night), one about the reasons to normalize, and the other about performance tuning.  It will be largely 80% a discussion about the dynamic management objects.  I plan to do a great amount of example writing for the book in the weeks before the conference.


NOTE:  If you read my blog, books, etc, please feel free to accost me and tell me what you like or don't like about anything I produce.  I really really really want feedback.  I am about to start the 2008 edition of my design book, and I want to know what would make you purchase it, either for the first time, or again if you have any of the previous editions.  I am not sure that I would purchase each edition myself, so it is one of those things I obsess about.  The pay for writing is terrible (horrible, awful, terrible, to be exact) and the reason I do it is for my reference (I search the PDF of my book 2-5 times a month, and my websites 10-30 times a month) and to evangelize good design in my own, very light, sort of style.  If you want to schedule lunch to talk, drop me a line and we can get some eats and talk about the book. 

Crossposted to

Published Sunday, August 26, 2007 4:52 PM 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



Simon Worth said:

I find it interesting that in BOL, and on this site, DMV's are referred to as views, and DMF's are referred to as Functions, yet, when you look at the execution plan for the DMV's, their is a clear call to a table valued function.  I didn't actually look at them all, just a few to see if the results would vary.

I assume this is simply an abstraction layer for the user to think of these as views rather than a function - or is there more going on under the covers, and SQL Server is in fact getting confused?

If you look at the execution plan for dm_db_file_space_usage it says Table Valued Function (DM_DB_FILE_SPACE_USAGE).  Yet if you look in sysobjects it's listed as a View (xptype = V), and if you look in Management studio, the view is listed under System Views with no mention of something like named in functions at all.

If you look at the execution plan for sys.dm_db_partition_stats - you see a table valued function for PARTITIONCOUNTS.

This would lead me to believe that dynamically under the covers this function is being generated - however, I wasn't able to find anything in Profiler to confirm this.

August 27, 2007 2:21 PM

drsql said:

I think the key here is simple.  Microsoft gets to cheat.  They always have, and they always will.  It is their system, and they should be able to provide views of their meta data in the fastest way possible.

My only real quabble with the way they do it is: PLEASE HIDE THE DETAILS!  If an error said: "unable to view the code for system objects in the sys schema" it wouldn't hurt so much.  But instead, you get this cool looking code snippet that DOESN'T RUN!

Oh well, I guess I would rather have it this way than no dynamic management views/functions/cheating objects :)

Thanks for the comment!

August 28, 2007 9:07 PM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement