First published on MSDN on Jul 08, 2009
Sorry Star Wars fans to mislead you. I thought our readers should know about a feature we use quite a bit in CSS called a “Clone Database”. It is better publically now known as a “Statistics-Only” copy of the database. Why is this useful? Because it provides CSS a way to reproduce and diagnose query compilation and plan issues without actually having your data or the entire database.
This concept goes back several years when my colleague Keith Elmore worked with Lubor Kollar, then a program manager in the engine team(now a program manager for our SQLCAT team), to come up with an idea to “clone” a database without the data. Back then it was a tool they wrote that copied over the schema and statistics to a new database. So when we need to see what an estimated plan looks like, we can do it without actually having the entire database. This proved especially useful for situations where customers could not send us their database due to privacy or size reasons. But the tool and the process had issues. One was that we actually manually copied stat blobs from system tables.
In SQL Server 2005, the engine added support to extract out the stat blob via the CREATE STATISTICS T-SQL command. Then with SQL Server 2005 SP2, we added support for this in Management Studio. This support continues with SQL Server 2008 and beyond.
CSS has produced a KB article that shows you how to use Management Studio with the right options to “script out” a database including statistics. This now serves as our “clone database” technology when working with query plan issues.
You can read more about how to do this at:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914288
Consider trying out this process on your database to see if it works. It might help save you time when asking for CSS assistance on a query performance problem. You also may find it handy to evaluate what estimated plans will look like on an upgraded version of the server on a test machine.
Bob Ward
Microsoft
