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 talk about this table definition and how it will assist us 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 is a useful tool for database administrators and developers to monitor and manage the current requests or sessions on the SQL Server instance.
  • This dynamic management view is particularly useful for monitoring and debugging database performance, recognizing long-running queries, discovering blocking issues, and determining server resource use. It provides significant insights into the present activity on the SQL Server instance, helping database administrators and developers to manage and modify database processes efficiently.

Overall, sys.dm_exec_requests is an essential tool for real-time performance monitoring and troubleshooting in SQL Server. It enables database administrators and developers to monitor server activity and take proactive steps to improve performance and stability.

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

It contains information about all active sessions on the SQL Server instance, including user sessions, system sessions, and internal background processes.
The goal of sys.dm_exec_sessions is to offer a full picture of the current sessions connected to the SQL Server instance, as well as the many attributes and features associated with each. Here's how the DMV can be useful.

Overall, sys.dm_exec_sessions is a useful tool for monitoring and managing SQL Server sessions. Querying this DMV provides admins with insights into session activity, resource use, blocking circumstances, and transactional behavior, allowing them to efficiently optimize server performance and handle issues.

sys.dm_exec_connections
Microsoft SQL Server has a Dynamic Management View (DMV) called sys.dm_exec_connections.

It holds information on the current connections to the SQL Server instance, such as client connections, protocols, and network address.

The purpose of sys. dm_exec_connections is to give administrators and developers with information about the active connections to SQL Server instances.
Overall, sys.dm_exec_connections is an effective tool for monitoring and controlling client connections to SQL Server instances. Querying this DMV provides administrators with insights on connection attributes, network protocols, resource use, and session associations, allowing them to effectively diagnose connectivity issues and enhance server performance.

sys.dm_exec_sql_text
sys.dm_exec_sql_text is a Dynamic Management Function (DMF) that Microsoft SQL Server provides. It retrieves the text of SQL statements that are presently being executed or have been executed recently. This feature is especially useful for monitoring and troubleshooting purposes.

Overall, sys.dm_exec_sql_text is an effective monitoring and troubleshooting tool for SQL Server instances. It gives information about the SQL statements that are being executed, allowing administrators to identify performance issues, optimize queries, and assure the database's security and reliability.

Attention

Dynamic management views (DMVs) and dynamic management functions (DMFs) provide server state information that can be used to monitor a server instance's health, identify problems, and optimize 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.

HostForLIFEASP.NET SQL Server 2022 Hosting