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?
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, but that one seemed focused on ongoing analysis, not my one-time situation.)
sp_BlitzIndexto a table before before the maintenance restart?...what other information do you need from those DMVs that isn't already available insp_BlitzIndex?sp_BlitzIndexwith params for database and table name gives you tons more useful detail, and this database has (literally) 100,000 tables, so its not feasible for me to run it for each and archive ALL those results.sp_BlitzIndexwith params for database and table name gives you tons more useful detail" - Interesting, I never had the need to run it for a specific table yet. Usually found the high level index recommendations was sufficient enough.