SQL Server 20016 SP1 is released today and it introduces a truck load of new features and options. Even better, it makes many of the Enterprise only features available in Standard!

Ever since SQL Server 2014 SP2 introduced the new DBCC CLONEDATABASE command to create a schema and statistics only copy of a database for performance troubleshooting, I was secretly hoping that when the DBCC CLONEDATABASE command was ported to SQL Server 2016 it would also include an option to clone Query Store data. And behold! Now that DBCC CLONEDATABASE is also available in SQL Server 2016 SP1 it does just that!

By default, when you run DBCC CLONEDATABASE inside a SQL Server 2016 SP1 instance, it will not only clone the schema data and statistics like it did on a SQL Server 2014 SP2 instance, it will also clone the data that is inside the Query Store for your source database!

Let’s see how this works!

First of all we are going to create a clone of the AdventureWorks database I have running inside my SQL Server 2016 SP1 instance.

DBCC CLONEDATABASE('AdventureWorks', 'AdventureWorks_Clone')

Now this will create a complete clone of the AdventureWorks database, including Query Store data.

With the release of SP1 for SQL Server 2016 we can also specify additional arguments for the DBCC CLONEDATABASE command namely: WITH NO_STATISTICS or NO_QUERYSTORE. I think the arguments are pretty self-explanatory. Using one, or both, the hints will skip the cloning of statistics and/or Query Store data.

Let’s look at the Query Store data inside both the source and the cloned copy of the AdventureWorks database:

-- Query source database
 SELECT
   TOP 10 *
 FROM AdventureWorks.sys.query_store_runtime_stats
 ORDER BY runtime_stats_id DESC

-- Query cloned database
 SELECT
   TOP 10 *
 FROM AdventureWorks_Clone.sys.query_store_runtime_stats
 ORDER BY runtime_stats_id DESC

As you can see both queries return information from the Query Store but….it seems not everything that is inside the source database’s Query Store made it to the cloned database! Notice the runtime_stats_id and execution_time columns, there is runtime data inside the source database’s Query Store that isn’t inside the cloned copy. Interestingly enough, if I run the DBCC CLONEDATABASE command for a second time no Query Store data is updated inside the cloned database.

I am pretty sure I found the reason for this. When DBCC CLONEDATABASE creates a clone of the Query Store data inside the source database it only clones the Query Store data that has already been persisted to disk. Remember, the Query Store records runtime information inside memory first and flushes it to disk based on the interval you configured in the Data Flush Interval option at the Query Store properties page. As a matter of fact, if I run a DBCC CLONEDATABASE right after Query Store runtime statistics are persisted to disk they will be included in the cloned database copy, if I run the command before the data flush, they will be left out.

By default the Data Flush Interval setting is configured to 15 minutes. This means that you might lose just under 15 minutes of Query Store data inside a cloned database copy. I do not know yet if this is intended or not, but keep this in mind when you miss Query Store information inside a cloned copy of your database.

Edit: as a workaround you can execute the sp_query_store_flush_db Stored Procedure to force a flush to disk off all the in-memory Query Store data before executing DBCC CLONEDATABASE.

As for the rest of the data inside the Query Store, as long as it has been persisted to disk before running the DBCC CLONEDATABASE command it’s all there! Including the configuration of the Query Store itself and execution plans that were forced.

Oh, have I mentioned that the custom Query Store Dashboards I launched a while back also work on cloned Query Store data? Will, they DO!