Sql server script to find out blocking related information

Sql server script to find out blocking related information

  

Using  following query one can  find  out blocking related information such as

1)      which session is being blocked by which session ?

2)      User who is blocking ?

3)      Sql text for bloked and blocking session id’s

Scripts :-

      SELECT  CLIENT_NET_ADDRESS AS IP,
                  EC.SESSION_ID AS blocking_id ,
                  SESS.LOGIN_NAME AS [Who_is_blocking] ,
                  BLOCKINGSQL.TEXT AS [Blocking_statement] ,
                  WAITS.WAIT_TYPE AS [lock_type] ,
                  BLOCKED.SESSION_ID AS [victim_id] ,
                  BLOCKEDSQL.TEXT AS [what_is_blocked] ,
                  DB_NAME(BLOCKED.DATABASE_ID) AS [database]
      FROM SYS.DM_EXEC_CONNECTIONS AS EC
           
            INNER JOIN SYS.DM_EXEC_REQUESTS AS BLOCKED
                  ON EC.SESSION_ID = BLOCKED.BLOCKING_SESSION_ID
                   
            INNER JOIN SYS.DM_OS_WAITING_TASKS AS WAITS 
                  ON BLOCKED.SESSION_ID = WAITS.SESSION_ID
                 
            RIGHT OUTER JOIN SYS.DM_EXEC_SESSIONS SESS 
                  ON EC.SESSION_ID = SESS.SESSION_ID
                 
            CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS BLOCKINGSQL
           
            CROSS APPLY SYS.DM_EXEC_SQL_TEXT(BLOCKED.SQL_HANDLE) AS BLOCKEDSQL
      ORDER BY BLOCKING_ID