CODEWITHSUNDEEP
sqlt-sql
Losbear
Losbear

Reputation: 3315

View SQL Blocks/Locks?

I was wondering if there is there a tool or a way to view which statements cause a block/lock and which objects would be affected? I know about sp_who and sp_who2, but those only work while the system is running.

For example, I know running this:

UPDATE myTable SET col1 = 'something'

Will put a lock on "myTable", but there are more complex scenarios (like nested procs and triggers) that would be more difficult to identify.

I was hoping for tool like the "Actual Execution Plan" built-into SSMS, but other tools, queries will suffice

Thanks all

Upvotes: 0

Views: 431

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You can refer this blog and follow the different ways to identify the blocks:

  • sp_who2 System Stored Procedure
  • sys.dm_exec_requests DMV
  • Sys.dm_os_waiting_tasks
  • SQL Server Management Studio Activity Monitor
  • SQL Server Management Studio Reports
  • SQL Server Profiler

Also check: Different techniques to identify blocking in SQL Server

Upvotes: 2

dougajmcdonald
dougajmcdonald

Reputation: 20047

You can use SQL Activity Monitor (right click on the SQL instance in SSMS) to get a list the number of current locks etc and you can use the 'Task State' column of the 'Processes' panel in there to see currently running statements which will tell you if things are blocking.

These all focus on activities which are currently executing though, so I'm not sure this will be what you want.

Upvotes: 0

Related Questions