DBCC CLONEDATABSE Enhancements in SQL Server 2016 SP1

By: Rajendra Gupta   |   Updated: 2017-02-20   |   Comments   |   Related: > Performance Tuning

Problem

Microsoft SQL Server 2016 recently launched SQL Server 2016 SP1 and in this tip we will explore the enhancements to DBCC CLONEDATABASE.

Solution

In my previous tip, SQL Server DBCC CLONEDATABASE Example, we explored the SQL Server DBCC CLONEDATABASE process with examples. The clone database generates the same objects and statistics as the source database without any data. This enables us to generate the same kind of execution plans, so we can troubleshoot an issue on another database.

In New Features in SQL Server 2016 Service Pack 1, this article shows some of the new features and options provided in SQL Server 2016 SP1 and we will explore DBCC CLONEDATABASE further.

DBCC CLONEDATABASE enhancements in SQL Server 2016 SP1

This feature was first introduced in SQL Server 2014 SP2 and DBCC CLONEDATABASE created a schema and statistics only copy of a database for performance troubleshooting.

A new feature in SQL Server 2016 is the Query Store. The Query Store helps you to track query plans, runtime statistics, regressing queries, and queries/plans history. By default, when we run DBCC CLONEDATABASE inside a SQL Server 2016 SP1 instance, it will not only clone the schema data and statistics, it will also clone the data that is inside the Query Store.

The basic syntax for DBCC CLONEDATABASE is below.

DBCC CLONEDATABASE (source_database_name, target_database_name)

With the release of SP1 for SQL Server 2016 we can also specify additional arguments for the DBCC CLONEDATABASE command namely: NO_STATISTICS or NO_QUERYSTORE as follows:

DBCC CLONEDATABASE (source_database_name, target_database_name) WITH NO_STATISTICS, NO_QUERYSTORE

Example of DBCC CloneDatabase in SQL Server 2016 SP1

For demonstration purposes, I am using a sample database named Adventureworks2014_2016 and the Query Store is enabled on this database.

Below are the source database properties:

Now let's create a clone database as follows:

DBCC CLONEDATABASE ('Adventureworks2014_2016', 'Adventureworks2014_2016_SP1')

With no options specified, this creates a complete clone of the source database, including the Query Store data.  The below information is logged in the SQL Server error logs.

Let's look at the clone database Query Store properties. As we can see below, the clone database also has the same Query Store properties as the source database.

Now let's look at the Query Store data for both the source and the cloned copy of the database.

-- source database
use Adventureworks2014_2016
go
SELECT TOP 10 * FROM sys.query_store_runtime_stats  ORDER BY runtime_stats_id DESC

-- cloned copy
use Adventureworks2014_2016_SP1
go
SELECT TOP 10 * FROM sys.query_store_runtime_stats  ORDER BY runtime_stats_id DESC

We can see that the Query Store data exists in the cloned database as well. There may be differences in the data for the source database Query Store database and the cloned database Query Store data. When DBCC CLONEDATABASE creates a clone of the Query Store data of source database, it only clones the Query Store data that has already been persisted to disk. It is based on the Data Flush Interval option on the Query Store properties page.

DBCC CLONEDATABASE without Query Store Data

If we do not want the Query Store data to be cloned from the source database, we can specify the parameter NO_QUERYSTORE as follows:

DBCC CLONEDATABASE ('Adventureworks2014_2016', 'Adventureworks2014_2016_SP1') with NO_QUERYSTORE

If we go to the cloned database properties, we can see that Query Store is set to Off.

Also, we can query the sys.query_store_runtime_stats DMV to check the Query Store and it shows 0 records.

DBCC CLONEDATABASE without Statistics

Similar to this, if we want to exclude the statistics when creating a cloned database, specify the NO_STATISTICS parameter.

DBCC CLONEDATABASE ('Adventureworks2014_2016', 'Adventureworks2014_2016_SP1') with NO_STATISTICS

DBCC CLONEDATABASE without Statistics and Query Store

Further, if we wish to include the schema only and exclude the Query Store and statistics, specify both parameters NO_STATISTICS and NO_QUERYSTORE as follows:

DBCC CLONEDATABASE ('Adventureworks2014_2016', 'Adventureworks2014_2016_SP1') with NO_STATISTICS,NO_QUERYSTORE

Next Steps

• Read more:
  • DBCC CLONEDATABASE.
  • New Features in SQL Server 2016 Service Pack 1
  • SQL Server 2016 Query Store Introduction
  • Clone a SQL Server Database Using DBCC CLONEDATABASE
  • SQL Server DBCC CLONEDATABASE Example
  • Database Cloning in SQL Server for Troubleshooting and Diagnostics

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips