This query is causing lock escalation ( 6000 to 8000 locks) Any idea on how to tune it to minimze the locks ?

Sam 2015-04-03 19:54:57

SQLkiwi 2015-04-05 15:56:51
Assuming you're running the query under the default read committed isolation level, you may be seeing lock escalation because the execution engine needs to hold shared locks to the end of the statement, rather than releasing them after each row – as it normally would at that isolation level. There are several possible scenarios, described in the links below:

  1. Read Committed and Bookmark Lookup
  2. Read Committed and Large Objects

It's impossible to guess which precise scenario you are encountering based on the anonymized plan session submitted, but at least you now where to look and what to check.

Sam 2015-04-06 17:38:16
Thanks for the answer

I believe the 3rd scenario which is a bookmark lookup is causing the lock escalation, since it's a select statement on a relativily small database with an execution plan in which one the operator is a keylook up with 65% cost. Would minimizing the key lookup cost to less than 10% help minimze the locks ? The other option is adding a nolock hint ( I am ware of the risk)

SQLkiwi 2015-04-08 01:50:54
No, it has nothing to do with estimated cost percentages. As the links in my answer say, under some circumstances, the engine will hold shared locks on rows to the end of the statement. If enough rows are accessed this way with a row-level lock, lock escalation will be attempted. See if you can sensibly eliminate the Lookup through indexing changes first.