SQL Server – Clone Database – Schema & Statistics

Background

Working with my best friend to troubleshoot a non-performant query and so here we go.

Scenario

The database is too big and contains data that should be kept private and so what to do.

DBCC Clone

Outline

  1. Source SQL Instance
    • dbcc  clone
    • Backup resulting database
  2. Destination SQL Instance
    • Restore database
  3. Compare Queries

Source SQL Instance

DBCC Clone

SQL

DBCC CLONEDATABASE 

	(
		  [WideWorldImportersDW]
		, [WideWorldImportersDW_DBCCClone]
	);   

GO
GO

Database Backup

SQL

exec master..xp_create_subdir 'E:\temp'
go

backup database [WideWorldImportersDW_DBCCClone]
to disk = 'E:\temp\WideWorldImportersDW_DBCCClone.bak'
with init, format, stats=1 

GO

Destination SQL Instance

Database Restore

SQL

USE [master]
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone'
go

exec master..xp_create_subdir 'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone'
go

RESTORE DATABASE [WideWorldImportersDW_DBCCClone] 

FROM  DISK = N'C:\Temp\WideWorldImportersDW_DBCCClone.bak' WITH  FILE = 1

	,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_167133806.mdf'
	,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_UserData_1341343279.ndf'
	,  MOVE N'fg_Partition_Year_Base__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Base_file_001_4162930605.ndf'
	,  MOVE N'fg_Partition_Year_2010__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2010_file_001_3028494415.ndf'
	,  MOVE N'fg_Partition_Year_2011__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2011_file_001_474007300.ndf'
	,  MOVE N'fg_Partition_Year_2012__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2012_file_001_3961387451.ndf'
	,  MOVE N'fg_Partition_Year_2013__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2013_file_001_2554884830.ndf'
	,  MOVE N'fg_Partition_Year_2014__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2014_file_001_3195214954.ndf'
	,  MOVE N'fg_Partition_Year_2015__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2015_file_001_2796606126.ndf'
	,  MOVE N'fg_Partition_Year_2016__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2016_file_001_2922730419.ndf'
	,  MOVE N'fg_Partition_Year_2017__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2017_file_001_89564842.ndf'
	,  MOVE N'fg_Partition_Year_2018__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2018_file_001_2594545631.ndf'
	,  MOVE N'fg_Partition_Year_2019__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2019_file_001_102789485.ndf'
	,  MOVE N'fg_Partition_Year_2020__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_2020_file_001_2559250543.ndf'
	,  MOVE N'fg_Partition_Year_Null__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Null_file_001_3677708393.ndf'
	,  MOVE N'fg_Partition_Year_Next__01' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_fg_Partition_Year_Next_file_001_2414564952.ndf'
	,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImportersDW_DBCCClone\WideWorldImportersDW_3476128794.ldf'
	,  NOUNLOAD
	,  STATS = 5

GO

Compare Queries

Outline

  1. Compare Queries
    • Query 1

Compare Queries

Query 1

SQL
set nocount on
go

set statistics io on
go

declare @dateTS datetime
declare @date datetime

set @dateTS = getdate()
set @date = convert(varchar(10), @dateTS, 23)

select
          @dateTS as [@dateTS]
        , @date as [@date]

select top 10 *

from   [Dimension].[Date] tblD

select top 10 *

from   [Dimension].[Date] tblD

where  tblD.[Date] = @date
Output
Output – Source

query1_Source_20181115_0911AM

Output – Destination

query1_Destination_20181115_0912AM

Explanation
  1. We have data from original source
  2. And, not data from the resultant cloned database

 

Query Plan
Query Plan – Destination

queryPlan_Destination_20181115_0853AM

Query Plan – Source

queryPlan_Source_20181115_0905AM.PNG

Statistics IO
Image – Destination

statisticsIO.Destiation.2018115.0855AM

Image – Source

statisticsIO.Source.2018115.0856AM

Explanation
  1. Statistics IO
    • Scan Counts will match
    • Logical & Physical Reads will not
      • As Source has actual data, but destination does not

 

References

  1. Microsoft
    • Docs / SQL / T-SQL / Database Console Commands
      • DBCC CLONEDATABASE (Transact-SQL)

 

2 thoughts on “SQL Server – Clone Database – Schema & Statistics

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s