DBCC Clonedatabase and Query Store

SQLRUs, 2018-06-04 (first published: 2018-05-25)

Cloning things is all the rage these days…..even Storm Troopers.

Anyway, by now, you probably have seen documentation on Query Store and DBCC Clonedatabase.  Query Store, introduced in SQL Server 2016, is considered the “black box” or “flight recorder” of queries.  It retains a history of executed queries, including run-time statistics as well as execution plans from query store enabled databases.   This flight recorder helps to diagnosis and pinpoint query performance issues.

Another useful tool is DBCC Clonedatabase.  This command started was released in older versions via service packs before being fully released in 2016.  This tool allows you to make a “schema” only copy of a database which will reside on the same server as the source database.  The schema includes both user and system schema, as well as any corresponding statistical data.

Why is this useful?

Let’s say you have a 1 terabyte database and wanted to perform query tuning on it. Storage is usually a premium commodity in most organizations and there isn’t a spare terabyte of storage just laying around.  DBCC Clonedatabase would allow you to make a schema only copy, including statistics.  Once the new database has been created, you could move the new database onto another server without having the requirement of large amounts of storage.  Since the database is really a schema-only copy, the footprint of the database is pretty small.  After moving the database, queries ran against it would utilize the statistics contained within to execute the query.  True, there isn’t any data in the database, but you can account for that when perform query performance analysis.


NO_STATISTICS – This option specifies that table/index statistics are excluded. Available with SQL Server 2014 SP2 CU3 & SQL Server 2016 SP1

NO_QUERYSTORE – This option specifies that query store data is excluded.  Available with SQL Server 2016 SP1

VERIFY_CLONEDB – Verifies the consistency of the new cloned database.  Available starting with SQL Server 2016 SP2

BACKUP_CLONEDB – Creates and verifies a backup of the newly cloned database.  Available starting with SQL Server 2016 SP2

The command works in this order:

  1. Creates a new destination database.  Same file layout as the source database however with the default file sizes from Model.
  2. Generates a snapshot of the source database.
  3. Copies the system metadata from the source to the target database created in step 1.
  4. All object schema is copied from the source to the target.
  5. Index statistics are copied from the source to the target.

Fairly easy and straight forward.  But wait! There’s more!

You will noticed the “WITH NO_QUERYSTORE” option in the command.  If you are running SQL Server 2016 SP1 or higher and utilizing Query Store for the source database, DBCC Clonedatabase will also bring over the query store data! This is enabled by default so if you do not want the query store data, you have to explicitly define using NO_QUERYSTORE.  This means, by default, if you clone a database you will get the flight recorder data from query store as well.

These two tools, query store and DBCC Clonedatabase are just another means to help troubleshoot performance issues.




© 2018, John Morehouse. All rights reserved.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis


1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren


1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren


360 reads