7

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?

  • There is a varbinary(max) column which is LOB ,but it is not part of index. – user9516827 Jul 12 at 19:33
13

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.