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.
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%.