June 8, 2021 at 10:13 am
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
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
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.
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.
July 1, 2023 at 4:28 am
This was removed by the editor as SPAM
July 1, 2023 at 4:30 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply