aviadavi (2/27/2013)
I see. well, I'm running it with read uncommitted isolation level. as i don't care few updates will not be performed exactly as i plan.
Read uncommitted (and nolock) only apply to selects, not to updates or any other data change. Also, bad idea.
See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
as for the execution plan, i see many index seeks and one index scan on a non-clustered index.
how can i know what index is missing?
should I look on the predicate? defined values? object? output list?
Index scan = read of the entire index, that's where your lock escalation is coming from. First tune the query as has been recommended in this thread, then see if you still are getting the scans.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability