SQL SERVER Lookup Lock Information

Through the system's stored procedure sp_who or Sp_who2 can find all the lock information, but do not see which table, what statement

When using sp_who or Sp_who2 to find the lock information, there is a SPID information, you can use the following statement to find out the corresponding statement

DBCC InputBuffer (SPID)

Use this statement, not necessarily can find information, can find out, is the execution of the statement, also said this is to check the deadlock statement.

The following statement checks the number of deadlocks

Select Blockedfrom (SELECT * from sys.sysprocesses where  blocked>0) a where isn't exists (SELECT * FROM (SELECT * Fro M sys.sysprocesses where  blocked>0) b where a.blocked=spid)

　

Use the following statement to find out which tables are locked and how many times they are locked

Select TableName, COUNT (*) CNT from (select   request_session_id   spid,object_name (resource_associated_entity_ ID) tableName   from   sys.dm_tran_locks where resource_type= ' OBJECT ') AA group by Tablenameorder by CNT Desc

SQL SERVER Lookup Lock Information