Index maintenance and ALLOW_PAGE_LOCKS

  • DataAnalyst011

    SSCarpal Tunnel

    Points: 4035

    Hi All,

    I'm working on an issue related to reorg jobs failing because page level locking is not enabled for several of the indexes. It looks like these jobs were created using the SSMS wizard, and I'm looking at implementing Ola's solution.

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    ...I would probably use something like this:
    EXECUTE dbo.IndexOptimize
    @databases = 'USER_DATABASES',@databases = 'USER_DATABASES',
    @FragmentationLow = NULL,@FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 5,@FragmentationLevel1 = 5,
    @FragmentationLevel2 = 30,@FragmentationLevel2 = 30,
    @UpdateStatistics = 'ALL',

    However, that has me wondering if I will run into the same issue if the allow_page_locks isn't on. Does anyone have any thoughts or guidance on this?

  • Super Cat

    SSCertifiable

    Points: 7314

    You will probably have the same problem. Restore the DB to a DBA safe area and test. But I would imagine you need to turn it on for it to work.
    I had this issue once years ago and I think I just turn it on and that was the end of that.

  • DataAnalyst011

    SSCarpal Tunnel

    Points: 4035

    I think you are probably right. I've been getting around it with scripts that turn page locking on before the job and then off again. Its an app we purchased, and the release notes indicated they were running into deadlocks around that table. So I'm guess the fix was just to disable it altogether. I'll play around in a sandbox.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply