1

I am trying to create statistics on a table that already exists. I kept getting this error : The provided statistics steam is corrupt on some of the stats i am trying to create, but not all.

Why does this happen and how do I fix it? Please help

This is one of my stats query just for information:

if not exists (select * from sys.stats where name = N'_WA_Sys_00000034_452AF57A' and object_id = object_id(N'[dbo].[PromotionBenefit]'))
CREATE STATISTICS [_WA_Sys_00000034_452AF57A] ON [dbo].[PromotionBenefit]([DeductPointValue]) WITH STATS_STREAM = 0x010000000100000000000000000000008A2F619B000000001004000000000000D0030000000000006A0300006A0000000900120000000000000000000000000007000000A731CE00C6A60000F63C000000000000172C0000000000000000803F3694573D0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000110000001100000001000000190000000000104100D87346000000000000104100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001100000000000000000000000000000064020000000000006C020000000000008800000000000000A400000000000000C000000000000000DC00000000000000F800000000000000140100000000000030010000000000004C0100000000000068010000000000008401000000000000A001000000000000BC01000000000000D801000000000000F40100000000000010020000000000002C0200000000000048020000000000001000190057147246000000000000803F0100000000000000000400001000190022290441000000000000803F01010000000000000004000010001900BD8B7A40000000000000803F010200000000000000040000100019000000803F000000000000803F0103000000000000000400001000190091640D40000000000000803F0105000000000000000400001000190091640D40000000000000803F0106000000000000000400001000190091640D40000000000000803F010A000000000000000400001000190091640D40000000000000803F011400000000000000040000100019000000803F9C9018429C9018420128000000000000000400001000190091640D40000000000000803F013C00000000000000040000100019000000803F000000000000803F015000000000000000040000100019002C27AD40000000000000803F016400000000000000040000100019000000803F000000000000803F014A01000000000000040000100019000000803F000000000000803F019001000000000000040000100019000000803F000000000000803F01E803000000000000040000100019000000803F9C9018429C90184201C409000000000000040000100019000000803F000000000000803F018813000000000000040000F63C000000000000
GO

EDIT: So basically, I am doing data archiving on some tables in my database. Only one server is in use. Let's say I have tableA and I want to archive it . The step would be : 1. Create a new table with same columns as tableA , let's call it tableA_New. Then transfer records i want from original table to the new table. Then drop original table and rename the new table to original table name,i.e tableA.

After that, I would add back those constraints,triggers,indexes,statistics associated with table A back into it. That's where some stats I tried to add, it would throw me the error, only happens with some stats i am trying to create.

4
  • What is the error message you are receiving? And what version of SQL Server are you using? I'd also like to bring to your attention that supplying the STATS_STREAM option is not supported. The documentation states STATS_STREAM: Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
    – John K. N.
    Nov 9, 2017 at 7:01
  • I am using SSMS 2017 . Error message: The provided statistics stream is corrupt Nov 9, 2017 at 7:07
  • I imagine you want to clone the statistics from prod db to test db, so maybe you have 2 servers and their versions are different? Please update your question with the info from where you are taking this statistics
    – sepupic
    Nov 9, 2017 at 7:32
  • See my edited post Nov 9, 2017 at 7:44

2 Answers 2

0

This is possibly due to the fact that the STATS_STREAM value that you are implementing is faulty. This can have multiple reasons, none of which seem to documented outside of Microsoft.

Official Documentation

The only useful reference to STATS_STREAM comes from the official Microsoft Documentation CREATE STATISTICS (Transact-SQL) in which Microsoft states:

STATS_STREAM =stats_stream

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

That says quite a lot about this parameter.

We (Microsoft) use it, but you (end-user) shouldn't.

(non-official) use case for STATS_STREAM

There is a case that has been blogged about by Thomas Kejser in which he uses "faked" STATS_STREAMs to populate a "smaller" table with values that could possibly be produced in the future.

He goes on to explain a possible situation in which you would want to implement "wrong" statistics in order to deceive the query analyzer to use "fabricated" statistics to produce a better execution plan for values that have only just been inserted and are thus not normally updated fast enough:

There is another approach that does not rely on the optimizer making guesses: You can intentionally deceive the optimizer, lie about the distribution. Recall that we want the “desired DW plan”. Alas, as with all telling of lies, this exposes you to a lot of risk.

Reference: The Ascending Key Problem In Fact Tables –Part Two: Stat Job!

Situation at hand

However, your situation does not look like you want to deceive the query optimizer, but to just recreate the system generated statistics on your migrated table.

Now because you have modified a large amount of data, by deleting the records you no longer require, you are possibly invalidating the STATS_STREAM value that you retrieved from the original table.

Solutions

Option 1

Instead of trying to fill the system generated statistics with "fake" values that don't seem to work, why not just create new statistics for the columns that have had automatic statistics created for them?

You would just take your individual statements and instead of using the system generated name, replace it with a user defined name:

CREATE STATISTICS [PromotionBenefit_DeductPointValue] ON [dbo].[PromotionBenefit]([DeductPointValue])

This has the advantage that the statistics are created correctly and do not have to rely on fake values.

You can determine the columns of the system generated statistics by running the following statement:

SELECT s.name AS statistics_name
      ,c.name AS column_name  
      ,sc.stats_column_id  
FROM sys.stats AS s  
INNER JOIN sys.stats_columns AS sc   
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
INNER JOIN sys.columns AS c   
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id  
WHERE s.object_id = OBJECT_ID('dbo.PromotionBenefit')
AND s.name LIKE '_WA_Sys_%'
ORDER BY 1,3

This will give you a list of all system generated statistics for your table and the columns in the statistics.

Option 2

Just insert the data and let the database engine created new system generated statistics as soon as queries are performed against the table.

5
  • What are the main purpose of stats? Nov 10, 2017 at 2:24
  • Or should I leave out those stats that could not be created? Nov 10, 2017 at 2:24
  • Statistics (MSDN) explained by Microsoft. Bluntly put, statistics are a means for the query optimizer to determine which index to use to retrieve the requested data as fastest as possible.They are an estimated map of the distribution of the data in the (clustered-) index or column.
    – John K. N.
    Nov 10, 2017 at 6:41
  • What about those that could not be created? Should i leave them out? Nov 10, 2017 at 6:45
  • The _WA_Sys_.... statistics are (in most cases) system generated statistics. If the query optimizer determines that a statistic on a index or on a column is required and missing, then it will automatically create the missing statistics for you (if the AUTO_UPDATE_STATISTICS option is on; see link). You can leave them out and check to see if the system creates them again after you perform a query against the table. In testing I had a _WA_Sys_.... column statistic after one single query on a table with only 5 records and 1 clustered index.
    – John K. N.
    Nov 10, 2017 at 6:52
0

I am doing data archiving on some tables in my database.
...

Then transfer records i want from original table to the new table.

...

After that, I would add back those constraints,triggers,indexes,statistics associated with table A back into it. That's where some stats I tried to add, it would throw me the error, only happens with some stats i am trying to create.

All this is fine except for transfering old statistics.

Statistics are used to build optimal execution plan, they reflect data distribution in your columns.

When you'll query your new table, all the missing statistics will be recreated unless you disabled AUTO_CREATE_STATISTICS (the option is ON by default).

If you set AUTO_CREATE_STATISTICS to OFF and want to recreate all the statistics that existed on the old table you should just recreate them, all that you need for this is to know on what columns old statistics were created.

This way you'll get fresh statistics on your new table.

The undocumented option that you was trying to use serve another purpose: it's used with update statistics to recreate the same statistics in another environment. Here "the same" means not to use the same column to create statistics, but set another, not actual statistics, statistics taken from another table with another data distribution, on your table.

I mean if your original table had 1000 rows and half of them had col1 = 0 and another 500 rows had col1 = 1, you could create the same table on another server, put there only one row, and transfer data distribution as 500 rows having 0 + 5000 rows having 1. And this is not what you want. You want real data distribution on your new table, so just create the statistics on col1 that will reflect what is now in this table without using WITH STATS_STREAM.

13
  • Should i just recreate the ones that throw the error? What about those stats that were successfully created? Nov 10, 2017 at 2:26
  • If you want to have reasonable plans that are made based on real statistics that reflect your new tables content, you should update statistics already created. As I understand, you don't copy all the rows from original tables, i.e. you have less rows that your statistics reflect, this means you'll have overestimation of these tables that can lead to inappropriate scans, wrong join types, exaggerated memory grants.
    – sepupic
    Nov 10, 2017 at 6:08
  • So what should I do now? Leave it as it is for those successful stats created? Nov 10, 2017 at 6:19
  • I already answered you: >>> you should update statistics already created<<<. learn.microsoft.com/en-us/sql/t-sql/statements/… (see example A)
    – sepupic
    Nov 10, 2017 at 6:22
  • I already did that but say I have 3 updated stats in the table and the rest are meant for creating stats, what could be done about those meant to be created?? Nov 10, 2017 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.