How to check blocking queries in SQL Server

Question

I have one warehouse server which got data/sync from legacy system 24/7, I noticed some of my reports/sql jobs performance is uncertain and most of the time I heard from DBA team that my query is blocking to other sync process.

From DBA team I came to know command i.e. EXEC SP_WHO2 by which I can identify spid of query which cause blocking by looking into column BlkBy.

Please suggest me how I can avoid blocking and other ways to check blocking in SQL Server

Answer 1

This is a very comprehensive guide. Some basic guidelines though:

• Avoid SELECT ... INTO #temp pattern and instead create a table first and use INSERT INTO #Temp SELECT...
• Use WITH (NOLOCK) on queries where you can tolerate dirty reads
• Ensure proper indexes exist
• Use sargable predicates in your WHERE clauses
• Talk to your DBA about potentially enabling READ_COMMITTED_SNAPSHOT isolation level

Answer 2

Apart from Sp_Who2 you can use following query to identify blocking in you SQL.

SELECT
db.name DBName,
tl.request_session_id,
wt.blocking_session_id,
OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
tl.resource_type,
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
tl.request_mode
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

Also can check detail of particular SPID by using following command.

DBCC INPUTBUFFER(56) — Will give you the Event Info.

KILL 56 -- Will kill the session of this id.

Answer 3

There is an another query which can be useful. This query will provide you important details about blocking session id,time(from when the blocking session is running),executing query,user account who is executing this blocking session. The query is very useful when you are dealing with a production environment with many users running their transactions and you are supposed to find out blocking session and user and take necessary action.

SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
ORDER BY
[Begin Time] ASC;
GO