By: Rajendra Gupta | Comments | Related: > Performance Tuning
Problem
Microsoft has recently released SQL server 2014 SP2 in which a new DBCC command - DBCC CLONEDATABASE is introduced. I found this command very useful and interesting too. In this tip we will explore the functionality of DBCC CLONEDATABASE.
Solution
Many times we have issues in our environment and we want to analyze a snapshot of the database when the issue occurred with the statistics, structure, indexes, stored procedures, etc. Normally we do so by restoring the database to another environment to the closest point in time or script out the database to load onto another instance, but this can be time consuming, complex and may require additional storage.
With the release of Microsoft SQL Server 2014 SP2 Microsoft solves this problem by introducing the DBCC CLONEDATABASE command. As the name alludes, this command is for creating a database clone. This command creates a new database that contains the schema of all the objects and statistics from the specified source database without any data.
SQL Server DBCC CLONEDATABASE Creation Process
The syntax for the DBCC CLONEDATABASE is:
DBCC CLONEDATABASE (source_database_name, target_database_name)
As per MSDN, it uses a snapshot of the source database for the transactional consistency that is needed to perform the copy. This prevents blocking and concurrency problems when these commands are executed. If a snapshot cannot be created, DBCC CLONEDATABASE will fail.
The DBCC CLONEDATABASE works with the following steps:
- Validate the source database
- Get Shared locks for the source database
- Create internal snapshot of the source database
- Create a clone database (this is an empty database which is inherited from model)
- Get an eXclusive lock for the clone database
- Copy the system metadata from the source to the destination database
- Copy all schema for all objects from the source to the destination database
- Copy the statistics for all indexes from the source to the destination database
- Release all database locks
As soon as the command has finished running, the internal snapshot is dropped. TRUSTWORTHY and DB_CHAINING options are turned off for a cloned database.
SQL Server DBCC CLONEDATABASE Example
Let's explore the DBCC CLONEDATABASE functionality now. For the test I am using the Adventureworks2014 database which is about 7 GB based on my testing.
DBCC CLONEDATABASE (AdventureWorks2014, AdventureWorks2014_Clone)
The DBCC CLONEDATABASE will generate the following message:
Database cloning for 'AdventureWorks2014' has started with target as 'AdventureWorks2014_Clone'. Database cloning for 'AdventureWorks2014' has finished. Cloned database is 'AdventureWorks2014_Clone'. Database 'AdventureWorks2014_Clone' is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the warning the command generates with the database creation that it should be used for diagnostic purposes only.
In the Databases folder of SQL Server Management Studio we can see that the database has been created and is in a 'Read Only' state.
Validating the SQL Server Database File Properties
Now if we compare the properties of this clone database, with the source database we will see differences in terms of the database size.
We can also query the system views to see the basic file properties:
SELECT
db.name AS DBName,
type_desc AS FilType,
Physical_Name AS Location
FROM
sys.master_files mf
INNER JOIN
sys.databases db on db.database_id=mf.database_id
where db.name in('AdventureWorks2014','AdventureWorks2014_Clone')
As we can see the file names for the destination database uses the naming convention of the "source_file_name" plus "underscore_random_number".
Now if we review the file growth of the Model, source and clone database we can see that the growth properties of the clone database match the model database.
SELECT 'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files where database_id in (DB_id('AdventureWorks2014'),DB_ID('AdventureWorks2014_Clone') ,
DB_ID('model'))
ORDER BY type_desc
Execution plan comparison on Source and Cloned SQL Server database
Now to see the actual usage of the clone database, let's run a query against the source database and include the execution plan.
select A.salesorderID,A.Orderdate,A.DueDate,A.SalesOrderNumber, A.onlineorderflag,B.Carriertrackingnumber,B.Unitprice,B.UnitPriceDiscount from [Sales].[SalesorderHeaderEnlarged] A inner join [Sales].[SalesOrderDetailEnlarged] b on A.SalesorderID=B.SalesOrderID order by A.Orderdate desc
Now run the same query against the clone database and see the execution plan.
Based on this example, we can see that the clone database generates the same basic execution plan as of the source database without any data. This enables us to generate the same kind of execution plan, so we can troubleshoot an issue.
One item to note is that the Clone database is read only and will not change. So the statistics will be at the point in time when the clone was created despite changes in the source database.
SQL Server Database Clone vs. Database Snapshot
One common question is: what is the difference between the database clone and database snapshot operations? A SQL Server Database Snapshot is a read-only, static view of a SQL Server database. The engine keeps the Database Snapshot consistent by copying the modified pages from the source database to the snapshot database. While DBCC CLONEDATABASE creates a copy of the schema and statistics only of a user database without any data and no changes are reflected in the cloned database.
Learn more about SQL Server Database Snapshots:
- SQL Server Database Snapshots for Data Comparison
- How to revert a Database Snapshot to recover a SQL Server database
- SQL Server Database Snapshots for Data Comparison
- SQL Database Snapshots for Reporting, Auditing and Data Recovery
Restrictions
Clone database can be created for the following databases:
- The source database must be a user database. Cloning a system database (master, model, msdb, tempdb, distribution, etc.) isn't allowed.
- The source database must be online or readable.
- A database that uses the same name as the clone database must not already exist.
- The command isn't in an explicit transaction.
Next Steps
- Download and explore SQL Server 2014 SP2.
- Read more about DBCC CLONEDATABASE.
- Check out these tips on DBCC CLONEDATABASE:
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
