THE SQL Server Blog Spot on the Web

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

Kalen Delaney

Did You Know? The Best SQL Server 2005 Feature

This post really should have a topic of "Pet Peeve" but I really didn't want to sound quite so negative.

It seems like almost every time I see a clever solution in a newsgroup response, or in a blog post, about how to extract some very useful troubleshooting information using the Dynamic Management Views, someone will ask for an equivalent query in SQL Server 2000.  It just happened again as I was doing a google search and came across a post by Kevin Kline right here on sqlblog about using the new metadata to get information about space usage in tempdb. It was actually referring to a query developed by Aaron Bertrand, who also posts here. Someone asked if there was a way to do the same thing in SQL 2005, but we assume they meant SQL 2000. Our own Adam Machanic responded that the CROSS APPLY operator was not available in SQL 2000, but that is the least of our worries. The query refers to seven different Dynamic Management Objects (6 views and a function), most of which have no equivalent in SQL Server 2000.

It just seems that some people seem to think that anything we can do in one version should be doable in a previous version. If that were true, why would you upgrade? Do you only upgrade for performance benefits? I don't think so.

In my opinion, the BEST SQL Server 2005 feature is the new system metadata. It is absolutely worth the price of admission. There are so many things that you couldn't even begin to find out about with the metadata from SQL 2000, and the only way to get that level of internal detail is to UPGRADE!

If you're running SQL 2005, the Dynamic Management Objects can provide hours and hours of fun and exciting explorations!


Published Tuesday, December 4, 2007 10:44 PM by Kalen Delaney
Filed under: ,

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



Denis Gobo said:

I agree with you Kalen, the DMVs do rock!

December 5, 2007 4:22 AM

dmarkle said:

For me, the best feature has to be the snapshot and read committed with row versioning isolation modes.  In my experience, it seems that they're the most overlooked feature of the entire system, too.  Maybe it's because not enough people really understand transaction isolation levels well enough to begin with, and therefore it doesn't get much press...

December 5, 2007 6:45 AM

a.m. said:

For me the best feature is CROSS APPLY :-)

December 5, 2007 7:26 AM

Linchi Shea said:

I always have trouble answering this type of questions--best features, favorite movies, etc. I like the general trend of the SQL Server database engine getting more scalable, easier to use and manage, more robust, and more available with every new release. But I hope that Microsoft would keep up the good work since there is still much more to be done.

December 5, 2007 3:34 PM

Kalman Toth said:

The trouble with DMV-s is the lack of human-engineered UI.

Long, hard to memorize names.....even complicated joins to get

something out of them....

You may not know the exact syntax but DBCC dbreindex...  but it is easy to

remember the start of at least. So is DBCC checkdb.

DBCC the way is database consistency checker (Sybase). It was

extended to cover other maintenance commands.

December 7, 2007 10:48 AM

Denis Gobo said:


Have you looked at Script Repository: SQL Server 2005 on Technet

There is a bunch of good stuff which is already written

December 7, 2007 10:54 AM

rubik said:


SQL Server 2005 Books Online  

SQL Distributed Management Objects (SQL-DMO)  

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

December 24, 2007 9:42 PM

habia said:

so confused:

Dynamic Management Objects

Distributed Management Objects

December 24, 2007 9:49 PM

Kalen Delaney said:

Hi Habia

Dynamic Management Objects are a new set of metadata objects in SQL 2005. Mostly views, but a few functions. The names all start with sys.dm_ and they can be accessed like other objects within your TSQL code.

Distributed Management Objects are a programming interface, as an alternative to TSQL code.

December 24, 2007 11:48 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement