4

I have a huge table on one server, and need to copy the table to the production server. I can do this, but then I need to generate statistics, which will take a long time on a system that I don't want to ask to handle a huge job. If I move the entire database, I need to take down the system in order to remove the existing database, which is a no-no.

Pinal Dave has a post, http://blog.sqlauthority.com/2011/01/05/sql-server-copy-statistics-from-one-server-to-another-server/ In the post, he outlines how to copy statistics from one table to a new one using Microsoft's "Generate Scripts," which allows copying via the undocumented STATS_STREAM feature. This is for a new table. I'm not sure if I can create a table with the statistics and then write in all of the data, since that should make the system want to update the statistics - possibly even if I have autogenerate statistics off.

I am using the following code: (The Table is InvTbl, and the statistics on that table I am updating is GammaTheta. The statistics exist, and the data is there.)

Update STATISTICS dbo.InvTbl [GammaTheta]  WITH STATS_STREAM = 0x0100002000...

First, does anyone know if Stats_Stream contains all of the statistics data? Then, importantly, how is it being stored in binary?

Note: The table stores a statistical distribution of two variables which we look up based on a join, and has some really important features about the distribution of the inputs. It is static, and I will never need to recalculate the values or the stats once loaded. Not using fullscan is not an option, based on testing, as join speed matters and is affected.

Note 2: No, the number does not end with an ellipsis. I left the data off.

David Manheim
  • 2,553
  • 2
  • 27
  • 42

1 Answers1

4

If I understand your question, the stats stream is a binary representation of the statistics data and can be used to fully re-create the statistics, even on another table with different data (though the same schema). Kevin Kline blogged about that here. I took a swing at better automation of it here.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Does a clone database retain the same statistics after copying additional data into the database? – David Manheim Apr 25 '12 at 11:53
  • 1
    Statistics are updated only when you issue an explicit 'update statistics' statement or if either of 'is_auto_update_stats_on' or 'is_auto_update_stats_async_on' is set for your database and ~10% of your data has changed since the stats were last updated. Any way you slice it though, stats aren't maintained in real time like indexes are. – Ben Thul Apr 25 '12 at 15:13
  • I need it not to update even when I add rows. I can keep auto_update off, I guess. – David Manheim Apr 27 '12 at 13:57