Find blocking by T-SQL Queries
Blocking is an unavoidable and by-design characteristic of any relational database management system (RDBMS) with lock-based concurrency. As mentioned previously, in SQL Server, blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is small. When the owning session releases the lock, the second connection is then free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance. Source: https://docs.microsoft.com
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server. Its returns information about each request that is executing in SQL Server. Use any one script out of three.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Returns information about each request that is executing in SQL Server SELECT * FROM sys.dm_exec_requests GO -- Returns information about the request that is executing in SQL Server where session_id is greater than 50 SELECT * FROM sys.dm_exec_requests WHERE session_id > 50 GO -- Returns information about the request that is executing in SQL Server. -- SQL Handle: It is a hash of the SQL Text containing all of your formatting (casing, spaces, etc). SELECT * FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(sql_handle) GO |
Here is another script where the result will be below the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SELECT qs.Session_ID ,Blocking_Session_ID ,qs.STATUS ,Wait_Type ,Wait_Time ,Wait_Resource ,SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ( ( CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset ) / 2 ) + 1) AS statement_text ,GetDate() SnapshotDateTime , --Open_Transaction_Count, ss.PROGRAM_NAME ,ss.HOST_NAME ,ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
Kill blocking process
1) Use an activity monitor to kill the process
Right-click on the SQL instance name -> Activity monitor -> Expand the process section -> Find the relevant process id and right-click to kill the process.
2) Run the kill command to kill the process
|
1 |
KILL PROCESS_ID |
“Please let us know if there are any changes/modifications required. Feel free to comment below – We always appreciate hearing your tips.”
1 thought on “Find blocking by T-SQL Queries”