Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

DBCC CLONEDATABSE Enhancements in SQL Server 2016 SP1


By:   |   Read Comments   |   Related Tips: More > Performance Tuning

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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:

SQL Server Source Database Properties

Now let's create a clone database as follows:

DBCC CLONEDATABASE ('Adventureworks2014_2016', 'Adventureworks2014_2016_SP1')

DBCC CLONEDATABASE

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.

Complete clone of the source database, including the Query Store data in the SQL Server Error Log

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.

SQL Server Clone Database Properties

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

Query Store Data in Source SQL Server Database

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

Query Store Data in Cloned SQL Server Database

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

No Query Store Data from the Source SQL Server Database

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

For the cloned database properties, the Query Store is Off

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

query this 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

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

DBCC CLONEDATABASE without Statistics and Query Store
Next Steps


Last Update:


signup button

next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools