Deadlocks in Profiler

I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks were
generated by Loadrunner scripts (stress testing) simulating application
SQL via an ODBC DSN connection.

2 things are puzzling me in the SQL Profiler traces that I have logged

1) There are a large number of Lock:Timeout events but the 'lock
timeout' setting is the default 'wait forever' so I dont know what is
timing out.

2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using the
same ClientProcessId at the time of deadlock. What is the
ClientProcessId and is it relevant to the deadlock?

Thank you in advance for any replies.<Robert_Couldry@.linfox.com> wrote in message
news:1113887215.714232.311400@.f14g2000cwb.googlegr oups.com...
> I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks were
> generated by Loadrunner scripts (stress testing) simulating application
> SQL via an ODBC DSN connection.
> 2 things are puzzling me in the SQL Profiler traces that I have logged
> 1) There are a large number of Lock:Timeout events but the 'lock
> timeout' setting is the default 'wait forever' so I dont know what is
> timing out.

Lock timeouts are set by the client, so perhaps one particular connection or
application has set a specific value - you can use sp_lock to investigate
exactly which process is holding a lock and on which resource, and Erland
has a useful tool for investigating locking:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

> 2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using the
> same ClientProcessId at the time of deadlock. What is the
> ClientProcessId and is it relevant to the deadlock?

ClientProcessID is the operating system process ID (ie. the PID in Task
Manager) of the client application connecting to MSSQL. One application may
have multiple SPIDs - Query Analyzer has one for the main window, and
another for the Object Browser. So it seems that your application is somehow
blocking itself.

This KB article may help you, if you haven't already seen it (there are a
number of other articles about deadlock caused by known issues also):

http://support.microsoft.com/defaul...kb;en-us;832524

Simon