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.
.ab_code
{
padding:10px;
background:#ccc;
font-family:lucida console,courier new
}
.ab_error
{
font-family:courier new; color:red; font-size:12px;
}
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?