Pushing for more disclosure in DMVs
I have been involved in several discussions over the past week involving getting more diagnostic information out of the DMVs in SQL Server 2008 and beyond.
Yesterday, I asked for procedure recompiles to be tracked in the new sys.dm_exec_procedure_stats DMV:
Today, Erland followed up with a similar request, but at the statement level (so the stats would be tracked in sys.dm_exec_query_stats):
I also pushed an item today asking for page split information to be tracked in sys.dm_db_index_physical_stats. There is probably not enough meat there for everyone to be satisfied, but it is a start:
There is a reluctance to add more data to the DMVs. So I urge you, if any of this information is important to you, go and vote, or otherwise make your voice heard. If you think there are better ways to derive this data, say so!
XEvents is a new way to collect trace-like data, without the overhead of SQL Trace, and without the overhead of generically collecting everything under the sun in the DMVs. The problem many have with this approach is that you still have to know what to look for, and set up XEvents to capture it, before it happens.
You can read more on XEvents on MSDN:
http://msdn.microsoft.com/en-us/library/bb630354.aspx
And in Paul Randal's TechNet article:
http://technet.microsoft.com/en-us/magazine/dd314391.aspx
More specifically, Eladio Rincón talks about how to use XEvents to determine which queries are causing page splits.
