This article describes how to collect data to troubleshoot an issue with
SQL blocking.
What blocking is, and how it differs from deadlocking:
- An SQL blocking issue is different from an SQL deadlock issue. Blocking occurs in the following scenarios:
- When one process (UserA) tries to acquire a lock on a database object (table, page, row)
- When another process (UserB) already has an incompatible lock on the same or parent object
The scenario above prevents UserA from accessing this object until UserB is finished with the object and has released it.
- A deadlock occurs when two processes compete for exclusive access to a resource, but one process is unable to obtain exclusive access because the other process prevents it. The result is a standoff where neither process can continue. The only way out of a deadlock is for one of the processes to be closed. The SQL Server automatically detects when deadlocks have occurred. SQL kills one of the processes, known as the victim.
- There's a natural progression of events during a transaction. The transaction places locks on several resources because it needs to protect the referential integrity and database consistency. These locks block other transactions from acquiring locks on the same resources, until the transaction with the lock releases the lock. It releases the lock by either completing or being rolled back. These actions (locking and blocking) are normal events within the SQL Server, and are to be expected. But, if you have a long running transaction, it can create long-term blocking in the database and prevents other work from going on. If two different transactions enter into a deadlock situation, the SQL Server closes one of the transactions so that the system isn't locked up.
- If you see a few deadlocking errors, they're unlikely to lead to any noticeable issues. You can safely ignore any errors within the ePO console. If many deadlock errors occur, follow the steps listed in this article to collect additional information about these deadlock errors so we can investigate.
- For SQL blocking, the first step in the investigation is to identify the query or queries that block other queries (known as head blockers). To help identify what queries are responsible for blocking, use the query attached to this article. You must run them while the issue occurs. To capture the output in a readable format, use the steps listed in the "Solution" below.
