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:
- Ensure no open, uncommitted transactions exist that have modified the table/index in question.
- Run
EXEC sys.sp_spaceused with the @updateusage = 'updateusage' option, or DBCC UPDATEUSAGE.
- Run
CHECKPOINT to ensure all modifications have been written to disk.
- Check the output of
sys.dm_db_index_physical_stats and sys.allocation_units; the counts should now be the same.
Best Answer
The maintenance plan must be attempting an ALTER INDEX REORGANIZE, which is an online operation. To remove fragmentation (pages not in order), pages must be locked and moved, which is not possible if page locks have been disabled. The only way to defragment without page locks is to lock the entire partition, which is not possible for REORGANIZE as its online only.
You need to grasp what a record and page are to evaluate the impact of disallowing a particular lock type. If you are unfamiliar with SQL Server storage internals, start with Anatomy of a Record and Anatomy of a Page. Put very simply:
If you were to alter the permitted lock types:
There are two scenarios I'm aware of where it can be beneficial to disallow a lock type. Doesn't mean there aren't others, hopefully someone else will step in with examples.
A frequently accessed lookup table, that changes infrequently - By disabling both page and row level locks, all readers will take a shared table lock. This is faster/cheaper rather than the usual intent-shared on the table, followed by intent-shared on a page and finally a shared lock on a specific row or rows.
Preventing a specific deadlock scenario - If you encounter deadlocks caused by concurrent processes acquiring locks that are frequently on the same page, disallowing row locks results in page locks being taken instead. Only one process can then access the page at a time, the other must wait.
The first example is micro-optimisation and unlikely to yield measurable benefit on a typical system. The second will solve that particular deadlock scenario but may introduce unexpected side effects e.g. killing concurrency in a different section of code. Difficult to assess the impact fully, approach with caution!
The default is for both to be enabled and this should not be changed without good cause.