Sql-server – Page count differences between sys.dm_db_index_physical_stats and sys.allocation_units

database-internalssql serversql-server-2008-r2

In doing research for index maintenance, I found some significant differences between the page counts reported by sys.dm_db_index_physical_stats and sys.allocation_units in some of the clustered indexes on one of our servers.

For one table, allocation_units reports 6706 data_pages and 15203 used_pages, while dm_db_index_physical_stats reports 4079 leaf pages and 15 non-leaf pages.

I don't know when the last time any maintenance was done on the indexes. It is possible that they have not been reorganized or rebuilt since they were initially loaded to the server. Rebuilding the index does sync the stats.

I'd be interested in knowing what caused it to get out of sync and if the extra space reported by allocation_units is actually reserved on the disk.

The query I used the following query for allocation_units. It returned 1 row of IN_ROW_DATA.

SELECT *
FROM
    sys.indexes idx
    INNER JOIN sys.partitions part
        ON idx.object_id = part.object_id
            AND idx.index_id = part.index_id
    INNER JOIN sys.allocation_units alloc
        ON part.partition_id = alloc.container_id
WHERE
    idx.type = 1
    AND idx.object_id = OBJECT_ID('dbo.Table')

Best Answer

Page counts shown by sys.dm_db_index_physical_stats and sys.allocation_units can vary significantly when a variety of DDL modifications are made to the object concerned.

From the Microsoft Docs page on sys.sp_spaceused

There are some situations, for example, after an index is dropped, when the space information for the table may not be current. [...] Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sp_spaceused immediately after dropping or truncating a large object may not reflect the actual disk space available.

sys.dm_db_index_physical_stats has the following definition (in SQL Server 2016):

create function sys.dm_db_index_physical_stats
(
    @DatabaseId         SMALLINT        = 0,
    @ObjectId           INT             = 0,
    @IndexId            INT             = -1,
    @PartitionNumber    INT             = 0,
    @Mode               nvarchar(20)    = NULL
)
returns table
as
    return select *
        from OpenRowset
        (   TABLE
            INDEXANALYSIS,
            @DatabaseId,
            @ObjectId,
            @IndexId,
            @PartitionNumber,
            @Mode
        )

As you can see, it uses the internal table INDEXANALYSIS, whereas sys.allocation_units uses a different source, ALUCOUNT, as shown in its definition:

CREATE VIEW sys.allocation_units AS
    SELECT au.auid AS allocation_unit_id,
        au.type,
        ip.name AS type_desc,
        au.ownerid AS container_id,
        convert(int, au.fgid) AS data_space_id,
        isnull(ct.reserved_pages, au.pcreserved) AS total_pages,
        isnull(ct.used_pages, au.pcused) AS used_pages,
        isnull(ct.data_pages, au.pcdata) AS data_pages
    FROM sys.sysallocunits au OUTER APPLY OpenRowset(TABLE ALUCOUNT, au.ownerid, au.type, 0, 0) ct
    LEFT JOIN sys.syspalvalues ip ON ip.class = 'AUTY' AND ip.value = au.type

These sources vary in their level of consistency as you can see in the output of your queries. If you require absolute certainty about the count of pages used by each, you should do the following:

  1. Ensure no open, uncommitted transactions exist that have modified the table/index in question.
  2. Run EXEC sys.sp_spaceused with the @updateusage = 'updateusage' option, or DBCC UPDATEUSAGE.
  3. Run CHECKPOINT to ensure all modifications have been written to disk.
  4. Check the output of sys.dm_db_index_physical_stats and sys.allocation_units; the counts should now be the same.
Related Question