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.

DBCC CLONEDATA – when you only need a ghostly clone, not a full copycat 

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

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

SQLSentryData File Growth

SQLSentryDataClone File Growth and Names

Model Database File Growth

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;

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:

SQLSentryData Estimated Plan

SQLSentryDataClone Estimated Plan

And here are the Actual Plans:

SQLSentryData Actual Plan

SQLSentryDataClone Actual Plan

Statistics

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:

SQLSentryData Statistics

SQLSentryDataClone Statistics

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. 🙂

Melissa Connors

Melissa is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.