1

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.)

4
  • 1
    Why not just log sp_BlitzIndex to a table before before the maintenance restart?...what other information do you need from those DMVs that isn't already available in sp_BlitzIndex?
    – J.D.
    Jul 28 at 18:18
  • @J.D. I intend to do that at the top level; but running sp_BlitzIndex with 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.
    – BradC
    Jul 28 at 18:32
  • When working with live data, I use the top-level results to decide which tables to run the more detailed analysis on; that's the part I'm fearing will be lost if I don't archive the right pieces in the right way.
    – BradC
    Jul 28 at 18:33
  • "but running sp_BlitzIndex with 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.
    – J.D.
    Jul 28 at 18:44

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.