Index reorg problem

Recently, we upgraded an application to a new version. The following night, our standard maintenance plan failed. The job output log file gave the following error:

Failed:(-1073548784) Executing the query “ALTER INDEX […] ON […” failed with the following error: “The index “…” (partition 1) on table “…” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

I’ve omitted the object names, because they are irrelevant here. Not all error messages tell you exactly what the problem is, but this one does: The index … cannot be reorganized because page level locking is disabled.

A little background information
Multiple users may require access to the same database object at the same time. This causes concurrency issues: when you’re reading a table, you don’t want somebody else to change the contents of the table while you’re reading, causing you (for example) to read the same record twice. SQL Server solves this problem with locking. There is a hierarchy of objects within the database, with the row at the lowest level and the database at the highest level. There are also different types of locks, that may or may not be compatible with each other, such as SHARED, INTENT SHARED, EXCLUSIVE. To give an example: any user that logs in to a database, will cause a SHARED lock to be placed on the database. This doesn’t block anybody else from logging in, since a SHARED lock is compatible with another shared lock. It does, however, block somebody else who tries to perform an action that requires an EXCLUSIVE lock at the database level; EXCLUSIVE locks are not compatible with other types of lock.
You can easily verify this. Open a query window and connect it to any database. Then, right click the database in the Object Explorer pane and try to rename the database. This will result in an error:

Database lock error

Locking at the page level is more complicated to demonstrate. If you want to know more about that, I will have to refer you to the excellent book by Kalen Delany, Microsoft SQL Server 2008 Internals.
For every query, SQL will automatically decide what locks need to be placed at what levels in the hierarchy. Usually, it is best to trust SQL to make this decision for you; however, there are some situations that you might want to change the default locking behavior.
So SQL decides when it is useful to lock pages of an index. When you create an index, you can specify that you don’t want to allow row locks on the index. This is the T-SQL statement that will achieve that:

Like I said, this is not something that I would recommend that you do without thorough testing; you can read more about these options in this MSDN article.
Back to the problem at hand. The error with our maintenance was caused because several indexes where made that didn’t allow page locks. One of these indexes was in the error message; I found the others with the following T-SQL statement:

Interestingly enough, three of the indexes were called “queue_secondary_index”. These were not part of the application, but instead, indexes on internal tables. Maybe a good topic for another blog post, but not related to the problem at hand, since the reorganization task skips these internal indexes.

The solution
There were three solutions to the problem:
1. Change the allow_page_locks setting back to the default permanently;
2. Change the setting back temporarily, reorganize and then change it once again;
3. Skip these indexes altogether.
According to the software manufacturer, the setting was turned off deliberately, because their tests showed a performance benefit. They advised against turning ALLOW_PAGE_LOCKS back on, even temporarily. So we choose option three: we changed our index reorg script to ignore these indexes. Problem solved.

This entry was posted in Indexes. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *