Preserve PROD DMV metadata for offline analysis

All we need is an easy explanation of the problem, so here it is.

  1. How do I preserve and migrate a point-in-time state of all metadata from a PROD DB to a TEST copy on another server?

I want to review PROD object exec performance and index usage without straining resources or otherwise impacting the state of the PROD DB. I’m primarily interested in sys.dm_db_index_usage_stats and sys.dm_exec_query_stats, plus the other metadata views views commonly used together with them.

  1. In general, do perf/usage DMVs depend on host server properties to calculate results accurately?

  2. I struggled to find unambiguous search terms. What is the correct terminology for describing this perserve and migrate metadata task? I’m not even confident that I’m using the word "metadata" accurately.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I think this might be the best solution for MSSQL 2016+. It’s still not on-demand, but the built-in reporting and tools definitely meet my use cases (regression, A/B testing, resource monitoring). Thanks for the comments, guys, I might not have found this without the help!

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply