Its rare that we come across peculiar (easy though) questions to answers within the forums here, but today a question got me interest. The matter is "As in SQL 2005 there are DMVs available. I want to ask what is the retention period for the data for these views? How long I can go back and look at the information? I don't find this information anywhere."
To open up the short forms of DMV - Dynamic Management Views and DMF - Dynamic Management Function are the new handy tools to the DBAs in SQL Server 2005 by not relying upon other software tools or so. Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of dynamic management views and functions:
- Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
- Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database
As specified both the statements return internal, implementation-specific state data. Further that Microsoft SQL Development team suggested that the schemas and the data they return may change in future releases of SQL Server, so better be-wise to use them or read BOL when using same code in upcoming versions of SQL Server.
The reply to the question is that majority of DMVs and DMFs are not persisted and will reset themselves back to zero once SQL Server is restarted. For instance like sys.dm_db_index_usage_stats the counters are resetted every time the SQL Server service is restarted.