Where is the statistics stored

Question

I'm looking for a way to check on what file-group a statistics is stored in SQL Server.

It seems like you can update statistics on a table even if it is located on a read-only filegroup.

Could it be that all statistics are stored on the primary file-group?

Ola Hallengren

http://ola.hallengren.com

Answer 1

Hi Ola,

As per this article, information regarding the statistics in stored in the sysindexes table. This table always resides on the primary file group. Here is a snip.

Microsoft SQL Server 2008 collects statistics about individual columns (single-column statistics) or sets of columns (multicolumn statistics). Statistics are used by the query optimizer to estimate the selectivity of expressions, and thus the size of intermediate and final query results. Good statistics allow the optimizer to accurately assess the cost of different query plans and then choose a high-quality plan. All information about a single statistics object is stored in several columns of a single row in the sysindexes table, and in a statistics binary large object (statblob) kept in an internal-only table. In addition, information about statistics can be found in the new metadata views sys.stats and sys.indexes.

---

Pradeep Adiga
Blog: sqldbadiaries.com

Answer 2

That articles is sligtly incorrect. Not that it matters much, but just to be more precise:

Sysindexes is no more (it is a view). We can do "sp_helptext 'sys.stats'" and we see that the resl system table is named sysidxstats. The important thing, though, is that the table resides on the primary filegroup.

---

Tibor Karaszi, SQL Server MVP | web | blog