A blocked query is a database query waiting for the blocking query to complete its execution.
Blocking occurs when one process holds a lock on a specific resource and a second one attempts to acquire a conflicting lock type on the same resource.
This alert is informative, indicating how many queries are being blocked by some higher-level action query.
It’s important to understand why queries are blocked, which queries are causing the blocking, and which resources are involved.
Find out how you can save hours of work when investigating the root cause of this issue.
Symptoms :
Users report that some process has stopped or timed out. Otherwise, they notice that their actions are taking longer to provide results.
Impact: Medium
One or more queries cannot be completed due to their dependency on a higher-level query that is blocking a required resource. This may cause disruption to the performance of specific users, affected by the delay and a possibility for time out or cancellation of the query.
Expected behavior :
There is no standard setting for this alert
The time frame during which the process is locked by another process holding on to the resource should be very small.
Longer blockings might interrupt the optimal business flow and may occur in parallel to an increase in usage (more transactions, queries running longer, increased disk I/O, and more).
Possible causes:
1- The query is being blocked by another query. Priority: High
Once several users or processes are trying to access the same data concurrently, there is a higher chance of blocking situations. There might be several reasons for this – long-running queries, deadlocks, latch contention too high, overloaded hardware resources, or transaction levels.
Problem identification:
Identify events of queries not executing well or waiting for too long to execute. This might be an indication of queries being blocked by other queries.
- Track the database server activity: identify which SQL statements have high execution time and consume most of the resources. In addition, track events of queries time-out. For SQL Server, you can use the built-in activity monitors or SQL Profiler. This task may take hours or days of work, requiring a highly skilled DBA.
- Identify which of the queries were blocking queries. You have to make sure that the database and table in the long-running queries are the same as the query that was blocking. In addition, you have to identify which are the blocked queries. For that, you can use database system views.
- Look in the database logs for messages related to locks, deadlocks, or blocked queries. The correct location depends on which database management system you are working with.
- Investigate the issue looking for the cause of it. For possible causes, please view our article about blocking queries.
Recommended action :
Examine all currently running queries, especially ones that appear to have remained active for unexpected lengths.
Investigate the cause of the top-level query blocking – we recommend reading our article about blocking queries.
Investigate the code of the blocking query, and if necessary, terminate it.
Consider upgrading hardware if possible.
Adjust transaction isolation levels to minimize locking conflicts.
Once the issue is resolved, alerts about this kind of event should decrease.
AimBetter is essential to have a good understanding of your database system in order to address the root cause of issues, saving time, effort, and unnecessary expenses.
