Sum of pages in the data file does not equal the file size

Question

I am not trying to solve a specific problem, but just trying to understand.

I have read several places that the data file (let us just assume one mdf file) is split into 8 KB pages. But when I sum total_pages in sys.system_internals_allocation_units or sys.allocation_units the result does not add up the size of the data file and it is less. Is there anything else in the data file other than 8 KB pages?

Answer 1

There is a note in the documentation of sys.allocation_units (Transact-SQL). That is why it does not match.

Note that the value returned excludes internal index pages and allocation-management pages.

• Dan Guzman: IIRC, the view also does not include free space (pages not reserved).
• xhr489: @DanGuzman: You mean Page 1, PFS page, does not include all the pages in the data file?
• Dan Guzman: @xhr489, I mean pages may exist that are not allocated at all. These are free space and not included in the DMV. Create a new empty database and observe the free space is not counted.

Answer 2

As an analogy, you are saying:

The sum of file size for all my files on this disk doesn't equal the size of the disk.

No, you can have free space on the disk. The same goes here. An allocation unit corresponds to a file in the analogy and the database corresponds to the disk (partition).

To break it down, the allocation unit concept:

• A table can have one or more partitions.
• A table has the actual data (as a heap or as the clustered index).
• A table can have n non-clustered indexes.
• You can have three types of data/allocations in your tables:
  • "normal data" (int, varchar(30), datetime, etc)
  • LOB columns (varchar(max), varbinary(max), xml, etc)
  • Row overflow (two varchar(8000) populated fully doesn't fit one page, for example)

For each table, partition, data and index, and each type of data (as described immediate above), you get one allocation unit.