Recently
we have found that our index defragmentation
job is failing on a production server, due to REORGANIZE failure of one of our index. SQL Server was
unable to REORGANIZE this index because mistakenly we have an index with the ALLOW_PAGE_LOCKS options set to OFF
What
is Page Lock Option?
According
to BOL, If ALLOW_PAGE_LOCKS option is set to ON, it means Page locks are
allowed when accessing the index. The Database Engine determines when page
locks are used.
If ALLOW_PAGE_LOCKS option is set to off following query will return an error.
ALTER INDEX IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode
ON Person.Address REORGANIZE
The index "IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode" (partition 1) on table "Address" cannot be reorganized because page level locking is disabled.
You can correct it by simply
updating ALLOW_PAGE_LOCKS option to ON with the help of following query
ALTER INDEX
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode ON Person.Address
SET (ALLOW_PAGE_LOCKS = ON);
To make sure to avoid this problem in futur for all datbases on a instance, Amna Asif has suggested following simple but efficient script to detect indexes with ALLOW_PAGE_LOCKS option set to OFF and automatically fix this problem for all of your databases on an instance.
/***********************************
Script By: Amna
AsifPurpose : To fix ALLOW_PAGE_LOCKS option on
all indexes of all databases on a particular instance
***********************************/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
DECLARE @DBName NVARCHAR(255)
DECLARE
@IndexCount intDECLARE @UpdateIndexQuery Varchar(500)
--- Table variable to hold intermediate result set
DECLARE @IndexsInfo TABLE
(
RowNo int identity(1,1),
DatabaseName varchar(100),
TableName varchar(100),
IndexName varchar(100)
)
--- Cursor to work on each changeable index of each db on an instance
DECLARE DatabaseList CURSOR
FOR
SELECT Name
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND is_read_only = 0
ORDER BY name
OPEN DatabaseList
FETCH NEXT FROM DatabaseList INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @IndexsInfo (DatabaseName,TableName,IndexName)
EXEC( ' SELECT '''+@DBName+''' AS DatabaseName,TABL.NAME AS TableName,indx.NAME AS IndexName
FROM '+@DBName+'.SYS.INDEXES indx
LEFT OUTER JOIN '+@DBName+'.SYS.TABLES TABL ON indx.[OBJECT_ID]=TABL.[OBJECT_ID]
WHERE ALLOW_PAGE_LOCKS = 0 -- where page lock option is not selected
AND indx.name NOT LIKE ''QUEUE%'' ' -- we need only user defined indices
)
FETCH NEXT FROM DatabaseList INTO @DBName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList
-----Update allow_page_locks option for those indexes where we need
SET @IndexCount=(SELECT MAX(RowNo) FROM @IndexsInfo )
WHILE @IndexCount >0
BEGIN
SET @UpdateIndexQuery=( SELECT ' ALTER INDEX '+ IndexsInfo.IndexName +' ON ['+
IndexsInfo.DatabaseName+'].dbo.['+IndexsInfo.TableName+']
SET (
ALLOW_PAGE_LOCKS = ON
) ; '
FROM @IndexsInfo AS IndexsInfo
WHERE IndexsInfo.RowNo=@IndexCount)
EXEC(@UpdateIndexQuery)
SET @IndexCount=@IndexCount-1
END
Hi! great Stuff.
ReplyDeleteIt works, but only if Databases are named with a single word. Most of SharePoint 2010 DBs are multi worded (eg [Application de service de profil utilisateur_ProfileDb_{Guid}).
So, you need to change:
EXEC( ' SELECT '''+@DBName+''' AS DatabaseName,TABL.NAME AS TableName,indx.NAME AS IndexName
FROM ['+@DBName+'].SYS.INDEXES indx
LEFT OUTER JOIN ['+@DBName+'].SYS.TABLES TABL ON indx.[OBJECT_ID]=TABL.[OBJECT_ID]
WHERE ALLOW_PAGE_LOCKS = 0
AND indx.name NOT LIKE ''QUEUE%'' ' -- where page lock option is not selected -- we need only user defined indices
)
Thanks.
Thanx Amna Asif
ReplyDeleteThe query works great... :)
Thanks, would prefer if the script returned the SQL to run, so I could see how many indexes would be fixed instead of just running it and getting x rows affected, e.g.:
ReplyDeletePRINT @UpdateIndexQuery
--EXEC(@UpdateIndexQuery)