Post reply

Allow_Page_Locks

  • June 18, 2008 at 2:51 am

    #189269

    I have noticed that when a table index has Allow_Page_Locks = OFF (i.d Disabled), it will cause Reorganizing of indexes to fail.

    So I set all table indexes to Allow_Page_Locks = ON, which made the Reorganizing of indexes possible.

    But now we are experiencing frequent deadlocks and poor performance and I am wondering if it might be due to the change I made to these indexes?

    Can anyone confirm this for me? Or at least just help me out with the effect this setting (ON of OFF) has on data.

    Thanks,

    M

  • June 18, 2008 at 4:21 am

    #830288

    Bad code and poor indexing cause deadlocks. Someone in the past probably switched the allow page locks off as a 'quick fix'. My guess is that your poor performance is from blocking (check sys.dm_exec_sessions and sys.dm_exec_requests to confirm that)

    In general, SQL should be allowed to pick the lock type and granularity it wants and not be forced.

    Regarding the deadlocks, switch trace flag 1222 on so that you will get the deadlock graph written into the error log. That should give you a good idea of the source of the deadlock. Fixing the bad code is the best way to solve the deadlocks. Locking hints just hide the problem.

    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

  • June 18, 2008 at 7:56 am

    #830442

    In addition to Gila's post you should check for missing indexes that may help reduce the number of scans.


    * Noel

  • June 19, 2008 at 9:15 am

    #831181

    Here is the Bible on deadlock troubleshooting: http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx

    It is certainly conceivable that page lock escalation is involved in your problem. Perhaps you can set all indexes back to no page locks and then as part of your index maintenance operation alter them all to set page locking on, do the maintenance you desire, then set page locking back off? Pretty straight-forward work around.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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