Post reply

LOCK_ESCALATION = AUTO

  • June 8, 2021 at 10:13 am

    #3893325

    Hello, I have a table partitioned and the table has an index partitioned.

    When I set LOCK_ESCALATION = AUTO, does the feature work with the additional index?

    Thanks for all.

     

  • June 9, 2021 at 11:10 am

    #3893866

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • June 9, 2021 at 5:36 pm

    #3894028

    The documentation says:

    AUTO

    This option allows SQL Server Database Engine to select the lock escalation granularity that's appropriate for the table schema.

    If the table is partitioned, lock escalation will be allowed to the heap or B-tree (HoBT) granularity. In other words, escalation will be allowed to the partition level. After the lock is escalated to the HoBT level, the lock will not be escalated later to TABLE granularity.

    If the table isn't partitioned, the lock escalation is done to the TABLE granularity.

    SOURCE - https://docs.microsoft.com/en-us/sql/relational-databases/tables/table-properties-ssms?view=sql-server-ver15

    My understanding of the above is that since an index is just another B-tree, it would follow the same lock escalation policy as the table would; in your case to the partition level.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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