Press "Enter" to skip to content

Specifying Multiple Indexes in a Table Hint

Michael J. Swart is not satisfied with just one index:

My team wondered if this could be used as to help with a concurrency problem. We recently considered using it to resolve a particular deadlock but we had little success.

It’s useful to think that SQL Server takes locks on index rows instead of table rows. And so the idea we had was that perhaps taking key locks on multiple indexes can help control the order that locks are taken. But after some effort, it didn’t work at avoiding deadlocks. For me, I’ve had better luck using the simpler sp_getapplock.

I now would be curious what the maximum number of such index hints would be so we could apply Swart’s 10% Rule. Though I suppose, no matter the number, this would be a degenerate case of the rule, always leading you down to “one or zero, and stop messing with my database!”