Sql-server – Sql server – The provided statistics stream is corrupt

sql serverssms

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.

Best Answer

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.

Related Question