T-SQL Tuesday #080 : DBCC CLONEDATABASE
DBCC CLONEDATABASE post is my first contribution to the T-SQL Tuesday series and that is mostly due to it being Chris Yates' birthday. He gives a lot to the community and this post is my gift to him. Happy Birthday, Chris! Additionally, it's my mom's birthday, therefore I must say Happy Birthday, Mom!
Yesterday, Microsoft released SQL Server 2014 Service Pack 2, which contains a new command:
DBCC CLONEDATABASE. This new command creates a clone of a database's schema and statistics (not the data). Since it has the statistics, it is useful for troubleshooting, investigating, and diagnosing performance issues. As a result, you need not use your production database to diagnose a problematic query. Hence, the database clone provides the necessary information from the query optimizer. For details, supported objects, and additional ideas on when to use it, read Microsoft's kb3177838 article.
DBCC CLONEDATA – when you only need a ghostly clone, not a full copycat [photo credit: https://flic.kr/p/8qAzb3]
Cloning the database
List of databases before cloning.
First of all, the syntax for
DBCC CLONEDATABASE is rather simple. You need only the original database (source) and desired name of the clone (target) as the parameters. In the example below, I am cloning the SQLSentryData database to SQLSentryDataClone:
DBCC CLONEDATABASE (SQLSentryData, SQLSentryDataClone);
Cloning a SQL Sentry Database
Database cloning for 'SQLSentryData' has finished. Cloned database is 'SQLSentryDataClone'.
Database 'SQLSentryDataClone' is a cloned database.
A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Query executed successfully.
SQLSentryDataClone via DBCC CLONEDATABASE
Perhaps most noteworthy is that the SQLSentryDataClone database is a read-only database. Also, it does not contain the data from the SQLSentryData tables.
SQLSentryData size info
SQLSentryDataClone size info
File Names and Random Numbers
Another thing to note is that the clone's file names have a
_random-number to the name format. Additionally, the cloned database is created with size and growth info based on the
Since I wanted to see this in action, I put together a simple join to illustrate what happens while executing a query against each database:
select * from dbo.PerformanceAnalysisData d join dbo.PerformanceAnalysisCounter c on c.ID = d.DeviceID where c.PerformanceAnalysisCounterCategoryID = 8 and d.EventSourceConnectionID = 4;
Query used for Estimated and Actual Plans
Below are the Estimated Plans obtained through SQL Sentry Plan Explorer for the query and, as you can see, they are almost identical:
And here are the Actual Plans:
Finally, keep in mind that because the clone is a read-only, empty database, you should be able to test repeatedly without updating statistics and skewing your results. Since I wanted to see this for myself, I executed a set of updates and selects against the SQLSentryData and SQLSentryDataClone databases. As a result of the lack of data and read-only database status, there were no actual updates in SQLSentryDataClone. Consequently, the statistics were updated in the SQLSentryData database, but remained the same in the SQLSentryDataClone database:
I deleted and re-created SQLSentryDataClone more than once in my testing, hence those statistics were updated after the Date Created time in one of the images above.
DBCC CLONEDATABASE is a new, easy-to-use command in SQL Server; because of its simplicity, it is a command that many people will be able to use now. It seems like it will probably be a useful tool to add to the box for testing performance query issues. Furthermore, it's a great birthday gift from Microsoft to Chris Yates. :-)