Tricking SQL Server Statistics by undocumented feature "STATS_STREAM"

Do you know that you can store your statistics into a script and restore them later even on another server?

Does not seem to be useful. Usually you need your stats as fresh as possible.
However, there might be some cases when you already know what values have to be defined for your statistics and you want to preserve them.

Think about it as about another tool in your SQL tool box.

Let's start.

Before running the script press Ctrl+M to capture actual execution plan.

USE TestDB GO CREATE TABLE tbl_Test_Stats(ID INT IDENTITY(0,1) PRIMARY KEY, i INT, v CHAR(100)); GO CREATE INDEX ix_tbl_Test_Stats ON tbl_Test_Stats (i); GO INSERT INTO tbl_Test_Stats(i) VALUES(1); GO INSERT INTO tbl_Test_Stats(i) SELECT i FROM tbl_Test_Stats GO 10 GO INSERT INTO tbl_Test_Stats(i) VALUES(2); GO DBCC FREEPROCCACHE; GO SET STATISTICS IO ON GO SELECT * FROM tbl_Test_Stats WHERE i = 1 OPTION (RECOMPILE); GO SELECT * FROM tbl_Test_Stats WHERE i = 2 OPTION (RECOMPILE); GO SET STATISTICS IO OFF GO

As a result you should have following:

SELECT for i = 1 did a Full Scan with 17 reads and for i=2 did a seek with only 4 reads:

Now we can take a look at statistical histogram and capture STATS_STREAM:

DBCC SHOW_STATISTICS ('tbl_Test_Stats', 'ix_tbl_Test_Stats'>') WITH HISTOGRAM; GO DBCC SHOW_STATISTICS ('tbl_Test_Stats', 'ix_tbl_Test_Stats') WITH STATS_STREAM; GO

You should get something like this (do not forget to store value STATS_STREAM column in your editor):

Now will do update of the test table to reverse all values:

UPDATE tbl_Test_Stats SET i = CASE i WHEN 1 THEN 2 ELSE 1END; GO SET STATISTICS IO ON GO SELECT * FROM tbl_Test_Stats WHERE i = 1 OPTION (RECOMPILE); GO SELECT * FROM tbl_Test_Stats WHERE i = 2 OPTION (RECOMPILE); GO SET STATISTICS IO OFF GO

As a result you have to get this:

Now SELECT for i = 1 did a Seek with 5 reads and for i=2 did Full Scan with 17 reads:

Now will do the trick (just in case it fails replace your STATS_STREAM value):

UPDATE STATISTICS tbl_Test_Stats ix_tbl_Test_Stats WITH STATS_STREAM = 0x010000000200000000000000000000002CEDD0E4000000000202000000000000AA01000000000000380203F83800000004000A000000000000000000FFFFFFFF380303F83800000004000A0000000000000000000200000007000000C746150106A5000001040000000000000104000000000000000000000000003F10C07F3A0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000020000000200000014000000000000410020804400000000000080400000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000110000000000000000000000000000003E000000000000004600000000000000100000000000000027000000000000001000140000008044000000000000803F01000000040000100014000000803F000000000000803F020000000400000104000000000000; GO SET STATISTICS IO ON GO SELECT * FROM tbl_Test_Stats WHERE i = 1 OPTION (RECOMPILE); GO SELECT * FROM tbl_Test_Stats WHERE i = 2 OPTION (RECOMPILE); GO SET STATISTICS IO OFF GO DROP TABLE tbl_Test_Stats GO

And here are the results for the same data set:

It did Full Scan for i = 1 did a  with 17 reads!
And for i=2 did a seek with 2053 reads!!!:

Unreal statistics made SQL Server behave not in the best way possible.
However,  it is exactly what we've expected.

Just think for a moment, if we can trick SQL Server by directing it in a wrong way, we can direct it in the right way when statistical situation is not so obvious.

Just do not forget that STATS_STREAM option is not documented and not supported by Microsoft. In case it will not work as expected you can't complain, but there is a bright side, the option is still available in SQL Server 2016. That means we have long way to go.