All we need is an easy explanation of the problem, so here it is.
- 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_exec_query_stats, plus the other metadata views views commonly used together with them.
In general, do perf/usage DMVs depend on host server properties to calculate results accurately?
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 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 🙂