Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Finding blocking/locking queries in MS SQL (mssql)

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.

like image 883
Carlo V. Dango Avatar asked Dec 06 '10 11:12

Carlo V. Dango


People also ask

How do I find blocked queries in SQL Server?

In SQL Server Management Studio (SSMS) Object Explorer, right-click the top-level server object, expand Reports, expand Standard Reports, and then select Activity - All Blocking Transactions. This report shows current transactions at the head of a blocking chain.

How can we check locks in SQL Server?

To obtain information about locks in the SQL Server Database Engine, use the sys. dm_tran_locks dynamic management view.


1 Answers

You may find this query useful:

SELECT *  FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = 'YourDBName'  AND blocking_session_id <> 0 
like image 82
Nomad Avatar answered Oct 23 '22 21:10

Nomad