SQL Sentry Portal Blocking

Applies to the following products and features: The on-premises SQL Sentry Portal feature for SQL Sentry. See the Getting Started with SQL Sentry Portal article for more details.

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.

SQL Sentry Portal 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:
Depending on the screen width and number of items in the chart legend, you may need to scroll to see all items:

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.

.Net SQLClient displays the longest avg block time and SQL Server Agent Job Block A displays the highest number of blocks.

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.

Blocking by Wait Resource displaying Total Blocks, Total Time, and Average Time 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.

Blocking By Wait Type displaying Total Blocks, Total Time, Average Time charts, and the wait types applicable to the blocks.

Additional Information:

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.

Head Blockers table (organized by Start Time) displaying the heads of blocking chains and blocking statements.

Head Blockers table showing the blocking session and the blocked session.

Head Blocker table displaying a blocking statement with multiple blocked statements.

Example of a blocking statement with multiple blocked statements.

Available Columns

Name	Description
SPID	The session process ID of the associated blocked/blocking process.
Start Time	Start time of the request.
Duration	The length of time that the block exists.
Statement	The command text associated with the request.
Wait Time	Duration of wait time in milliseconds.
Wait Type	Name of the wait type. Additional Information: See the sys.dm_os_wait_stats (Transact-SQL) MSDN article.
Wait Resource	Name of the resource on which the request is currently waiting.
Host	Name of the server hosting the associated database.
Application	The associated application.
Database	The associated database.
Login	The login name associated with the session.
Version	A 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.

Text Data section displaying the T-SQL Blocking SPID Text Data for the highlighted Head Blocker statement.

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 Hosts.

Blocking Filters that can be applied to Applications, Databases, and Hosts. Checks indicate active filters for the option.

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).