Columnstore stats in DBCC CLONEDATABASE

DBCC CLONEDATABASE creates a clone of the database that contains a copy of the schema and statistics for troubleshooting and diagnostic purposes. More specifically, with DBCC CLONEDATABASE, you have a lightweight, minimally invasive way to investigate performance issues related to the query optimizer. In SQL Server 2019, we now extend the capabilities of DBCC CLONEDATABASE by adding support for columnstore statistics.

Columnstore statistics support

In SQL Server 2019, support has been added for columnstore statistics. Before SQL Server 2019, manual steps were required to capture these statistics (refer to the following link). We now automatically capture stats blobs, and therefore, these manual steps are no longer required:

https://techcommunity.microsoft.com/t5/SQL-Server/Considerations-when-tuning-your-queries-with-columnstore-indexes/ba-p/385294.

DBCC CLONEDATABASE validations

DBCC CLONEDATABASE performs the following validation checks. If any of these checks fail, the operation will fail, and a copy of the database will not be provided.

• The source database must be a user database.
• The source database must be online or readable.
• The clone database name must not already exist.
• The command must not be part of a user transaction.

Understanding DBCC CLONEDATABASE syntax

DBCC CLONEDATABASE syntax with optional parameters:

DBCC CLONEDATABASE   
(  
    source_database_name, target_database_name
)
    [ WITH { [ NO_STATISTICS ] [ , NO_QUERYSTORE ] 
    [ , VERIFY_CLONEDB | SERVICEBROKER ] [ , BACKUP_CLONEDB ] } ]

The following T-SQL script will create a clone of the existing database. The statistics and Query Store data are included automatically.

DBCC CLONEDATABASE ('Source', 'Destination');

The following messages are provided upon completion:

Figure 1.24: Cloned database output

To exclude statistics, you rewrite the code to include WITH NO_STATISTICS:

DBCC CLONEDATABASE ('Source', 'Destination_NoStats') 
WITH NO_STATISTICS;

To exclude statistics and Query Store data, execute the following code:

DBCC CLONEDATABASE ('Source', 'Destination_NoStats_NoQueryStore') 
  WITH NO_STATISTICS, NO_QUERYSTORE;

Making the clone database production-ready

Thus far, the database clones provisioned are purely for diagnostic purposes. The option VERIFY_CLONEDB is required if you want to use the cloned database for production use. VERIFY_CLONEDB will verify the consistency of the new database.

For example:

DBCC CLONEDATABASE ('Source', 'Destination_ProdReady') 
  WITH VERIFY_CLONEDB;

The output is as follows:

Figure 1.25: Verifying the cloned database