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
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
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.