Microsoft SQL Server 2016, using dbcc clonedatabase

Logo

Introduction

A new feature has been introduced in SQL Server 2012 : dbcc clonedatabase. This feature has been enhanced in SQL Server 2016 with the major types of objects supported.

How does it work and what is the purpose of this new feature ? dbcc clonedatabase is very useful to diagnose compatibility level impact on queries in a production environment with real time statistics without modifying directly and immediately the compatibility level of the source database, modification that can generate further issues and must be conducted cautiously.

Cloning a database

Cloning a database with dbcc clonedatabase is very easy : 

dbcc clonedatabase ('AdventureWorks','CloneAdventureWorks')
go
          
Database cloning for 'AdventureWorks' has started with target as 'CloneAdventureworks'.
Database cloning for 'AdventureWorks' has finished. Cloned database is 'CloneAdventureworks'.
Database 'CloneAdventureworks' is a cloned database.
This database should be used for diagnostic purposes only and is not supported for use
in a production environment.

What happens with this new command ?

  • Only schema and statistics are copied to the cloned database, data are not copied.
  • Cloned database is set in read only mode.

Database files are created with a random number in the default data and log directories : 

use CloneAdventureworks
go
select physical_name,
   size
from sys.database_files
go
          
physical_name                                           size
-----------------------------------------------------   ----
/sqlpac/mssql/dbfiles/AdventureWorks_2621330649.mdf	    9216
/sqlpac/mssql/tlogfiles/AdventureWorks_1792103105.ldf   9216

Data are not copied, tables are empty, but all statistics when cloning the database have been copied, so the optimizer will choose the same execution plan than the one if tables were filled.

use CloneAdventureworks
go
exec sp_spaceused
go
database_name            database_size      unallocated space
------------------------ ------------------ ------------------
CloneAdventureworks      249.00 MB          103.00 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
303656 KB          162304 KB          114072 KB          27280 KB
exec sp_spaceused "HumanResources.Employee"
go
name          rows       reserved           data               index_size    unused
------------- ---------- ------------------ ------------------ ------------- ------------
Employee      290        200 KB             56 KB              136 KB        8 KB
dbcc show_statistics("HumanResources.Employee", BusinessEntityID) with histogram
go
RANGE_HI_KEY RANGE_ROWS     EQ_ROWS        DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ -------------- -------------- -------------------- --------------
           1            0.0            1.0                    0            1.0
           3            1.0            1.0                    1            1.0
           4            0.0            1.0                    0            1.0

Usage

Execution plans are the same with or without data. This can be useful to troubleshoot a query with poor performance , indeed the option "Include Actual Execution Plan" using current statistics without retrieving data can be used in the cloned database instead of the option "Display Estimated Execution Plan" which is not always relevant.

select BusinessEntityID,LoginID from [HumanResources].[Employee]
order by BusinessEntityID
offset 10 rows
fetch next 20 rows only
go
Exec plan demo

Further more, the right execution plans for DML queries (insert, update, delete) can be diagnosed safely in the cloned database without modifying any data. Even if the cloned database is in read only mode, update queries can be run against as no data are impacted.

DML queries

Live missing indexes recommendations are available in the cloned database for both SELECT and INSERT/UPDATE/DELETE statements :

delete from Sales.SalesOrderDetail where Unitprice > 10
DML queries, index recommendations

The best feature of the database cloning with only the statistics is the ability to compare execution plans when changing the compatibility level, very very useful for regression testing due to the compatibility level.

Obviously the cloned database is in ready only mode, so the compatibility level can not be changed :

alter database CloneAdventureworks set compality_level=110
go
        
Msg 3906, Level 16, State 1, Server vps650326, Line 1
Failed to update database "CloneAdventureworks" because the database is read-only.
Msg 5069, Level 16, State 1, Server vps650326, Line 1
ALTER DATABASE statement failed.

So alter the database in read-write mode :

alter database CloneAdventureworks set read_write with no_wait
go

VERY IMPORTANT : disable auto update statistics, otherwise statistics snapshot generated with the command dbcc clonedatabase may be altered. Obviously, don’t recreate or alter indexes, statistics will be lost too.

alter database CloneAdventureworks set auto_update_statistics off with no_wait
go

Now the compatibility level for the cloned database can be altered : it won’t be useful for CPU and I/O statistics but we can now diagnose execution plans modifications when compatibility level is modified. In this example, a query running in a cloned database of AdventureworksDW : 

use CloneAdventureworksDW
go
SELECT c.CalendarYear
	,b.SalesTerritoryRegion
	,FirstName + ' ' + LastName AS FullName
	,count(SalesOrderNumber) AS NumSales
	,sum(SalesAmount) AS TotalSalesAmt
	,Avg(SalesAmount) AS AvgSalesAmt
	,count(DISTINCT SalesOrderNumber) AS NumOrders
	,count(DISTINCT ResellerKey) AS NumResellers
FROM FactResellerSalesXL_PageCompressed a
INNER JOIN DimSalesTerritory b ON b.SalesTerritoryKey = a.SalesTerritoryKey
INNER JOIN DimEmployee d ON d.Employeekey = a.EmployeeKey
INNER JOIN DimDate c ON c.DateKey = a.OrderDateKey
WHERE b.SalesTerritoryKey = 3
	AND c.FullDateAlternateKey BETWEEN '1/1/2006' AND '1/1/2010'
GROUP BY b.SalesTerritoryRegion,d.EmployeeKey,d.FirstName,d.LastName,c.CalendarYear
GO
go

With compatibility level 130 (SQL Server 2016) :

Exec plan compatibility_level 130

When modified to compatibility level 150 (SQL Server 2019) : 

alter database CloneAdventureworksDW set compatibility_level=150
go
Exec plan compatibility_level 150

Preparing an empty database without reverse engineering

Another interesting usage with dbcc clonedatabase is the ability to prepare an empty database containing only the objects, with no data and no statistics, useful when no source code is available to recreate an empty database with only objects otherwise than using reverse engineering options with Management Studio, PowerDesigner… : 

dbcc clonedatabase('Adventureworks','CloneAdventureworks') with no_statistics
go
alter database CloneAdventureworks set read_write with no_wait
go
backup database CloneAdventureworks to disk='CloneAdventureworks.bak'
go

Then in the target environment :

restore database Adventureworks from  disk='CloneAdventureworks.bak'
with move 'AdventureWorks_Data' to '/sqlpac/mssql/dbfiles/AdventureWorks.mdf',
move 'AdventureWorks_Log' to '/sqlpac/mssql/tlogfiles/AdventureWorksldf',
replace
go

Conclusion

dbcc clonedatabase can be very helpful for diagnosing issues due to the compatibility level in a production environment without copying the entire database to another environment which may not have the same hardware profile (CPU, disks…).