SQL with Manoj

 
Have you ever been in a similar situation where your PROD database size is in GBs or TBs, and for a code-release validation or some performance-fix you need to restore it on an Dev or Test server? You know that taking backup & restore will take lot of time, but you have no other option.

We also face this many a times as our PROD database size ranges from 500 GB to 1-2 TB, and we end up waiting 4-5 hrs in this activity.

There are even third party tools, but they also take good time and have their own pros & cons.
 

Now SQL Server brings a new feature with SQL Server 2016 SP1 & 2014 SP2, i.e. DBCC CLONEDATABASE, to create a new database clone of an existing database within seconds. The new cloned database created is ReadOnly, with no data, but with Statistics.

With DBCC CLONEDATABASE feature you can generate a clone of a database in order to investigate a performance issue related to a Query or Workload.

Note: As best practice suggested by Microsoft product team this clone Database is not supposed to remain in PROD database, but can be moved to a Dev or Test box for further troubleshooting and diagnostic purposes.
 

–> Syntax:

DBCC CLONEDATABASE (source_db_name, target_clone_db_name)
[WITH [NO_STATISTICS][,NO_QUERYSTORE]]

–> The above statement creates Clone of the source database in following operations:

1. Validate the source database.

2. Get S lock for the source database.

3. Create snapshot of the source database.

4. Create an empty database by inheriting from “model” database, and using the same file layout as the source but with default file sizes as the “model” database.

5. Get X lock for the clone database.

6. Copies the system metadata from the source to the destination database.

7. Copies all schema for all objects from the source to the destination database.

8. Copies statistics for all indexes from the source to the destination database.

9. Release all DB locks.
 

–> Now let’s create a Clone Database on AdvantureWorks database:

-- With Stats

DBCC CLONEDATABASE ('AdventureWorks2014', 'AdventureWorks2014_CloneDB')

Message:

Database cloning for ‘AdventureWorks2014’ has started with target as ‘AdventureWorks2014_CloneDB’.

Database cloning for ‘AdventureWorks2014’ has finished. Cloned database is ‘AdventureWorks2014_CloneDB’.

Database ‘AdventureWorks2014_CloneDB’ 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.
 

–> I’ll create one more Clone Database without Stats:

-- Without Stats

DBCC CLONEDATABASE ('AdventureWorks2014', 'AdventureWorks2014_CloneDB_No_Stats')
WITH NO_STATISTICS, NO_QUERYSTORE

–> Let’s check the file size of all 3 Databases:

– The 1st image shows the size of original AdventureWorks2014 database i.e. 207 MB

– And 2nd and 3rd shows the size of other two Clone Databases i.e. just 16 MB.

–> Now we will check the Execution Plans of a query on all these three databases:

-- Check Execution plan on below T-SQL query in all 3 databases:

SELECT 
	P.BusinessEntityID, P.Title, P.FirstName, P.MiddleName, P.LastName, 
	E.BirthDate, E.Gender, E.HireDate, E.JobTitle, E.MaritalStatus, 
	D.Name
FROM [Person].[Person] P
INNER JOIN [HumanResources].[Employee] E ON E.BusinessEntityID = P.BusinessEntityID
CROSS APPLY (
	SELECT TOP 1 DepartmentID
	FROM [HumanResources].[EmployeeDepartmentHistory] DH 
	WHERE DH.BusinessEntityID = E.BusinessEntityID 
	ORDER BY StartDate DESC) EDH
INNER JOIN [HumanResources].[Department] D ON D.DepartmentID = EDH.DepartmentID

– On executing the above query on original AdventureWorks2014 database & AdventureWorks2014_CloneDB it shows same execution plan, like it shows:

1. Hash Match operator
2. 72% cost on Person PK
3. 18% cost on EmployeeDepartmentHistory PK

(click on the image to expand)

– But on executing the same query on AdventureWorks2014_CloneDB_No_Stats it shows different execution plan, here it shows:

1. Nested Loop operator
2. 92% cost on Person PK
3. 5% cost on EmployeeDepartmentHistory PK

(click on the image to expand)