Press "Enter" to skip to content

Preventing Deadlocks on Key Lookups

Erik Darling talks about key lookups:

I go back and forth when it comes to Lookups.

On the one hand, I don’t think the optimizer uses them enough. There are times when hinting a nonclustered index, or re-writing a query to get it to use a nonclustered index can really help performance.

On the other hand, they can really exacerbate parameter sniffing problems, and can even lead to read queries blocking write queries. And quite often, they lead to people creating very wide indexes to make sure particular queries are covered.

Read on for one scenario around deadlocking due to key lookups.