Monitor Blocking

Applies to the following products and features: The on-premises SentryOne Portal feature for SQL Sentry.

Introduction

Blocking occurs in SQL Server when a session places a lock on a resource, and an additional session attempts to lock that same resource, but is unable to obtain the desired lock due to the existing lock from the first session. Another session can come along, attempting to lock that resource before the second has even had a chance to do so, creating a blocking chain. When these blocks become excessive and last longer, performance starts to decline and can cause a serious issue in your database's performance.

Monitor Blocking maps the relationships between all blocking and blocked sessions (SPIDs) in a blocking chain, allowing you to pinpoint the cause and fix the blocking issue.

Blocking by

At the top of the screen is the Blocking by... chart, which allows you to select Application, Wait Resource, or Wait Type. Each chart displays the Total Blocks, Total Time (in seconds), and Average Time (in seconds).

Note:  Hover over the charts to view more details.

Monitor Blocking: Hover for More Details

Application

Use the Blocking by Application chart to discover which application is causing the most or longest blocks.

In the example below, we can see that the .Net SqlClient Data Provider has the longest average block time, but it's the SQL Server Agent Job 'Block A' that has the highest number of blocks, and longest amount of total time with blocking.

SentryOne Monitor Blocking by Application Charts

Wait Resource

SQL Server can place locks on resources, at different levels, such as a table, page, or single row. The Blocking by Wait Resource chart shows you which resources are having the most serious blocks.

SentryOne Monitor Blocking by Resource Charts

Additional Information: Transaction Locking and Row Versioning Guide on Microsoft Docs.

Wait Type

The Blocking by Wait Type chart shows you which wait types (e.g. LCK_M_IX, PAGELATCH_SH, etc.) are applicable to the blocks.

SentryOne Monitor Blocking by Wait Type Charts

Head Blockers

The Head Blockers table displays the details behind the blocks. 

The top row is the head of the blocking chain and contains the blocking statement. There will be an icon at the beginning of the row to indicate if the session has completed (check mark) or is currently still running (running person).

Select the arrow to expand the row and view the blocked sessions in the chain (labeled with the stop button icon ). All blocked statements are nested underneath the blocking statement.

SentryOne Monitor Blocking Head Blockers TableHead Blockers table showing the blocking session and the blocked session.

SentryOne Monitor Blocking Head Blockers Blocking ChainExample of a blocking statement with multiple blocked statements.

Available Columns

NameDescription
SPIDThe session process ID of the associated blocked/blocking process.
Start TimeStart time of the request.
DurationThe length of time that the block exists.
StatementThe command text associated with the request.
Wait TimeDuration of wait time in milliseconds.
Wait TypeName of the wait type.

Additional Information: See the sys.dm_os_wait_stats (Transact-SQL) MSDN article.
Wait ResourceName of the resource on which the request is currently waiting.
ApplicationThe associated application.
DatabaseThe associated database.
LoginThe login name associated with the session.
VersionA unique record is shown for each version of a blocking chain, denoted by the Version column. A new row is added each time the blocking chain changes, meaning that a blocked SPID is either added or removed from the chain between polling intervals. For some blocks this may happen frequently, creating multiple rows, while others may not change at all for the duration of the block.

Text Data

The Text Data section displays the T-SQL for the highlighted statement in the Head Blockers table.

SentryOne Monitor Blocking SPID T-SQL Text Data

Additional Information: Back to Basics: The "Runaway" Query

Filters

Use the filter icon in the upper right to apply filters to the Applications, Databases, or or Hosts.

SentryOne Monitor Blocking Filter Options

Make selections on the filter screen, then select Apply to apply them, or Reset to undo your changes and return to the default view (everything selected, no filters).

Note: 

  • The filter does not affect the Blocking by... charts. It filters values for the Head Blockers table only (including the Text Data).
  • The filter icon will be highlighted in blue () if a filter is applied.
  • At least one selection must be made from each filter category to apply a filter.
  • The filter does not impact the collection of blocking data.