Executing the query "" failed with the following error: "The index ""
(partition 1) on table "" cannot be reorganized because page level
locking is disabled."
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.
What is the difference between the two locking schemes, and what are
their real-world (in production) consequences?
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:
- rows = records
- rows are stored in pages of 8kb
If you were to alter the permitted lock types:
- Disable page locks = Row and table locks only
- Disable row locks = Page and table locks only
- Disable both = Table locks only
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.
Is the table too small?
Fragmentation figures are meaningless for small tables that span up only a few extents
This is captured by @minPageCount in the script. The default is 8: meaning it will be skipped if one extent in size.
Best Answer
By default, [allow_page_locks] is set to 1, so, someone must have changed it in the past to avoid that level of locking.
I would test this in a non-prod environment prior to changing. I would think the outcome would be unexpected locking.
You could use this script to identify those indexes and then change it to modify those. But, I would think there was probably a good reason for the change in the first place.