1 thought on “How Unused Indexes Hurt: Lock Escalation”

  1. Cool demo. I always thought 5,000 exclusive locks was the threshold at which lock escalation happens, but it looks like that is not always the case. Apparently, not all locks count towards that threshold though, at least according to Kendra’s post at https://sqlworkbooks.com/2017/04/which-locks-count-toward-lock-escalation/.

    But what I thought interesting was the locks that showed up after you added the fourth index. At 4:35 in the video, we see seven page exclusive locks show up. Since row locks don’t get escalated to page locks, I guess SQL decided that some index or indexes needed a page lock for the update.

Leave a Reply

Your email address will not be published. Required fields are marked *