Sample Header Ad - 728x90

One-time archiving of SQL's Index DMV contents for later deep analysis

1 vote
0 answers
59 views
I had planned to do some in-depth index performance analysis on a key production database next week, but realized that it will be restarted during maintenance this weekend, which resets all these DMVs. How can I safely preserve the contents of all the [relevant index-related DMVs](https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/index-related-dynamic-management-views-and-functions-transact-sql?view=sql-server-ver15) ? sys.dm_db_column_store_row_group_physical_stats sys.dm_db_index_operational_stats sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.dm_db_missing_index_details sys.dm_db_missing_index_columns sys.dm_db_missing_index_groups sys.dm_db_missing_index_group_stats Is it sufficient to simply SELECT INTO a holding table for each of these? Are there any of these I really don't need? What about sys.dm_db_index_operational_stats and sys.dm_db_index_physical_stats, which take a bunch of parameters for database/object/index_id? Are these parameters just filters? If I run it with (NULL, NULL, NULL, NULL) will that truly return all rows? Or are they like sp_BlitzIndex where narrowing to a specific table/index gives you *more* information about that specific object? Related question about sp_BlitzIndex: can I use sp_BlitzIndex to analyze my archived DMV data, stored in other tables? Or does it always run against the actual system DMVs? (I found a [similar question](https://dba.stackexchange.com/questions/294963/preserve-prod-dmv-metadata-for-offline-analysis) , but that one seemed focused on ongoing analysis, not my one-time situation.)
Asked by BradC (10023 rep)
Jul 28, 2022, 05:40 PM
Last activity: Jul 29, 2022, 03:21 AM