How to Find Blocked Deadlocks in SQL?

SQL Query

SELECT
    r.blocking_session_id AS BlockingSessionID,
    DB_NAME(r.database_id) AS DatabaseName,
    --OBJECT_NAME(object_id, r.database_id) AS BlockedObjectName,
    --OBJECT_SCHEMA_NAME(object_id, r.database_id) AS BlockedObjectSchema,
    r.database_id AS BlockedObjectName,
    r.database_id AS BlockedObjectSchema,
    st.text AS BlockedSQLText,
    r.blocking_session_id AS BlockedBySessionID,
    r.command AS BlockingCommand,
    CASE WHEN r.transaction_id IS NULL THEN 'Not In Transaction' ELSE 'In Transaction' END AS BlockingInTransaction,
    es.login_name AS BlockedUser,
    er.blocking_session_id AS BlockingSessionID,
    es.host_name AS BlockingHostName,
    es.program_name AS BlockingProgram,
    er.start_time AS BlockingStartTime
FROM
    sys.dm_exec_requests AS r
JOIN
    sys.dm_exec_sessions AS es ON r.session_id = es.session_id
JOIN
    sys.dm_exec_connections AS ec ON es.session_id = ec.session_id
JOIN
    sys.dm_exec_requests AS er ON ec.most_recent_session_id = er.session_id
CROSS APPLY
    sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE
    r.session_id != @@SPID
    AND r.blocking_session_id != 0
ORDER BY
    r.blocking_session_id;

Let’s discuss this table definition and how this table will help us to make this query.

sys.dm_exec_requestspan

sys.dm_exec_requests is not a table in the traditional sense; rather, it's a Dynamic Management View (DMV) provided by Microsoft SQL Server.

It serves as a valuable tool for database administrators and developers to monitor and manage the currently executing requests or sessions on the SQL Server instance.
This dynamic management view is particularly useful for monitoring and troubleshooting database performance, identifying long-running queries, detecting blocking issues, and understanding resource utilization on the server. It provides valuable insights into the current activity happening on the SQL Server instance, allowing database administrators and developers to optimize and tune database operations effectively.

Overall, sys.dm_exec_requests is a critical tool for real-time performance monitoring and troubleshooting in SQL Server environments. It allows database administrators and developers to gain visibility into the activity happening on the server and take proactive measures to optimize performance and maintain stability.

sys.dm_exec_sessions

sys.dm_exec_sessions is another Dynamic Management View (DMV)provided by Microsoft SQL Server.

It contains information about all active sessions on the SQL Server instance, including user sessions, system sessions, and internal background tasks.
The intention of sys.dm_exec_sessions is to provide a comprehensive view of the current sessions connected to the SQL Server instance, along with various attributes and properties associated with each session. Here's how this DMV can be helpful.

Overall, sys. dm_exec_sessions serves as a valuable tool for monitoring and managing the sessions connected to a SQL Server instance. By querying this DMV, administrators can gain insights into session activity, resource utilization, blocking scenarios, and transactional behavior, allowing them to optimize server performance and troubleshoot issues effectively.

sys.dm_exec_connections

sys.dm_exec_connections is a Dynamic Management View (DMV) provided by Microsoft SQL Server.

It contains information about the current connections established to the SQL Server instance, including details about client connections, protocols, and network addresses.

The intention of sys. dm_exec_connections is to provide administrators and developers with insights into the active connections to the SQL Server instance.
Overall, sys.dm_exec_connections serves as a valuable tool for monitoring and managing client connections to an SQL Server instance. By querying this DMV, administrators can gain insights into connection properties, network protocols, resource utilization, and session associations, allowing them to troubleshoot connectivity issues and optimize server performance effectively.

sys.dm_exec_sql_text

sys.dm_exec_sql_text is not a table but rather a Dynamic Management Function (DMF) provided by Microsoft SQL Server.

It is used to retrieve the text of the SQL statements that are currently being executed or that have been executed recently. This function is particularly useful for monitoring and troubleshooting purposes.

Overall, sys.dm_exec_sql_text is a valuable tool for monitoring and troubleshooting SQL Server instances. It provides insights into the SQL statements being executed, allowing administrators to diagnose performance issues, optimize queries, and ensure the security and stability of the database environment.

Attention

Dynamic management views (DMVs) and dynamic management functions (DMFs) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

Deadlock Monitor

Dynamic Management View/Function	Description	Usage in Finding Deadlocks
sys.dm_exec_requests	Provides information about each request that is currently executing or waiting for execution in SQL Server.	Can be used to identify blocking and deadlock scenarios by analyzing the blocking_session_id column to find the sessions involved in the deadlock chain.
sys.dm_exec_sessions	Returns one row per authenticated session on SQL Server. It includes information such as session ID, login name, hostname, and program name.	Useful for obtaining details about the sessions involved in the deadlock, such as login name and hostname, to identify the users or applications causing the deadlock.
sys.dm_exec_connections	Provides information about the connections established to SQL Server, including details such as session ID and client IP address.	Helpful for identifying the client connections associated with the sessions involved in the deadlock, aiding in troubleshooting, and identifying the source of the deadlock.
sys.dm_exec_sql_text	Returns the text of the SQL statements that are currently being executed or have been executed recently. It takes an SQL handle as input.	Can be used to retrieve the SQL text of the queries involved in the deadlock chain, enabling administrators to analyze the queries causing the deadlock and take appropriate actions to resolve it.