Finding blocking/locking queries in MS SQL (mssql)

Question

Using sys.dm_os_wait_stats I have identified what I believe is a locking problem

  wait type    waittime  pct     running ptc
  LCK_M_RS_S   2238.54   22.14   22.14
  LCK_M_S      1980.59   19.59   41.73

Is there a way I can find the top blocking/locking queries? I've tried querying sys.dm_db_index_operational_stats without much luck.

Answer 1

You may find this query useful:

SELECT * 
FROM sys.dm_exec_requests
WHERE DB_NAME(database_id) = 'YourDBName' 
AND blocking_session_id <> 0