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 

HostForLIFEASP.NET SQL Server 2022 Hosting