Index fragmentation problem after disabling Page Level lock for an Index

by user9516827   Last Updated July 12, 2019 15:06 PM

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.

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.

Any suggestions/advice is helpful.

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'

Corrections : it is not 9.9413% but it is 95.9413%.



Related Questions


Fragmentation Level for Heaps

Updated March 17, 2019 10:06 AM


Table Fragmentation

Updated April 12, 2019 01:06 AM