Dynamic Management Objects are nice, but…

Don't get me wrong, I really like the catalog views and dynamic management objects. There is so much information revealed to us now; previously, only available through PSSDiag, obscure or unsupported/undocumented DBCC commands, or not at all.

But there are a couple of issues that I think should have received more attention during development. They are more annoyances than anything else, but I still believe they warrant mention.

The first is, why couldn't the dynamic management functions be distinguished from the dynamic management views by naming scheme? It would have been really nice to have sys.dmv_exec_sessions and sys.dmf_db_index_operational_stats. The primary reason? With a dmv, I can just say SELECT * FROM sys.dm_name. With a dmf, I usually have to know in advance the parameters to the function.

For example, if I do this:

SELECT * FROM sys.dm_db_index_physical_stats;

The error message isn't exactly helpful:

Msg 216, Level 16, State 1, Line 1
Parameters were not supplied for the function 'sys.dm_db_index_operational_stats'.

Why couldn't it be more helpful, like calling a stored procedure without a mandatory parameter will tell you that parameter 'my_param' was expected but not supplied?

My second beef is that sp_help and sp_helptext don't work against a large number of the dynamic management objects. For example:

EXEC sys.sp_helptext  'dm_db_index_physical_stats'; EXEC sys.sp_help	  'dm_db_index_physical_stats';

This results in:

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object 'dm_db_index_physical_stats' does not exist in database 'Org00003981' or is invalid for this operation.
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'dm_db_index_physical_stats' does not exist in database 'Org00003981' or is invalid for this operation.

Now, it clearly is not an issue that the source code for these objects is truly restricted (or like some objects, lives in the system resource database), because I can always go back to the very useful OBJECT_DEFINITION() function:

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.dm_db_index_physical_stats'));

So, why can't sp_helptext and/or sp_help work against this object? What are we trying (unsuccessfully) to hide from end users?

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, speak frequently at conferences, and write at SQLPerformance and MSSQLTips. In real life I am an architect at Wayfair.

3 Responses

  1. Razvan Socol says:

    The sp_helptext procedure works against DMF-s and DMV-s, if you put the schema prefix before the object name:

    EXEC sp_helptext 'sys.dm_db_index_operational_stats'

  2. Aaron says:

    Wow, I could have sworn I tried it that way.  Clearly I didn't.  :-\

    However, I still think that the following should work instead of giving me a misleading error message:

    EXEC sys.sp_helptext 'dm_db_index_operational_stats'

    Shouldn't it inherit the sys. prefix on the system procedure, or at least check the sys. schema for a corresponding object, much like it does for executing user vs dbo or checking the master database for sp_ objects before the local database?