T-SQL Tuesday #080 : DBCC CLONEDATABASE

This 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!

DBCC CLONEDATABASE

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.

Cloning the database

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 started with target as 'SQLSentryDataClone'.
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.

Perhaps most noteworthy is that the SQLSentryDataClone database is a read-only database. Also, it does not contain the data from the SQLSentryData tables.

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 model database.

Estimated and Actual Query Plans

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;