Sql Server Query for Blocked Session and login names

The below query can be used to find out the login names and session id of the blocked users,the detailed explanation of blocking is beyond the scope of the post  and this post covers only to extract blocking information
...................................................................................................................................................

To replicate the blocking, i have opened a transaction record  for  an  update  by a user    and   another user is querying the same record  ,both users are using  the sql server default  isolation of read committed  and this causes the blocking,  as the second  user is waiting to acquire a shared lock on the same record

I have made the screen shots at the end of query  and highlighted   activity  monitor  screen shot  to show     the query and blocked session
-----------------------------------------------------------------------------------------------------

SELECT  distinct  case when r.session_id=s.session_id then s.nt_user_name end as BlockedLogin ,r.session_id as BlockedSessionId ,r.status ,
 case when r.blocking_session_id=st.session_id then st.nt_user_name end as BlockedByLogin, r.blocking_session_id as BlockedBySessionId,s.nt_user_name
    ,wait_type ,wait_time ,wait_resource
    ,transaction_id
FROM sys.dm_exec_requests r  inner join  sys.dm_exec_sessions s
on r.session_id=s.session_id inner join sys.dm_exec_sessions st
on r.blocking_session_id=st.session_id
  WHERE r.status = N'suspended';
GO

additionally to get the blocked query  text you can use the below query



SELECT  distinct  case when r.session_id=s.session_id then s.nt_user_name end as BlockedLogin ,r.session_id as BlockedSessionId ,r.status ,
 case when r.blocking_session_id=st.session_id then st.nt_user_name end as BlockedByLogin, r.blocking_session_id as BlockedBySessionId,s.nt_user_name
    ,wait_type ,wait_time ,wait_resource,qry.Text as BlockedQuery
    ,transaction_id
FROM sys.dm_exec_requests r  inner join  sys.dm_exec_sessions s
on r.session_id=s.session_id inner join sys.dm_exec_sessions st
on r.blocking_session_id=st.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)  Qry
  WHERE r.status = N'suspended';
GO

The result can be seen below

The Same can be observed on activity monitor as below

As you  can observe we can use combination of many tools in sql server diagnosis