Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
When you create a statistics only copy of the database it shows a binary STATS_STREAM I've never looked into whether this is something findable in the file itself. –  Martin Smith Oct 18 '12 at 12:19
2  
Stats are created by an internal-only aggregate function (StatMan) that outputs a blob (ironically, that name is highlighted as a function in an SSMS query window). Logically, stats are associated with an index or a set of table columns, so I would start by examining the internal metadata tables looking for a binary or varbinary column that will lead to the blob. This should be viewable using DBCC PAGE, but probably not any other way because it's all internal. –  Jon Seigel Oct 18 '12 at 13:21
1  
@ivanmp I edited your question for clarity since many of the more novice DBA's won't know what a BP or a QO is. –  Max Vernon Oct 18 '12 at 13:29
2  
Used to be in sysindexes.statblob but since 2005 that returns NULL and the location is completely undocumented, only retrievable (that I know of) through DBCC SHOW_STATISTICS(o, i) WITH STATS_STREAM;. –  Aaron Bertrand Oct 18 '12 at 13:33
1  
Found the index stats -- they're in sys.sysidxstats -- it looks like there's a LOB pointer in that table. I'm not sure where the column stats are yet; they could be in that table as well as there's a type column. –  Jon Seigel Oct 18 '12 at 15:55

1 Answer 1

up vote 19 down vote accepted

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:

Partial results of DBCC Page

(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.

share|improve this answer
2  
We have a winner ladies and gentlemen. I tip my hat to you sir. –  Zane Oct 18 '12 at 16:09
    
Hahaha, congrats and thanks, sir! Don't worry, I'm not doing anything I shouldn't (AKA "stupid"). It's just for personal growth. I got really interested in it once I realized I couldn't find anything about this anywhere. =) –  ivanmp Oct 18 '12 at 16:11
    
About Joe Chang's article, I found it while I was looking for the answer to this. I had already started reading it. Thanks again. :) –  ivanmp Oct 18 '12 at 16:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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