Post reply

High Amount of Page Level Locking Disabled

  • December 9, 2015 at 8:04 am

    #399443

    We recently migrated a database from SQL 2012 Standard Edition to SQL 2014 Enterprise Edition. Since the migration, we have been getting a high level of "...cannot be reorganized because page level locking is disabled" error message. Page level locks are disabled on the old database. What I am trying to figure out is why we are getting the message now.

    Thanks,

    MC

  • December 9, 2015 at 9:10 am

    #1844953

    Probably because the new server has an index maintenance job that does ALTER INDEX REORGANIZE, whereas the old server didn't. REORGANIZE needs page locks to be enabled

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 9, 2015 at 9:25 am

    #1844963

    Both servers are running the same maintenance jobs. And yes, it is failing on the ALTER INDEX REORGANIZE. Based on fragmentation level, the index is reorganized or rebuild. It is possible then that the index is less fragmented on the new server because we are seeing more index reorganization on the new server instead of rebuilds.

    New server configuration:

    SQL 2014 Enterprise Edition

    Memory: 64GB

    Processors: 4

    Old Server

    SQL 2012 Standard Edition

    Memory: 32GB

    Processor: 4

  • December 9, 2015 at 9:29 pm

    #1845093

    To fix this issue enable page level locking on index

    ALTER INDEX <index name> ON <table name> SET (ALLOW_PAGE_LOCKS = ON)

    GO

  • December 9, 2015 at 11:37 pm

    #1845101

    mceventphoto (12/9/2015)

    Page level locks are disabled on the old database.

    I'm just curious... why did you need to do that? To what benefit?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 10, 2015 at 10:59 am

    #1845298

    Do what Jeff?

  • December 10, 2015 at 11:51 am

    #1845313

    mceventphoto (12/10/2015)

    Do what Jeff?

    Why did you need to disable Page Level locking on the old server to begin with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 10, 2015 at 12:07 pm

    #1845316

    Disable page locks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 11, 2015 at 7:48 am

    #1845494

    Hi Jeff,

    It is a vendor supplied database and it came with page level locking disabled on some of the indexes.

Viewing 9 posts - 1 through 8 (of 8 total)

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