Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Clonesome Database

By Phil Factor,

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.

Total article views: 195 | Views in the last 30 days: 2
 
Related Articles
FORUM

Cloned server

Cloned Server

FORUM

Can I programmatically clone a stored procedure in tsql script?

Trying to make a cloning tool.

FORUM

Database Cloning?

Hi fellow DBA's, While I've been a DBA for a few years now, I'm rather green when it comes to SQL S...

FORUM

Cloning

Comments posted to this topic are about the item [B]Cloning[/B] Creating a clone is legal, but it is...

FORUM

Last executed query total duration

Last executed query total duration

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones