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.
Additional info:
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?
