SQL SERVER – Copy Statistics from One Server to Another Server

I was recently working on a performance tuning project in Dubai (yeah I was able to see the tallest tower from the window of my work place). I had a very interesting learning experience there. There was a situation where we wanted to receive the schema of original database from a certain client. However, the client was not able to provide us any data due to privacy issues. The schema was very important because without having an access to underlying data, it was a bit difficult to judge the queries etc. For example, without any primary data, all the queries are running in 0 (zero) milliseconds and all were using nested loop as there were no data to be returned. Even though we had CPU offending queries, they were not doing anything without the data in the tables. This was really a challenge as I did not have access to production server data and I could not recreate the scenarios as production without data.

Well, I was confused but Ruben from Solid Quality Mentors, Spain taught me new tricks. He suggested that when table schema is generated, we can create the statistics consequently. Here is how we had done that:

Once statistics is created along with the schema, without data in the table, all the queries will work as how they will work on production server. This way, without access to the data, we were able to recreate the same scenario as production server on development server.

When observed at the script, you will find that the statistics were also generated along with the query.

You will find statistics included in WITH STATS_STREAM clause.

What a very simple and effective script.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

20 thoughts on “SQL SERVER – Copy Statistics from One Server to Another Server

  1. Indeed, very simple, yet excellent trick! I am going to try that tomorrow itself to see how this works with our development databases. This has tremendous potential in performance tuning when you don’t have enough space to get all the data in-house or have security considerations (as in your case).
    Simply wonderful! Thank-you!

    Like

  2. This is neat. I wonder though – since SQL Server auto updates statistics when they are more than 20 percent out of date – the first query run would use the fake stats while the engine updates the stats for the table on a separate thread. The second query run against the table would then use updated stats which would nullify the intentions of the fake stats.

    Like

  3. Thanks Pinal.May be next time in during production issue I will recommend this. Till now I was recommending on stimulating the statistics in dev or test environment by deceiving the optimizer.SQL Server 2005/2008 gives that functionality.

    Like

  4. Pingback: SQLAuthority News – An Year Worth Remembering and Looking Forward to Better Next Year « SQL Server Journey with SQLAuthority

  5. Great topic yet again!
    In SQL PSS every perf engg sure would have crossed this.
    An additional thing i used to do is create a test DB from the script and change DB state to READ_ONLY, just to be sure that stats don’t get modified during t/s.

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

  7. Remember that your production environment will (for the most part) have different hardware than your Dev/Test environments. Therefore the Query Plans can differ slightly (seldom significantly) from what you see replicated in DEV to what SQL Engine will do in PROD.

    Like

  8. Pingback: SQL SERVER – How to Script Statistics from SSMS? – Scripting Statistics is Disabled in SSMS | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s