SentryOne Team Blog (blogs.sentryone.com)

T-SQL Tuesday #080 : DBCC CLONEDATABASE

T-SQL Tuesday #080 : Chris Yates' Birthday!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 CLONEDATADBCC 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.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 CLONEDATABASESQLSentryDataClone 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 infoSQLSentryData size info

SQLSentryDataClone sizeSQLSentryDataClone 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 GrowthSQLSentryData File Growth

SQLSentryDataClone File Growth and NamesSQLSentryDataClone File Growth and Names

Model Database File GrowthModel 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 PlanSQLSentryData Estimated Plan

SQLSentryDataClone Estimated PlanSQLSentryDataClone Estimated Plan

And here are the Actual Plans:

SQLSentryData Actual PlanSQLSentryData Actual Plan

SQLSentryDataClone Actual PlanSQLSentryDataClone 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 StatisticsSQLSentryData Statistics

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

Summary

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

3 replies on “T-SQL Tuesday #080 : DBCC CLONEDATABASE”

3 Comments (Comments are now closed.)
  1. Two souls, one thought… :)

    I also made DBCC CLONEDATABASE the topic for my T-SQL Tuesday post:
    http://www.pontop.dk/single-post/2016/07/12/TSQL-Tuesday-80-I-want-DBCC-CLONEDATABASE-available-on-all-supported-versions-of-SQL-Server

    Apart from using the clone to solve performance problems, the clone database is also very useful for in data warehouse scenarios, where you need a copy of the production schema in dev.

    I have also created a connect item for support on 2008R2, 2012 and 2016:
    https://connect.microsoft.com/SQLServer/feedback/details/2921830
    if anyone would want the feature there as well

    Best wishes and happy blogging,
    K