DBCC Clonedatabase and Query Store

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.

DBCC CLONEDATABASE (source_database_name, target_database_name) WITH [NO_STATISTICS],[NO_QUERYSTORE],[VERIFY_CLONEDB],[BACKUP_CLONEDB]

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.