Retention period for various DMVs such as sys.dm_db_resource_stats, sys.dm_os_wait_stats

Question

I want to understand retention period for various DMVs (Dynamic Management Views). I specifically want to know the retention period for DMVs such as sys.dm_db_resource_stats, sys.dm_os_wait_stats, sys.dm_database_replica_states, sys.dm_os_performance_counters, sys.dm_exec_query_stats, sys.dm_exec_query_plan, sys.dm_exec_sql_text, sys.dm_exec_query_profiles, sys.dm_exec_query_plan_stats, and sys.dm_io_virtual_file_stats

PS - Based on common issues that we have seen from customers and other sources, we are posting these questions to help the Azure community.

Answer 1

Greetings!

Historical data is maintained for approximately one hour. sys.dm_db_resource_stats.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database?view=azuresqldb-current

sys.dm_os_wait_stats All data is cumulative since the last time the statistics were reset or the database engine started

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql?view=sql-server-ver16

sys.dm_database_replica_states, it keep the actual replica state.

Depending on the action and higher-level states, database-state information may be unavailable or out of date. Furthermore, the values have only local relevance.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-replica-states-azure-sql-database?view=azuresqldb-current

sys.dm_os_performance_counters

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql?view=azuresqldb-current

sys.dm_exec_query_stats

Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-ver16

sys.dm_exec_query_plan When a plan is removed from the cache, the corresponding rows are eliminated from this view.

sys.dm_exec_sql_text When a plan is removed from the cache, the corresponding rows are eliminated from this view.

sys.dm_exec_query_profiles

Monitors real time query progress while the query is in execution

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-profiles-transact-sql?view=sql-server-ver16

sys.dm_exec_query_plan_stats

Returns the equivalent of the last known actual execution plan for a previously cached query plan.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-stats-transact-sql?view=azuresqldb-current

sys.dm_io_virtual_file_stats

Returns I/O statistics for data and log files.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-ver16

Regards

Geetha