I have an Index which has page level locking disabled and now I have index fragmentation issues for that index.
I was aware that I won't be able to
Reorganize the index, but I believed that I would be able to
rebuild the index.
Now I think rebuild also is not working. I am using the default fill factor of 100. My database is huge so I don't want the table/db size to get increased by giving a fill factor of 80% or less. By not working, I mean that the Avg Fragmentation remains the same after running Ola's script the previous night.
A little background on why page level locking is disabled.
I was getting lots of transaction
deadlocks on this index in that particular table. This table was kind of a fact table where
insert, update and delete was happening every moment. And there are some
foreign key relationship to a few other tables with
CASCADE deletes. So, I was getting lots of locks/deadlocks and after disabling the page level lock, I was able to get rid of all those deadlocks.
I started using Ola Hallengren's script for
Index maintenance and I applied Index Rebuild for all those indexes for which Index Reorganize is not working.
But I noticed that Index Rebuild also is not working.
Now, I see a AvgPageFragmentaiton of 95.9413 and Page count of 1196826 for the index, which is not good.
I am using Ola Hall's script as shown below.
EXECUTE dbo.IndexOptimize @Databases = 'DB_NAME', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE', @FragmentationHigh = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @SortInTempdb = 'Y', @MaxDOP = 0, @WaitAtLowPriorityMaxDuration = 0, @WaitAtLowPriorityAbortAfterWait= 'NONE', @Indexes = 'DB_NAME.[dbo].TB1,DB_NAME.[dbo].TB2'
My question is how to reduce the fragmentation in an index if the page level lock is disabled for that index?