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.
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.
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).
- 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:
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.
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.
The Blocking by Wait Type chart shows you which wait types (e.g. LCK_M_IX, PAGELATCH_SH, etc.) are applicable to the blocks.
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 showing the blocking session and the blocked session.
Example of a blocking statement with multiple blocked statements.
|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.|
||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.|
The Text Data section displays the T-SQL for the highlighted statement in the Head Blockers table.
Use the filter icon in the upper right to apply filters to the Applications, Databases, or Hosts.
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).
- 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.