on February 28, 2017
The word ‘statistics’ is awfully confusing in SQL Server
It can mean “statistics” themselves – little objects that describe the distribution of data in columns or indexes to help the optimizer.
Or it can mean “usage statistics” - dynamic management views that let you see how many times an index has been used or requested, how many times a query has been run, that kind of thing.
I was curious when I read that DBCC CLONE DATABASE “copies statistics for all indexes”
When the DBCC CLONEDATABASE command was released for SQL Server 2014 SP2 and SQL Server 2016 SP1, it came with detailed documentation.
The wording is just confusing. It seemed pretty clear that column and index statistics, the ones which describe the data, are being cloned. But I got a little hopeful that perhaps some index usage statistics might be copied as well.
So I did some testing with the sample code in this Gist, and here’s what I found.
DBCC CLONEDATABASE doesn’t copy any information from the index usage or missing index DMVs
Your new cloned database has no idea how many times an index was used, or which indexes were requested in the database you cloned.
The index usage and missing index DMVs will record information in your cloned database
If anyone runs queries against the clone database for testing purposes, SQL Server will record the index usage and index requests in the missing index DMVs as normal.
This works even while the cloned database is read-only.
Is DBCC CLONEDATABASE safe?
This new feature is documented and fully supported. I tend to be pretty careful using new features against critical production databases until I’m quite familiar with them, though. I’d rather get to know this in test environments for a while before declaring, “it’s all good”.
Checking connect.microsoft.com…
- Ola Hallengren has filed a bug related to clonedatabase and Query Store
- Joey D’Antoni ran into a bug with clonedatabase and temporal tables, and
- Ben Whitman found a database he can’t clone.
Those bugs haven’t been responded to yet, and I don’t mean to make DBCC CLONEDATABASE sound horrible, just be aware.
Summing up…
If you look at any information in the index usage or missing index DMVs in a “cloned” database, the information you see just reflects activity run against the clone– not against the original database.
REGISTER