DeadLocks and Locking

[nikhilparchure]

We have been facing this production issue of locking which is hampering the performance of the system.

What is required is a query that will query the system tables
and would be able to give the locked tables and which process has been locking the same and by which users

Well I have a vague idea of the table involved but it would be helful if i get a refined query for process locking and processes, users locking the databases...

some tables ...
syslocks
sysprocesses
sysdatabases
sysobjects


I have been able to get a very raw query which need to be further refined
Help need guys

Select A.id 'Table ID' ,
B.Name 'TableName' ,
C.hostname 'HostName' ,
C.Program_name 'Program'
from master..syslocks A ,
sysobjects B ,
master..sysprocesses C
Where A.id = B.id
and A.spid = C.spid
and B.type = 'U'

I want the above query to be refined

Regards
Nikhil Parchure

 

[TomKane]

Maybe you could check this out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_6ofn.asp