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. :-)
