Sql-server – Index fragmentation problem after disabling page-level locking for an Index

fragmentationindex-maintenanceola-hallengrensql server

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?

Best Answer

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