How Unused Indexes Hurt: Lock Escalation

Facts


Yesterday we looked at how unused indexes can muck up the buffer pool, because writes to them will bring pages in.

Today we’re going to look at how unused indexes add to locking problems.

Hold My Liquor




One 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 *