Eugene Davydov (4/23/2013)
I said that information: c# code calls in loop some simple procedures which perfoms only atomic insert/update/delete (by one row). There's no reason for escalation. More loop amount, more amount of locks in session (and - again - no escalation will trigger).
If they aren't running in a transaction, those locks will be released as soon as the insert/update/delete completes, so there won't be more and more locks. If they are in a transaction, then they're eligible for lock escalation as soon as you have a certain number of locks on the same table unless lock escalation has been disabled.
And because there're huge amount of fine-grained lock I see perfomance degradation in that session.
Lots of locks, by themselves should not cause performance degradation.
Are you seeing high waits? If so, what type?
Are you seeing high CPU time?
Are you seeing high signal wait times?
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