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?
varbinary(max)
column which is LOB ,but it is not part of index.