Symptoms: SQL has detected a block, which may affect other users trying to access the same resource.
Impact: Medium
This alert means that the designated query has created a block. This may cause disruption to performance to specific users.
Expected behavior :
The time frame during which the first process locks the resource should be very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance.
Possible causes
A process in a rollback state Priority: High
A data modification query that is killed, or canceled outside of a user-defined transaction, will be rolled back. This can also occur as a side effect of the client computer restarting and its network session disconnecting.
A query selected as the deadlock victim will be rolled back. A data modification query often cannot be rolled back any faster than the changes were initially applied. For example, if a DELETE, INSERT, or UPDATE statement had been running for an hour, it could take at least an hour to roll back. This is expected behavior because the changes made must be completely rolled back, or transactional and physical integrity in the database would be compromised. Because this must happen, SQL Server marks the process in a rollback state (which means it cannot be killed or selected as a deadlock victim).
Recommended action :
You should wait for the process to finish rolling back the changes that were made.NOTE: If the server is shut down in the middle of this operation, the database will be in recovery mode upon restarting, and it will be inaccessible until all open transactions are processed. Startup recovery takes essentially the same amount of time per transaction as run-time recovery, and the database is inaccessible during this period. Thus, forcing the server down to fix a block in a rollback state will often be counterproductive.
A distributed deadlock Priority: High
Blocking was caused by a distributed Client/Server deadlock. Unlike a conventional deadlock, a distributed deadlock is not detectable using the lock manager, due to the fact that only one of the resources involved in the deadlock is a SQL Server lock. The other side of the deadlock is at the client application level, over which SQL Server has no control.
Recommended action :
Applications must properly manage transaction nesting levels, or they may cause a blocking problem following the cancellation of the query in this manner. Refer this to the application design team.
Something prevented the transaction from being committed Priority: Medium
Recommended action :
Check what else is running that might block the resources needed by the open transaction.
Query with a long execution time Priority: Medium
Recommended action :
The solution to this type of blocking problem is to look for ways to optimize the query. This class of blocking problem may just be a broader performance problem, and require you to pursue it as such… see our notes about PLE here and long queries here
A sleeping process has lost track of nesting level Priority: Medium
This type of blocking can often be identified by a SPID that is sleeping or waiting for a command, yet whose transaction nesting level is greater than zero. This can occur if the application experiences a query timeout, or issues a cancel without also issuing the required number of ROLLBACK and/or COMMIT statements. When a SPID receives a query timeout or cancel, it will terminate the current query and batch, but does not automatically rollback or commit the transaction. The application is responsible for this, as SQL Server cannot assume that an entire transaction must be rolled back simply due to a single query being canceled. The query timeout or cancel will appear as an ATTENTION signal event for the SPID in the Profiler trace.
Recommended action :
Applications must properly manage transaction nesting levels, or they may cause a blocking problem following the cancellation of the query in this manner. Refer this to the application design team.
Background
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. Tracking the history of the block via the AimBetter Profiler gives a clear analysis if the whole query execution picture, enabling you to take the correct action.
The occurrence of blocking is closely related to timed elapsed between query start and completion. The duration and nature of a query determine how long its locks are held and their impact on other queries.
For SELECT statements (if the query is not executed within a transaction and no lock hints are used), the locks will only be held on a resource at the time it is actually being read, not for the duration of the query. As long as data is organized properly and elements such as caching and page files are optimal, this should very rarely generate a block.
For INSERT, UPDATE, and DELETE statements, the locks are held for the duration of the query, both for data consistency and to allow the query to be rolled back if necessary. Chances of a block being generated are higher.
Without a full picture of the conflicting calls causing the block, it can be wasteful to try and resolve the issue via code or resource optimization.
