DBCC Clonedatabase and Query Store

Published On: 2018-05-25By:

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.

Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link