December 16, 2008 | SQL Server

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:

http://web.archive.org/web/*/https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=388218

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):

http://web.archive.org/web/*/https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=388416

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:

http://web.archive.org/web/*/https://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=388403

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.