SQL Server 2016 SP1 'dbcc clonedatabase' is swift & nifty

I think Microsoft has done a very good job of delivering technical documentation for SQL Server 2016 early in its life.

This kb is a good example.

How to use DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 and SQL Server 2016 SP1
https://support.microsoft.com/en-us/kb/3177838/

Erin Stellato gave DBCC CLONEDATABASE a look in this blog post.

Expanding the uses of DBCC CLONEDATABASE
https://sqlperformance.com/2016/08/sql-statistics/expanding-dbcc-clonedatabase/

So among other assorted fun, today I did this on SQL Server 2016 SP1.

SET statistics time on
SET statistics io on
DBCC CLONEDATABASE (SRCE, LONNY_CLONE) WITH NO_QUERYSTORE

The resulting message.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Database cloning for 'SRCE' has started with target as 'LONNY_CLONE'.
Database cloning for 'SRCE' has finished. Cloned database is 'LONNY_CLONE'.
Database 'LONNY_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.

 SQL Server Execution Times:
   CPU time = 40643 ms,  elapsed time = 47261 ms.

So... what is in that new LONNY_CLONE database?

SELECT type_desc as item_desc, COUNT(*) as item_count 
FROM sys.objects 
WHERE is_ms_shipped = 0 AND
type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE') 
GROUP BY type_desc
UNION ALL
SELECT 'columns' AS item_desc, COUNT (*) as item_count 
FROM sys.columns sc join sys.tables st ON sc.object_id = st.object_id 
WHERE st.type_desc = 'user_table' AND st.is_ms_shipped = 0
UNION ALL
SELECT 'qo_stat' as item_desc, COUNT (*) as item_count
FROM sys.stats ss 
JOIN sys.tables st ON ss.object_id = st.object_id
UNION ALL
SELECT 'nonclustered_index' as item_desc, COUNT (*) AS item_count 
FROM sys.indexes si 
JOIN sys.tables st ON si.object_id = st.object_id 
WHERE st.type_desc = 'user_table' AND st.is_ms_shipped = 0
AND si.type_desc = 'NONCLUSTERED'
UNION ALL
SELECT 'nondefault_schemas' AS item_desc, COUNT (*)
FROM sys.schemas WHERE schema_id BETWEEN 5 AND 16383
ORDER BY item_count DESC

There's plenty in there :-)

How big is the database?

SELECT file_id, type_desc,
CONVERT(NUMERIC(18,2), size * 8./1024.) AS size_mb
FROM sys.database_files

Not bad.  448 mb of database files, 968 mb of transaction log.

On a database with this much DDL (often databases like this have well over 60,000 query optimizer stats - this one just happened to be light in that area), using the scripts from SSMS to create a clone database can be really painful.  This is kinda fun :-)

This is going to be a great tool to enable investigating estimates and plan selection/shape.

Its a good thing that not every performance issue falls into those categories, or I'd have fewer edge cases to cherish. :-)