Index fragmentation problem after disabling page-level locking for an Index

Question

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?

• user9516827: There is a varbinary(max) column which is LOB ,but it is not part of index.

Answer 1

The answer to your query is buried in some documentation:

Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP> 1 and ALLOW_PAGE_LOCKS = OFF options.

The reasons for this are explained in detail in the following MSDN blog post:

How It Works: Online Index Rebuild – Can Cause Increased Fragmentation

Some options to resolve the problem in your situation, where page locks are disabled for the index, are:

• rebuilding offline
• rebuilding at MAXDOP 1