Imagine the following problem: the production database is laboring with some queries that used to go like lightening. Now they don't, and the database is running like a hand-cranked coffee grinder. Maybe the data volume has increased, or changed in its distribution. It might be that the statistics no longer properly reflect the data. Those indexes that were put in place originally may no longer be appropriate.

You need a quiet couple of hours poking about with DMVs, and checking execution plans to get to the bottom of the problem…but…you can't use your production server for this, and you can't just make a copy the production database either. It's too big, and the data is private.

You're not lost, however, because the performance of any query depends on the query optimizer, which uses the database metadata, hardware environment and session state in order to create query plans; but you don't actually need the data as well. You can do a lot with what's known as a 'Statistics-only clone' database. You can execute the query on the clone, and it will give you the same execution plan as it did on the production database.

You will need to script out the statistics and histograms as well as the metadata. This can be done from SSMS, though it is better done via SMO. If you have a monster database, and are sure which queries are causing the problem, you need only script out the objects that are referenced by the queries. This script can then be used to create a clone. You'll notice it is much bulkier than most scripts because it includes all the histograms and other statistical information as BLOBs. The troublesome query, when run against the cloned database, will still return the same query execution plans as it would if executed against the original database, even though the size of the clone database is much smaller than the original database.

This is best treated as a static system that just tells you the execution plans for the queries. It is strange to use a database that thinks it has a huge amount of data but actually hasn't, but it is valuable to see the plans change as you rewrite the queries.

If you accidentally trigger an update of statistics, you can always recreate the clone from script. It is just very convenient, and far less stressful to use than going near a working production or staging database. If you schedule the SQL script-generation as a PowerShell/SMO routine, then you can do a quick investigation pretty soon after being alerted to a problem.

Phil Factor.