CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

Attach of the Clone (… Databases)

Attach of the Clone (… Databases)

  • Comments 1

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

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • It is kind of a pain to follow all the steps in the kb 914288. I will try to get my C# SQL Clone tool to follow all the steps. Also, some people have databases with thousands of tables, tens of thousands of stats, and the new string info bloats the statistic size. So cloning large dbs requires special handling. Also, opening very large files in SSMS is cumbersome, so the final transfer should be done from the command line.

Page 1 of 1 (1 items)