Is it possible to save DMV's in SQL server database backup, which can be restored on different server, I mean with master db details( views and all)? If not, what's the best way to maintain 2 environment( one for Performance Testing and replica of it for analysis), so that database which has been used for testing can be analysed by team for optimizing queries without stopping further test execution window.

  • You can collect the DMV data in some tables in your database, that way they will be included in the backup. A good read: bit.ly/1RuCyFR – Razvan Zoitanu May 11 '15 at 11:47
  • Yeah, but that will not persist the actual execution plan which can be looked upon later on. – Prateek Yadav May 12 '15 at 14:33

The DMV's themselves are the same between same builds of SQLServer and give you a snapshot or a cumulative counter of the status of your server at any given time.

You cannot restore the database to a new server with the metadata from the old one. You can make snapshots of some of the performance metrics into a table but you cant restore the state of the DMV's. You can however grab the query plans from your production server and use plan guides to have the same behavior in test, if you want to mimic specific behavior of a single query that is not behaving in production environment but runs ok in test.

You can then of course use the Management Data Warehouse to collect information about performance of queries and the system to find which queries to optimize or third party tools that can collect query plans and wait statistics on the server. Here is a way to collect the query plans using sp_whoisactive

Not the answer you're looking for? Browse other questions tagged or ask your own question.