Mostly commonly asked question related to sql server blocking – how to find blocking and blocked session (spid) in sql server? There are number of way to find blocking in sql server, you can use either system stored procedure (sp_who2), sql script, sql server activity monitor, sql server profiler and DMVs
Find blocking sessions using sp_who2
Simplest way to find out blocking and blocked spid in sql server is by executing system stored procedure : sp_who2 on target sql server. It will list down the all session on sql server.
After executing sp_who2 command, In output result set concentrate on “Blkby” column referred as Blocked by. If you see any spid listed under column “Blocked by” that means sql server is facing blocking issue.
Blocking make other queries to run slow now you know the culprit session….. now before killing the session Please make sure that it should not hurt production server. Talk to user who is running the query, to find out what query user is executing you can also use DBCC inputbuffer command.
It will give the text of sql query that is currently running by spid.
Query to find blocking
Alternate sql script will give the list of all blocking sessions
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;