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

Illustrator

Answer 1

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.

Evite

Thanks for quick turnaround, it is helpful. – Illustrator

A longer description of this routine can be seen at a SQL Authority blog post. Complete with warnings/cautions – Joselow

its nice but would be better If we could replace RequestingText/BlockingTest with information from DBCC INPUTBUFFER – Acetous

Answer 2

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

Marnie

Answer 3

The simplest method is by using the Activity Monitor query within Microsoft’s SQL Server Management Studio (SSMS). To access this query from SSMS: first open up the main window; then click ‘Activity Monitor’ under ‘Tools’; then use either the ‘Processes/Sessions’ tab or specifically select ‘Blocking Processes” from the drop down menu at top left of the monitor window. This will show all currently running processes and their associated session ID's, as well as any transactions they might be involved with such as those that are being blocked by other threads.

You can also check for blocking using a few T-SQL scripts designed explicitly to check locking behavior on working systems. One such script is called SP_WHO2 this simple system-stored procedure displays lock information about active user connections and associated process IDs against all databases running on an instance of SQL server. --Cheers Mike B

Herlindaherm