Connect Digest : 2011-05-02
This week, after seeing a lot of the DMV enhancements made in SQL Server 2008 R2 SP1, I thought I would take a look at some dynamic management view/function requests.
Page split personality
Michael Zilberstein blogged on Monday about how Extended Events and the transaction log differ in the way they report page split information, and that it would be useful to have accurate information about these events available in the DMVs. So he filed this Connect item:
#664900 : Please add page splits data per index, index level and page split type
I asked for a very similar item back in 2008, but thought that it belonged in sys.dm_db_index_physical_stats:
#388403 : Include page split information in sys.dm_db_index_physical_stats
They've already provided additional info via Extended Events (see #388482 : SQL Server Extended Events Page/Split Event Additions) so, hopefully, they will take our DMV requests seriously as well.
How stale is my database?
Kimberly Tripp (blog | twitter) posted this item, asking for a column in sys.databases to reflect last accessed time (I think it belongs in an existing or new DMV, not in the catalog view). An additional comment on the item suggested a terrific enhancement to this potential feature, where each individual login would be represented with their last access time, making it easy to differentiate when a database is only being accessed by system or background processes.
They've recently rejected a similar item requesting last accessed time for objects within a database (see #454714 : I should be able to see the last time an object was accessed in sysobjects), though that one had zero votes, so I'm hopeful that Kimberly's request may be deemed both easier to implement and more important to the community.
I'm an E7-8800, what are you?
Glenn Alan Berry (blog | twitter) asked for an additional column on the sys.dm_os_sys_info DMV that reflects the CPU's description (e.g. Xeon X5505). As you may have noticed on his blog, Glenn spends a lot of effort on analyzing performance of workloads on different CPUs, and there are some pretty substantial differences between CPUs that may look quite similar to the average person – especially when seeing vastly different performance on two servers that are otherwise very similar (same amount of RAM, same SAN, etc).
#657435 : Add a processor_description column to sys.dm_os_sys_info in SQL Server Denali
An additional comment requested that the true observed speed would be shown, as opposed (or in addition) to the manufacturer's stated clock speed – the true speed is a number than can vary greatly if you are using power saving options.
You're a word, but are you reserved?
This almost certainly wouldn't be in a DMV, but I still like the idea and it almost fits here. Greg Low (blog | twitter) is requesting a system view of some kind that exposes a list of reserved words – my comment here was that relying on IntelliSense alone is a fool's errand.
On a somewhat-but-not-really-related note, Meredith Ryan-Smith (blog | twitter) recently blogged about a workaround she used to export all of the object names from sys.all_objects into a custom dictionary for use in Word, PowerPoint, etc. to avoid the ugly red squiggly lines pointing out that you misspelled sp_server_diagnostics and sp_who2. I think that if the metadata Greg is asking for were made available, then this idea would be easy to extend to reserved words, so that Word also wouldn't try to convince you that perfectly valid "words" like COLUMNSTORE and DATA_COMPRESSION are spelled wrong.
Do you really like DBCC?
Greg Low also requested that the information returned by running DBCC SHOW_STATISTICS be made available through a DMV (another user suggested adding a column like is_stale to sys.stats, but I think a separate DMV makes more sense).
#611155 : DBCC SHOW_STATISTICS info should be available as a DMV
Exposing this information in a DMV would also, presumably, make it easier to access information like STATS_DATE without having to be in the context of that database (see #654972 : STATS_DATE only works as documented when current db is where the tables are).
