DMV Wish List
Just today, Tony Davis editorialized at Simple-Talk.com on the topic of SQL Server Dynamic Management Views (and Functions) and the complexity that lies within. His opinion, and those of many of others that commented to date are that the DMVs/DMFs, while valuable, are overly-complex, incomplete, and inconsistent in their current state. I for one, am one of those in agreement and voiced my opinion there.
That started me wonderinig, what would you change about the DMVs (and DMFs) man how loathe the need to keep appending that little clause of four letters! if you had an opportunity to do so. Personally, I’d make sure that column naming conventions were consistent: database_id in the DMVs, yet dbid in the system catalog views? Bah! Secondly, and more importantly I’d descend partly down the slippery slope of over-inclusion and place certain (obviously used) columns in the DMVs to avoid needing to link out to the system catalog views for such columns as database name (from sys.sysdatabases.name) and sql_handle (from sys.sysprocesses.sql_handle.) None of the sys.dm_tran… DMVs include the sql_handle. You need to first link to sys.dm_tran_session_transactions in order to obtain a session_id, then join to sys.sysprocesses.spid to return the sql_handle just to determine the SQL associated with a transaction (which is a core reason for mining info from the transaction DMVs to begin with!) That last example also highlighted the consistency issue (session_id in sys.dm_tran_session_transactions, spid in sys.sysprocesses). A pox on your house, Microsoft!
So, let’s hear it, what would you do to improve the DMVs?