Where are Statistics physically stored in SQL Server?

Question

Where are the Statistics used by the Query Optimizer physically stored inside a SQL Server database file and the Buffer Pool?

More specifically, is there a way to figure out the pages used by statistics using DMVs and/or DBCC?

I own both SQL Server 2008 Internals and SQL Server Internals and Troubleshooting books and none of them talks about the physical structure of the statistics; if they do I haven't be able to find this information.

Answer 1

Found them.

1. Create a table with a simple stats object.

   CREATE DATABASE splunge;
   GO
   USE splunge;
   GO
   CREATE TABLE dbo.foo(bar INT, munge INT);
   GO
   CREATE STATISTICS x ON dbo.foo(bar);
   CREATE STATISTICS y ON dbo.foo(munge);
   GO
   INSERT dbo.foo SELECT s1.[object_id], s2.[object_id]
     FROM sys.objects AS s1
     CROSS JOIN sys.objects AS s2;
   GO
   UPDATE STATISTICS dbo.foo;
   GO
   

2. Connect using the DAC (ADMIN:Server[\instance]).

3. Run the following queries:

   DBCC SHOW_STATISTICS('dbo.foo', 'x') WITH STATS_STREAM;
   DBCC SHOW_STATISTICS('dbo.foo', 'y') WITH STATS_STREAM;
   
   SELECT name, imageval 
     FROM sys.stats AS s
     INNER JOIN sys.sysobjvalues AS o
     ON s.object_id = o.objid
     AND s.stats_id = o.subobjid
   WHERE 
     s.object_id = OBJECT_ID('dbo.foo');
   

You will note that imageval for each stats object isn't the same as the stats blob, but it does contain the stats blob - it's just offset. On my system it yielded this for x (I've obviously truncated a fair bit of bits):

0x0100...bunch of chars...000007000000C4E1BE00EEA0...rest the same
                            0x07000000C4E1BE00EEA0...rest the same

And this for y:

0x0100...bunch of chars...430007000000C7E1BE00EEA0...rest the same
                            0x07000000C7E1BE00EEA0...rest the same

The same was true for index-based statistics.

You could probably do further validation of this with a series of queries using DBCC commands. First, find out the pages that are involved with the clustered index on sys.sysobjvalues (substitute your database name):

DBCC IND('splunge', 'sys.sysobjvalues', 1);

The result will list a bunch of pages, you're interested in the ones of PageType = 1. With a new database, you should be able to find this info on one of the pages with the highest PagePID values. E.g. on my system this was page 281, so then I looked closer at that page:

DBCC TRACEON(3604);

DECLARE @dbid INT = DB_ID();

DBCC PAGE(@dbid, 1, 281, 3);

DBCC TRACEOFF(3604);

Sure enough, I found the data in slot 17:

(On larger databases, you might have to do a lot more hunting and pecking, since there's no guarantee that even a new stats object will end up on a new(er) page.)

Go ahead and try this at home, but there is a reason you need to connect with the DAC for this. I'd be curious to know, of course, what you are going to do with this information that you couldn't do with DBCC SHOW_STATISTICS output.

Note that this of course does not try to decode the STATS_STREAM to provide histogram or other information, and I could not find any evidence that the tabular output of DBCC SHOW_STATISTICS ... WITH HISTOGRAM is stored anywhere in table format. Joe Chang has some information about decoding if that's what you're after. I don't think it's something you'd want to do in a query - just use DBCC.