DBCC CLONEDATABASE

Often I hear developers claim that they need production data in order to properly tune their queries. There are a couple of reasons that they believe this. The most important one is likely because the execution plans generated in a development environment with limited data will be different than the ones which are generated with the full scale set of data in production.

Previously, I have blogged about DBCC OPTIMIZER_WHATIF which allows you to pretend that your server has different amount of physical resources. Today I will show you DBCC CLONEDATABASE which allows you to pretend that your database has the same quantity of data and data skew. DBCC CLONEDATABASE was released in SQL Server 2014 SP2 and SQL Server 2016 SP1.

DBCC CLONEDATABASE produces a copy of your production database without any data but it includes all of your objects and statistics. The cloned database will be in read only mode and can be backed up and restored to your development environment for tuning. Generating execution plans in the development environment will now produce the same execution plans as production, giving you the ability to play with different query structures. Your database will be extremely small and easy to move around. It will also be void of all sensitive information which might become a compliance risk.

DBCC CLONEDATABASE ('AdventureWorks2014','AdventureWorks2014_clone')
ALTER DATABASE AdventureWorks2014_clone SET READ_WRITE
ALTER DATABASE AdventureWorks2014_clone SET AUTO_UPDATE_STATISTICS OFF

Above, I set my cloned database back to READ_WRITE mode and turned off AUTO_UPDATE_STATISTICS. I did this so that you can play with objects such as views, stored procedures, and functions during my testing. By turning off AUTO_UPDATE_STATISTICS, you can also load a small measure of test data so that you can perform functionality testing without loosing any of the production statistics needed for the query tuning.

This is not the only way that you can produce the same results, it is simply the most convenient. You could use the generate scripts wizard to script out the entire data base, schema only, and include your statistics from the advanced options menu.

Leave a Reply