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:
The index "PK_Product" on table "Product" 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.
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.
My recommendation for you is to enable both allow-page-lock and allow-row-lock in order to let SQL Server decide the best locking option for each transaction.
Is there any implications in turning this setting to ON or OFF?
As I see several indexes in our tables that are already set to OFF when I used your query above.... (Very useful query thanks for posting it)..so not sure which one should be turned ON (depends if its used in a scheduled reindex\reOrg jobs?) as I don't want to wait for an error to occur.. or lock a page that can be used at the same time of the maint plans...etc.
Thursday, March 15, 2018 - 8:18:27 AM - Ahmad Yaseen
Executed as user: CHEC\svc_vrsqlwc15_cluadm. ... (Message 0) Performing Index Maintenance on master... [SQLSTATE 01000] (Message 0) Performing Index Maintenance on model... [SQLSTATE 01000] (Message 0) ALTER INDEX [backupmediasetuuid] ON [msdb].[dbo].[backupmediaset] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0) ALTER INDEX [backupmediafamilyuuid] ON [msdb].[dbo].[backupmediafamily] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0) ALTER INDEX [nc1] ON [msdb].[dbo].[sysjobhistory] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [backupsetuuid] ON [msdb].[dbo].[backupset] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0) ALTER INDEX [restorehistorybackupset] ON [msdb].[dbo].[restorehistory] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [clust] ON [msdb].[dbo].[sysjobactivity] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [pk_MSdbms_map] ON [msdb].[dbo].[MSdbms_map] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [pk_MSdbms_datatype_mapping] ON [msdb].[dbo].[MSdbms_datatype_mapping] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [PK_syscollector_collection_items_internal] ON [msdb].[dbo].[syscollector_collection_items_internal] REBUILD [SQLSTATE 01000] (Message 0) Performing Index Maintenance on msdb... [SQLSTATE 01000] (Message 0) ALTER INDEX [PK_EventReceivers] ON [NW2010DB_Config1].[dbo].[EventReceivers] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [PK_ContextDataResources] ON [NW2010DB_Config1].[dbo].[ContextDataResources] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IDX_InstanceIDSequenceID] ON [NW2010DB_Config1].[dbo].[WorkflowProgress] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IDX_Intiator] ON [NW2010DB_Config1].[dbo].[WorkflowInstance] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IDX_WFInstanceID] ON [NW2010DB_Config1].[dbo].[WorkflowInstance] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0) ALTER INDEX [IDX_State] ON [NW2010DB_Config1].[dbo].[WorkflowInstance] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [PK_63F77821] ON [NW2010DB_Config1].[dbo].[UserProfiles] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [PK_Credential] ON [NW2010DB_Config1].[dbo].[WorkflowConstants] REORGANIZE [SQLSTATE 01000] (Message 0) Performing Index Maintenance on NW2010DB_Config1... [SQLSTATE 01000] (Message 0) ALTER INDEX [PK_HumanWorkflow] ON [NW2010DB_Projects].[dbo].[HumanWorkflow] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IDX_WorkflowProgressIDTaskType] ON [NW2010DB_Projects].[dbo].[HumanWorkflow] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IDX_HumanWorkflowID] ON [NW2010DB_Projects].[dbo].[HumanWorkflowApprovers] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IDX_HumanWorkflowApprovers_SPTaskID] ON [NW2010DB_Projects].[dbo].[HumanWorkflowApprovers] REORGANIZE [SQLSTATE 01000] (Message 0) Performing Index Maintenance on NW2010DB_Projects... [SQLSTATE 01000] (Message 0) Performing Index Maintenance on NW2010DB_Teams... [SQLSTATE 01000] (Message 0) Performing Index Maintenance on NW2010DB_Tools... [SQLSTATE 01000] (Message 0) ALTER INDEX [IX_MSSCrawlDeletedURL_AccessHash] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlDeletedURL] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0) ALTER INDEX [IX_MSSCrawlDeletedURL_DocID] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlDeletedURL] REBUILD WITH (ONLINE = ON) [SQLSTATE 01000] (Message 0) ALTER INDEX [PK_MSSCrawlHostList] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlHostList] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IX_MSSCrawlHostList_Name] ON [Search_Service_Application_CrawlStoreDB_e0e756da67bc46e1b4be078578a66838].[dbo].[MSSCrawlHostList] REORGANIZE [SQLSTATE 01000] (Message 0) ALTER INDEX [IX_MSSCrawlQueue_Cluster] ON [Search_Service_Application_CrawlStoreDB... The step failed.
How to analyze the root cause for this Index maintainance job failure?
Very nicewell summarized article. I had simmillar issue and maintenance jobs were failing because of page level locink was disabled. But what i did was very lengthy step i have to go to the error log to find exactly what happened and which index on which table then address one by one. Whereas here you provide us a short cut just running the TSQL statement will identify all the indexes and tables with page level locking disabled simillarly you can use the script to enable the page level locking. Very good article, simple and relevent to the DBAs.
Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.
