Post reply

Row and page locks on indexes

  • August 18, 2011 at 10:08 am

    #242318

    Suppose, I have tables with 6mil to 15 mil records each.

    And these tables are only for select statements. There are no updates (except a batch update in the middle of the night when there are no users on the system).

    These tables have indexes, based on query needs. All these indexes have the "Use row lock when accessing index." and Use page locks when accessing index." options checked and enabled.

    Now, since these tables only allow select (read) statements, would it be ok to turn off the row and page index locks? Since no rows are being updated or inserted, why is there a need for locks?

  • August 18, 2011 at 11:15 am

    #1370266

    for one example, if ALLOW_PAGE_LOCKS=OFF, you are likely to encounter errors performing index maintenance like rebuilds or reorgs.

  • August 18, 2011 at 11:31 am

    #1370273

    If you turn both off, SQL will lock at a table level every time. You may know that there will be no updates but, unless the database or filegroup is readonly, SQL does not.

    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

  • August 18, 2011 at 12:04 pm

    #1370296

    Well, I am just going to leave it as is. I was curious if it would improve anything.

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

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