This is a quickie post to introduce the new DBCC command CLONEDATABASE.

Okay so this isn’t technically a SQL Server 2016 only feature as it’s also available in SQL 2014 SP2 onwards – but that wasn’t released until July 2016.

CLONEDATABASE allows you to do something you could only do before through the GUI in SSMS which is to create a copy of a database without the data, but with the Statistics objects that describe the distribution of data.

Why is this useful?

Often in our test and development environments we don’t have access to a set of databases with an amount and distribution of data that we would have in production. This means that queries aren’t likely to perform the same way they would in a production system, so we don’t spot performance issues until they are happening out in the wild. If we can obtain a cloned copy of a production database, that includes the statistics, we can at least see what execution plans would be formed by our queries, and that can give us a good idea ahead of time what changes or indexes might be needed.

It’s also great for dealing with production issues as you can see how a query would behave and as you make changes to progress a fix you can use the cloned copy to see if the new query will produce the plan you expect.

Because the cloned database doesn’t include the data we are able to get around the restrictions that would prevent us from copying live data into our test or development environments. I should mention that the statistics information could be seen as being “data” so it is worth making sure that it is acceptable to take even this information out of production.

Even better in SQL 2016

What’s even better in 2016, is that if you have Query Store enabled, CLONEDATABASE will by default also take a copy of your Query Store. This means you have access to the actual execution plans being used in production – and how they have changed over time – as well as the numbers on execution performance. This saves you having to get a production DBA or client to run queries for you to return this information. For many scenarios, just getting a copy of the cloned database will be all the information you need to dig into and start resolving issues with query performance – all in one package.

So how do I do it?

To Clone a database, the command is:

DBCC CLONEDATABASE('{Database Name}','{Name for Cloned Copy');

If I create one for the WideWorldImporters database used in my Query Store examples, it is simply:

DBCC CLONEDATABASE('WideWorldImporters','WideWorldImporters_Clone');

I can then see it in my set of database in SSMS:

As you can see it is Read-only – and also contains a read-only copy of the Query Store.

Then you just need to back it up and restore it to a development or test (or local environment) to do your investigation and troubleshooting.

Magic