I’d prefer more control on when I reset DMV data.

Recently I was working on an indexing presentation and looking for missing, duplicate, and unused indexes. As I set up demos to show the effects of indexing, I was constantly adding and deleting objects, and even resetting the DMVs to show statistics by starting the instance. The documentation for some DMVs, like sys.dm_db_index_usage_stats include a note that the counters are initialized to zero when the instance is restarted. If a database is taken offline, or detached, all rows referencing the database are removed.

That got me thinking. Why is this data removed? I’m sure some of the data is stored in memory and automatically reset, but is this the best way to handle this data? Wouldn’t it be better to persist this data and allow the DBA to reset values when they were ready? I know we can store this data in a table periodically now, but I think keeping this inside the system views would make sense as an option in SQL Server. When we shut down a database, persist this data inside the database. That would be closer to my vision of self-describing databases, which contain all their own metadata.

I know there might be performance impacts to persisting this information, but I can also see benefits, especially when you might have system crashes or problems. Being able to recover the information from DMVs, even incomplete information, brings us closer to a robust, flight-recorder kind of system that monitors itself and allows administrators to review information that might help them discover the causes of any issues.

I don’t think that every piece of data needs to be guaranteed to be written to the DMVs, or that the system should slow down to do so, but give the DBAs some control here. If we have the need for more extensive logging, or we want to retain whatever information we can, allow it to persist in the DMVs, or at least make snapshots of DMV data easy to take and store. It’s another form of logging that I’d appreciate having available from the platform.

Steve Jones

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast – MB WMV 
• Watch the iPod Video Podcast – MB MP4 
• Listen to the MP3 Audio Podcast – 4.3MB MP3