Page counts shown by
sys.allocation_units can vary significantly when a variety of DDL modifications are made to the object concerned.
From the Microsoft Docs page on
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
return select *
As you can see, it uses the internal table
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,
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:
- Ensure no open, uncommitted transactions exist that have modified the table/index in question.
EXEC sys.sp_spaceused with the
@updateusage = 'updateusage' option, or
CHECKPOINT to ensure all modifications have been written to disk.
- Check the output of
sys.allocation_units; the counts should now be the same.