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.
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.
To obtain information about locks in the SQL Server Database Engine, use the sys. dm_tran_locks dynamic management view.
You may find this query useful:
SELECT * FROM sys.dm_exec_requests WHERE DB_NAME(database_id) = 'YourDBName' AND blocking_session_id <> 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With