In SQL Server, blocfiing occurs when one session holds a locfi on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for when the first SPID locks the resource is small.
Long transactions occur when a single session opens a transaction and never commits or rolls back the transaction. When DML operations occur during an open transaction, they might create a lock on a specific resource, thus escalating to a block. In most applications, transactions are quick and should not stay open for long.
Experda tracks transactions longer than 3 minutes and blocks longer than 30 seconds.
Note: The default Experda Agent sample interval for blocking is every 60 seconds. This means that if your block occurred in between samples, it might not appear in the results.
Blocking – Part 1
| Legend | |
| 1 | Total unique blocking sessions found for the selected timeframe |
| 2 | Longest blocked session wait period for the selected timeframe. |
| 3 | Total long transactions for the selected timeframe. |
Blocking – Part 2
The blocking details grid shows the tracked blocking and long open transactions for the selected timeframe.
| Legend | |||||||||||||||||||||||||||
| 1 | Filter selection defaults to view all types. Other options: Blocking, Open Transactions | ||||||||||||||||||||||||||
| 2 | Main results grid – the blocking/long transactions details table shows in detail the blocking sessions or blocked sessions as a hierarchy. If the block is a blocking type, you can expand the row to see the complete hierarchy of blocked sessions.
In the example, we can see all the sessions that are blocked and their blocking parents. |
||||||||||||||||||||||||||
Blocking by application
Blocking by application module helps recognize the most troublesome top applications that are causing the most blocks.
| Legend | |
| 1 | Top applications by AVG block time |
| 2 | Top application by total block time |
| 3 | Top application by total block count |