0

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?

2 Answers 2

4

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.

3
  • 5
    IIRC, the view also does not include free space (pages not reserved).
    – Dan Guzman
    Oct 18, 2021 at 18:01
  • @DanGuzman: You mean Page 1, PFS page, does not include all the pages in the data file?
    – xhr489
    Oct 18, 2021 at 21:39
  • 2
    @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.
    – Dan Guzman
    Oct 18, 2021 at 21:52
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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