Finding blocking/locking queries in SQL Server

     To resolve blocking issue in sql server we have to find out which process is causing the issue, and then if possible kill the blocking process. There are many different ways in SQL Server to identify a blocks and blocking process that are as follows.

Activity Monitor – Right click on SQL server and open tool Activity Monitor, You can use Activity Monitor to view information about the current processes. You can find out and kill blocked process using Blocked By drop down.



sp_who2 – It is the System SP help to find out running process in sql server. Once we execute procedure, we can find out blocked process from BlkBY column. But using sp_who2 we can find out which session is causing the issue but we couldn’t get the information about which Query part is causing the block or other information




Find SQL block using query – Using below query we can find out the SQL blocks, which process is causing the block, from that in which query part taking time to execute everything we can find out. I think this query part is very help full to identify or rectifying SQL blocks. 

SELECT 
       s.session_id
       ,r.STATUS
       ,r.blocking_session_id AS 'blocked_by_SQL_Feathers'
,COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' +          QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' +
        QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS 'stored_proc'
       ,s.program_name
       ,s.last_request_end_time
       ,s.login_time
       ,s.login_name
       ,r.wait_type
       ,r.open_transaction_count
       ,r.command
    ,r.wait_resource
    ,CONVERT(VARCHAR, DATEADD(ms, r.wait_time, 0), 8) AS 'wait_time'
    ,r.cpu_time
    ,r.logical_reads
    ,r.reads
    ,r.writes
    ,CONVERT(VARCHAR, DATEADD(ms, r.total_elapsed_time, 0), 8) AS 'elapsed_time'
    ,CAST((
            '<?Running -->  ' + CHAR(13) + CHAR(13) + Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
                    (
                        CASE r.statement_end_offset
                            WHEN - 1
                                THEN Datalength(st.TEXT)
                            ELSE r.statement_end_offset
                            END - r.statement_start_offset
                        ) / 2
                    ) + 1) + CHAR(13) + CHAR(13) + '--?>'
            ) AS XML) AS 'query_text'
    --,qp.query_plan AS 'xml_plan'  -- uncomment (1) if you want to see plan
    ,s.host_name   
    ,s.host_process_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
--OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp --  (2) if you want to see plan
WHERE r.wait_type NOT LIKE 'SP_SERVER_DIAGNOSTICS%'
    OR r.session_id != @@SPID
ORDER BY
     s.session_id,
         r.cpu_time DESC