Problem
One of the most important aspects of SQL Server database maintenance is maintaining indexes. Over time indexes can become fragmented, so to fix fragmentation indexes can either be rebuilt or reorganized and this is usually dependent upon the level of fragmentation. Index maintenance is usually a scheduled task and recently we ran into an issue where the job failed on one of the tables showing that the index can’t be reorganized due to page lock issues. What is this page lock issue and why does it prevent us from reorganizing the index?
Solution
Assume we have a table named Product that requires index reorganization. If you check the index maintenance job history, the below error will be shown explaining that the reorganizing process can’t complete due to page level lock issue. Here is the actual error message:
Review Index Settings on Table
Let’s query the problematic table’s indexes by querying the sys.indexes system table joined with the sys.objects table as follows:
Select Indx.name, Indx.type_desc, Indx.is_disabled, Indx.allow_page_locks, Indx .allow_row_locks from sys.indexes Indx left outer join sys.objects OBJ on OBJ.object_id=Indx.Object_id where OBJ.name ='Product'
You can see from the result below that the allow_page_locks and allow_row_locks options are disabled for that index. The specific error we saw above was related to page level locking.
What are Row Level and Page Level Locks
By default the allow-page-lock and allow-row-lock options are enabled, which allows SQL Server to choose between the three locking levels: row-level locks, page-level locks, and table-level locks. By disabling these options, you limit the type of locking that can occur on the object.
The purpose of these different locking mechanisms is to limit how much of the table/index is locked at any one time, therefore allowing concurrent access to different parts of the table/index.
So, it is better to enable both allow-page-lock and allow-row-lock in order to let SQL Server decide the best locking option for each transaction.
Enable Row Level and Page Level Locks
In order to resolve the index reorganization issue, we will enable the row and page level locking by altering the index as shown below:
USE MSSQLTipsDemo GO ALTER INDEX [PK_Product] ON [Production].[Product] SET ( ALLOW_PAGE_LOCKS = ON ) ALTER INDEX [PK_Product] ON [Production].[Product] SET ( ALLOW_ROW_LOCKS = ON )
We can also enable the row and page level locking using SSMS. If you right click on the Index and select Properties and go to the Options page you can change the values to True as follows:
If we check the index properties again using the T-SQL code above, we will see the following result:
Retry Index Maintenance After Change
If we run the index maintenance job again, it completes successfully as shown below:
Next Steps
- It is better to keep the row level and page level locking options enabled, unless there is a specific need to disable these options.
- Check out these SQL Server Locking and Blocking Tips
- Check out these SQL Server Database Maintenance Tips
- Check out these SQL Server Fragmentation Tips
Ahmad has a Bachelor’s Degree in Computer Engineering from the University of Jordan and five years of experience working as a SQL DBA, gaining valuable knowledge of database structures, practices, principles and theories. His experience also includes.NET development, working with database applications, scripting and creating SQL queries and views. His personal abilities include having very strong communication and interpersonal skills, the ability to prioritize and to make good sound decisions that benefit the company. He has experience in upgrading, configuring, securing, tuning and monitoring SQL Servers since SQL Server 2005. This includes SQL Server performance tuning, SQL Server resource governor management, SQL Server maintenance plans, SQL Server data collection (Reports) analyzing and SQL databases design, developing, indexing and query optimization. In addition, he is familiar with installing and configuring SSRS, SSIS and SSAS. When it comes to disaster recovery and high availability, he has a solid foundation in SQL backup and recovery scenarios, mirroring, replication, log shipping, SQL clustering and AlwaysOn technology.
- MSSQLTips Awards: Author Contender – 2016-2017 | Trendsetter (25+ tips) – 2016 | Rookie Contender – 2015
You can use these queries to generate the ALTER INDEX statements.
SELECT ‘ALTER INDEX [‘ + i.name + ‘] ON [‘ + s.name + ‘].[‘ + o.name + ‘] SET ( ALLOW_PAGE_LOCKS = ON )’
FROM sys.indexes i
inner join sys.objects o on o.object_id=i.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
WHERE i.allow_page_locks = 0
AND i.type_desc IN (‘CLUSTERED’,’NONCLUSTERED’)
AND o.type = ‘U’
SELECT ‘ALTER INDEX [‘ + i.name + ‘] ON [‘ + s.name + ‘].[‘ + o.name + ‘] SET ( ALLOW_ROW_LOCKS = ON )’
FROM sys.indexes i
inner join sys.objects o on o.object_id=i.object_id
inner join sys.schemas s on o.schema_id = s.schema_id
WHERE i.allow_row_locks = 0
AND i.type_desc IN (‘CLUSTERED’,’NONCLUSTERED’)
AND o.type = ‘U’
I am facing the same issue but table having around 2500+ indexes, in which only 26-page level lock is enabled , for rest indexes i need to enable.
so how to enable the page level lock for 2600 indexes , please help and suggest.