One of the practices that need to be taken into account is identifying sessions that cause blocking. Blocking is a common occurrence in SQL Server and Azure SQL Database, where one session holds a lock on a specific resource and a second session attempts to acquire a conflicting lock type on the same resource. This leads to the second session’s request being blocked until the first session releases the lock.
Understanding Types of Locks
A session can acquire different types of locks depending on the operation. These include Shared (S) locks, Exclusive (E) locks, and Update (U) locks among others.
- Shared (S) locks allow multiple sessions to read (select) a resource under pessimistic concurrency control.
- Exclusive (E) locks prevent access to a resource by concurrent transactions.
- Update (U) locks are used for operations that can potentially modify data, so that multiple sessions don’t update the same resource simultaneously.
Tools for Identifying Blocking Sessions
There are multiple tools and techniques you can use to identify sessions that cause blocking.
- Activity Monitor: The Activity Monitor in SQL Server Management Studio offers an overview of system performance and user activity. You can examine the ‘Processes’ node to identify blocking sessions.
- Dynamic Management Views (DMVs): DMVs allow you to analyze performance and troubleshoot issues. For identifying blocking, you can use the
sys.dm_exec_requestsDMV which returns information about each request executed within SQL Server. - Extended events: Extended events are a lightweight, highly scalable and configurable data collection mechanism that allows you to collect as much or as little data as you need. A system_health session is a predefined event session that troubleshoots issues with SQL Server.
SELECT
r.session_id,
r.status,
r.command,
r.blocking_session_id
FROM
sys.dm_exec_requests r;
In this SQL query, the blocking_session_id shows the ID of the session causing blocking. A zero indicates that no blocking is taking place.
How to Handle Blocking Sessions
Once you have identified blocking issues, they need to be handled appropriately to prevent further performance bottlenecks. Here are some strategies:
- Change the Application Design: If a single application has several transactions causing blocks, redesigning the application to reduce the excessive lock times might be the solution.
- Optimize Queries: Poorly designed or inefficient queries can cause blocks. Improvement in the query performance might alleviate the blocking issues.
- Lock Timeout Setting: By setting a lock timeout value, sessions can be forced to stop waiting after a specified period of time.
- Use Row Level Versioning: Enabling Read Committed Snapshot Isolation (RCSI) or Snapshot Isolation (SI) can reduce blocking and deadlocks.
Identifying and handling blocking sessions is crucial for the performance and smooth operation of your SQL Server or Azure SQL Database. It’s a good practice to regularly monitor your system to detect potential blocks and take necessary actions to mitigate them.
Practice Test
True or False: The sys.dm_tran_locks dynamic management view will give the current blocking sessions.
- True
- False
Answer: True
Explanation: The sys.dm_tran_locks dynamic management view provides detailed information about current requests that are blocked.
Identify the ways to monitor blocking sessions in Azure SQL?
- A. Activity Monitor
- B. sys.dm_exec_requests
- C. sys.sp_who2
- D. All of the above
Answer: D. All of the above
Explanation: Activity Monitor, sys.dm_exec_requests and sys.sp_who2 can all be used to monitor blocking sessions in Azure SQL.
True or False: SQL Server Profiler can monitor blocking sessions in real-time.
- True
- False
Answer: True
Explanation: SQL Server Profiler is a tool that monitors SQL Server events in real-time, which includes blocking sessions.
Which DMV command will give details of the session which is causing the blocking?
- A. sys.dm_exec_requests
- B. sys.dm_pdw_nodes
- C. sys.dm_tran_active_transactions
- D. sys.dm_tran_session_transactions
Answer: A. sys.dm_exec_requests
Explanation: sys.dm_exec_requests dynamic management view provides details about the requests or sessions that are running.
Which of the following DMV commands is used to find the active transactions in a session?
- A. sys.dm_tran_active_transactions
- B. sys.dm_tran_locks
- C. sys.dm_exec_sessions
- D. None of the above
Answer: A. sys.dm_tran_active_transactions
Explanation: sys.dm_tran_active_transactions provides information about active transactions, while sys.dm_tran_locks is for the current request waiting for a lock and sys.dm_exec_sessions is to view current session on SQL.
True or False: Checking the wait type LCK_M_X can help in identifying blocking sessions.
- True
- False
Answer: True
Explanation: If a session is waiting and its wait type is LCK_M_X, this could indicate a blocking scenario.
What does the blocking_session_id column in sys.dm_exec_requests indicate?
- A. The session that is being blocked.
- B. The session that is causing the block.
- C. Both A and B.
- D. None of the above.
Answer: B. The session that is causing the block.
Explanation: The blocking_session_id column represents the session ID of the blocking transaction.
True or False: Deadlocks and blocking sessions are the same.
- True
- False
Answer: False
Explanation: Deadlocks and blocking sessions are different. A deadlock occurs when two processes each hold a lock and need a lock held by the other process. A session becomes blocking when it holds a lock on a specific resource and a second session tries to acquire a conflicting lock type on the same resource.
What tool or feature does Azure provide to monitor blocking sessions and deadlocks?
- A. Azure Monitor
- B. Azure Dashboard
- C. Azure SQL Analytics
- D. Azure Advisor
Answer: C. Azure SQL Analytics
Explanation: Azure SQL Analytics is a tool in the Azure portal that monitors your Azure SQL database and detects issues related to blocking sessions and deadlocks.
Which Azure feature can be used to automatically detect and mitigate blocking issues?
- A. Azure Machine Learning
- B. Azure SQL Intelligent Insights
- C. Azure Data Studio
- D. Azure Data Explorer
Answer: B. Azure SQL Intelligent Insights
Explanation: Azure SQL Intelligent Insights can provide automatic detection and mitigation for performance issues including those caused by blocking sessions.
Interview Questions
What is the primary tool in Azure SQL for identifying sessions that cause blocking?
The primary tool for identifying sessions that cause blocking in Azure SQL is the “Activity Monitor”.
What is blocking in database sessions?
Blocking occurs when one SQL Server session (Spid) holds a lock on a specific resource and a second SQL Server session requires a conflicting lock type on the same resource. Blocking is an integral part of SQL Server.
What is the purpose of sp_who2 stored procedure in Azure SQL?
sp_who2 is a stored procedure in Azure SQL that provides information about the current users, sessions, and processes in an Azure SQL DB instance. It’s used to identify sessions that cause blocking.
What is the role of the “sys.dm_exec_requests” dynamic management view in identifying blocking sessions?
The “sys.dm_exec_requests” dynamic management view is used to show information about each request that is executing within Azure SQL Database, which can be used to identify blocked SQL sessions.
What is a deadlock in Azure SQL Database environment?
A deadlock in Azure SQL Database environment is a situation wherein two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.
How is the DBCC INPUTBUFFER command used in identifying blocking in Azure SQL?
The DBCC INPUTBUFFER command is used to display the last statement sent by a client to an instance of Azure SQL Database. This can help to identify any problematic commands causing blocking.
How can Extended Events be used to identify blocking sessions in Azure SQL?
Extended Events can be configured to capture sqlserver.lock_acquired and sqlserver.lock_released events, which record when a lock is acquired or released by a session. It helps in identifying sessions that cause blocking by monitoring and recording lock activity.
How does the “Wait Statistics” feature help in Azure SQL to identify blocking sessions?
“Wait Statistics” is a feature in Azure SQL that allows you to see the time that sessions spend waiting for various resources. It provides indicators to the types of resources that may be causing blocking.
What type of information does the “blocking_session_id” column provide in an Azure SQL?
The “blocking_session_id” column in an Azure SQL provides the ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available.
What is the “sys.dm_tran_locks” dynamic management view used for in Azure SQL?
The “sys.dm_tran_locks” dynamic management view is used in Azure SQL to return information about the locks that are currently held by transactions, helping to identify sessions causing blocking.
What is a “lock” in the context of Azure SQL sessions?
A “lock” in the context of Azure SQL sessions is a mechanism that prevents users from reading data being modified by another user, protecting the data integrity in a multi-user environment.
Which SQL command is used to detect and provide information about blocking in Azure SQL?
The SQL command “SELECT” along with a join against ‘sys.dm_exec_requests’, ‘sys.dm_exec_sessions’ and ‘sys.dm_tran_locks’ dynamic management views can be used to detect and provide information about blocking.
Can you monitor blocking issues by using SQL Server Management Studio in Azure SQL?
Yes, SQL Server Management Studio offers Activity Monitor that shows information about database processes and how these processes affect the current instance of SQL Server, helping to identify potential blocking issues.
What is the “blocked process threshold” configuration in Azure SQL used for?
The “blocked process threshold” configuration option controls the minimum time that a process must be blocked before a blocked process report is generated, which helps in identifying sessions that cause blocking.
How does deadlock monitoring contribute to identifying blocking sessions in Azure SQL?
Deadlock monitoring aids in identifying blocking sessions by tracking situations where two or more tasks permanently block each other, effectively analysing and resolving the deadlock situation.
extutorials.com