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
Last activity: Jul 29, 2022, 03:21 AM