Subscribe to this blog!
Its a well known fact that restarting SQL Server will reset the contents of most Dynamic Management Views (DMVs). What's less well known are the other ways these DMVs are reset.
The other night I was preparing the indexing component of my worst practices session for this year's SQLPass summit, and I was confused as to why the contents of the sys.dm_db_index_usage_stats was completely empty after I'd just ran a script to "warm up" the database by running some queries.
It turns out that I'd enabled the AutoClose option on the database in order to trigger an alert from my Policy Based Management policy I'd setup the previous night. Having the memory of a aging fish, I'd completely forgotten about it. After running the warm up script, I closed the query window, which triggered the autoclose event, as that was the only remaining connection to the database.
DMV contents are reset under the following conditions;
- SQL Server restart,
- AutoClose events,
- Taking a database offline
When assessing the contents of a DMV, be sure to think about when any of the above events last occurred.
Cheers,
Rod.
PS: My book is at the printers :-) Read Kevin Kline's forward here
Comments