How to track down lock escalation

Hi,
using MSSQL 2000 SP3 on an Win2003 Server. My DB has 60 highly concurrent
users. Occasionlly I see heavy lock escalation (performance monitor) which
leads to blocking situations. Althoug I tried to track down the blocking
statements, I was not able to figure out which statements lead to the
escalation.
Any idea, what I can trie to identify the statements causing the locks?
TIA
Bernd
Hi,
I'd try to determine which functions or actions of the application are leading to the lock escalation. You can use SP_LOCK to locate the blocking locks, and hence the tables invloved. You might also want to use DBCC INPUTBUFFER on the SpId which is doing
the blocking, as this might give you clues as to what action the user is performing when the problem occurs. It depends on your application as to how useful this will be.
Once you have the action, examining the query plans from performing the actions with Query Analyiser should give you the problem statment. If you are getting a lock escalating to be a table lock, then you will be looking for Table Scans or Clustered Inde
x Scans.
There is also Profiler, which has a Lock:Escalation event, but once you start adding in all the event to collect all the SQL Statements, Profiler can start dropping event, especially when it's getting busy, which is just the bit you want. But if you can g
et the system to yourself and know the problem action, there shouldn't be any problem.