This code snippet utilizes SQL queries to identify and analyze blocking situations within a database management system (DBMS). It retrieves information about sessions, blocking sessions, and their execution details.

DECLARE @app_session_id VARCHAR(8000)
--@app_session_id=session_id

WITH cteBL (session_id, blocking_these) AS
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s
CROSS APPLY  (SELECT isnull(convert(varchar(6), er.session_id),'') + ', ' 
        FROM sys.dm_exec_requests as er
        WHERE er.blocking_session_id = isnull(s.session_id ,0)
        AND er.blocking_session_id <> 0
        FOR XML PATH('') ) AS x (blocking_these)
)

SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, *
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;  

Explanation of the DB Code:

This code snippet utilizes SQL queries to identify and analyze blocking situations within a database management system (DBMS). It retrieves information about sessions, blocking sessions, and their execution details.

Here’s a breakdown of the code:

1. Variable declaration:

• @app_session_id: Declares a variable to potentially hold a specific session ID, but isn’t used in the provided code.

2. Common Table Expression (CTE):

• cteBL: This CTE defines a temporary named result set named cteBL containing two columns:
  • session_id: Holds the session ID from sys.dm_exec_sessions table.
  • blocking_these: This column builds a comma-separated list of all sessions currently being blocked by the particular session_id.
  • The logic uses a CROSS APPLY with FOR XML PATH('') to efficiently concatenate session IDs from sys.dm_exec_requests where:
    • blocking_session_id matches the current session_id from sys.dm_exec_sessions.
    • blocking_session_id is not zero (meaning it’s actually blocking).

3. Main SELECT statement:

• This statement retrieves and combines information from various system views:
  • sys.dm_exec_sessions (s): Provides details about active sessions.
  • sys.dm_exec_requests (r): Contains information about running requests within sessions.
  • cteBL (bl): The previously defined CTE holding blocking information.
  • sys.dm_exec_sql_text (t): Retrieves the actual SQL statement text associated with the request (using OUTER APPLY).
  • sys.dm_exec_input_buffer (ib): Gets the input buffer information for the session (using OUTER APPLY).
• Selection criteria:
  • blocking_these is not null: Selects sessions that are currently blocking other sessions.
  • OR r.blocking_session_id > 0: Selects sessions that are themselves being blocked by another session.
• Returned columns:
  • s.session_id: The session ID.
  • blocked_by: The session ID blocking the current session (if any).
  • bl.blocking_these: The comma-separated list of sessions blocked by the current session.
  • batch_text: The actual SQL statement text being executed (if available).
  • input_buffer: The input buffer details for the session.
  • *: All other columns from the joined tables.
• Ordering:
  • The result set is ordered based on three criteria:
    1. Descending order of len(bl.blocking_these): This prioritizes displaying sessions blocking the most other sessions first.
    2. Descending order of r.blocking_session_id: Within the same blocking level, sessions being blocked by higher-numbered sessions appear first.
    3. Ascending order of r.session_id: Finally, sessions are displayed in ascending order by their own session ID.

Overall, this code helps identify potential bottlenecks and diagnose blocking issues within the database by providing information about:

• Which sessions are currently blocking others.
• Which sessions are themselves being blocked by others.
• The actual SQL statements and input details associated with these sessions.

This information can be valuable for database administrators to troubleshoot performance issues and optimize query execution.

Few things to know about this code:

DBMS Compatibility:

• This code primarily relies on system views and functions (sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_exec_sql_text, sys.dm_exec_input_buffer) that are specific to Microsoft SQL Server.
• Equivalent views or functions might exist in other database management systems, but the syntax and details would vary considerably. You’d need to rewrite the code and adapt it to the specific DBMS you want to use.

Required Privileges:

• Yes, running this code typically requires special privileges. Accessing the dynamic management views (DMVs) like sys.dm_exec_sessions, sys.dm_exec_requests, etc., usually requires elevated permissions. The exact level of privilege may depend on the specific DBMS:
  • Microsoft SQL Server: Often the VIEW SERVER STATE permission is sufficient. Sometimes specific database-level permissions might be needed.
  • Other DBMSs: Similar but differently named system or administration-level privileges would likely be required.

Important Considerations:

• Before attempting to run this type of diagnostic code in a production environment, always consult the documentation for your specific DBMS to understand the equivalent views/functions and the necessary permissions.
• Running diagnostic queries like this on a production database should be done with caution, as they can potentially have a performance impact, especially on a heavily loaded system.

Let’s explore the advantages of identifying and analyzing blocking situations within a Database Management System (DBMS), along with best practices and necessary precautions.

Advantages of Identifying and Analyzing Blocking Situations in DBMS:

1. Enhanced Performance:
   • By identifying and resolving blocking scenarios promptly, system performance improves.
   • Reduced waiting time for transactions leads to faster data retrieval and processing.
2. Data Consistency:
   • Analyzing blocking helps maintain data consistency.
   • Ensures that concurrent transactions do not interfere with each other, preventing data inconsistencies.
3. Resource Utilization:
   • Efficiently managing locks and resolving blocking situations optimizes resource utilization.
   • Avoids unnecessary resource contention and ensures smooth operation.
4. Improved User Experience:
   • Reduced wait times due to blocked transactions enhance the end-user experience.
   • Faster response times lead to higher user satisfaction.

Best Practices and Care Required:

1. Keep Transactions Short:
   • Longer transactions increase the likelihood of blocking and deadlocks.
   • Design transactions to be as short as possible, committing or rolling back changes promptly.
2. Acquire Locks in a Consistent Order:
   • Define a consistent order for acquiring locks to prevent deadlocks.
   • Follow a predictable sequence when accessing shared resources.
3. Use Appropriate Lock Types:
   • Choose the right type of lock (shared or exclusive) based on the transaction requirements.
   • Shared locks allow read access, while exclusive locks allow both read and write access.
4. Implement Deadlock Detection and Resolution Mechanisms:
   • Detect and handle deadlocks proactively.
   • Set up deadlock detection timeouts and implement strategies to break circular dependencies.

Explore more :

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/understand-resolve-blocking

1blog.sqlauthority.com

2learn.microsoft.com

3geeksforgeeks.org

4geeksforgeeks.org

5geeksforgeeks.org

Remember, proactive monitoring, understanding the database schema, and following best practices are essential for maintaining a healthy DBMS and minimizing the impact of blocking issues.

Dr, Amit Singh

Dr. Amit is a seasoned IT leader with over two decades of international IT experience. He is a published researcher in Generative AI and chatbot architectures (Springer & IJAET), with a PhD in Generative AI focused on human-like intelligent systems.

Amit believes there is vast potential for authentic expression within the tech industry. He enjoys sharing knowledge and coding, with interests spanning cutting-edge technologies, leadership, Agile Project Management etc. He previously earned top honors in his MCA.