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?
I would want a sql_handle with the missing index dmv’s. I posted a connect about it. It would also be nice if they exposed some like sys.dm_os_physical_io_stats that told you how many and what size seq and random reads and writes you are doing.
I guess backwords compatibility for sql 2000 for the poor and short staffed agencies is probably not applicable 🙂
Column names in the DMVs -are- consistent. They’re just not consistent with what -was- in the system tables, which are now deprecated. Therefore, the old names are deprecated. No problem.
Also, you don’t need to link out to a deprecated system table (sysdatabases) for a database name. Try sys.databases. Or better yet, the DB_NAME function.
Also, sys.dm_exec_requests has the sql_handle column.
I think you just need to explore a bit more… here’s how I do it:
SELECT OBJECT_NAME(object_id)
FROM sys.all_columns
WHERE name = ‘sql_handle’
And one more thing. The whole system catalog views (think sys.indexes) versus system compatibility views (think sys.sysindexes). I understand *why* they have both, but then I do stupid things like refer to one when I mean the other in blog posts, conversations, and forum posts. Grrrrr.
DB designers design tables so that they don’t store data more than once. We do this out of a fear that the database will store redundant data and thus face the risk of being abnormal, I mean denormalized.
But the views don’t face any such restriction. For the views, it’s their raison d’etre. So yes! stick in those sql_handles. Throw in any columns that could be useful.
My guess is that the dm view designers left out columns out of a (possibly subconscious) fear of redundancy.
I think the columns were left out to keep things as simple as possible–but you just can’t satisfy everyone, and as it turns out everyone has their own definition of simplicity. For me the current design is great, because if I look at one of those views in Object Explorer I see a handful of columns that are very well targeted to the domain covered by each of the views. Yes, they could be extended to reduce the number of joins that users need to write, but the result would be an explosion with regard to the number of columns on each view, thereby making it more difficult to find what you’re looking for. I think either side could be argued, but in my way of thinking I would rather write more joins against simpler structures than have to deal with mega-views. Have you ever tried to write complex queries against a “flat,” hugely-denormalized reporting table? Turns out, joins are actually easier in many cases.
My vote is sql_handle and object names. Give me database name, give me index name, give me freakin’ table name. It’s not that I can’t go look it up (trust me, I do, *every single time*), but it’s a VIEW so I shouldn’t *have* to look it up.
All things considered though, I’m just happy to have the DMV’s in the first place. It’s definitely nicer than 2000. 🙂