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