Performance Analysis Blocking SQL

The Blocking SQL tab displays all SQL Server blocks that occurred during the active date range that meet the Minimum Block Duration. Each block is displayed in a hierarchical format, showing the relationships between all blocking and blocked SPIDs in a blocking chain.

Blocking SQL Collection

Blocking SQL collection is controlled with the Blocking SQL Source settings. The Minimum Block Duration setting determines how long a block exists before information is collected on it. The default setting is 15 seconds. Adjust Blocking Collection as desired through the Settings pane (View >Settings). 

Example One

Change blocking collection to 20 seconds globally by completing the following steps:

  1. Select the All Targets (globally) node in the Navigator pane (View > Navigator).
    SentryOne select All Targets in the Navigator
  2. In the Settings pane (View > Settings), select SQL Server from the top drop-down list.
    SentryOne Settings pane select SQL Server settings from the top drop-down list
  3. Select Blocking SQL Source from the second drop-down list to show Blocking Source settings that are being applied globally.
    SentryOne Settings pane select Blocking SQL Source settings from the bottom drop-down list
  4. Change the Minimum Block Duration setting to 20 seconds, and then select Ok; it's saved automatically.
    SentryOne Settings pane Blocking Source settings set Minimum Block Duration to 20

Example Two

Change the blocking collection for an individual instance by completing the following steps:

  1. Select the desired instance node in the Navigator pane (View > Navigator).
    SentryOne select the desired node in the Navigator
  2. In the Settings pane, select Blocking SQL Source from the second drop-down list to see the Blocking SQL Source settings that are configured for the instance.
    SentryOne Settings pane Blocking SQL Source Settings instance level
  3. Change the Inherit From Parent setting to False.
    SentryOne Settings pane Collections Settings change Inherit From Parent to False
  4. Change the Minimum Block Duration setting to your desired value; it's saved automatically.
    SentryOne Settings pane Collection Settings change Minimum Block Duration to 20

Blocking SQL Retention

By default, Performance Analysis Blocking SQL, Top SQL, and Deadlock data is retained for 15 days. This is controlled with the Keep Performance History For setting found on the Storage tab of Global Settings (Navigator pane > Configuration >Global Settings > Storage tab). For more information about retention, see the Data Capacity Planning topic.

SentryOne Monitoring Service Settings Storage tab Keep Event History For setting

Blocking SQL Alerting

Configure actions in response to Blocking SQL conditions. The SQL Server: Blocking SQL condition occurs each time a block is collected, and therefore has a direct correlation to the Blocking SQL Source Collection settings. Configure an action in response to the SQL Server: Blocking SQL: Output Content Match (OCM) condition. The OCM condition is useful in narrowing the notifications you see concerning blocking.

SentryOne Actions Selector SQL Server: Blocking SQL: Output Content Match

Note:  Consider applying a Response Ruleset to any action you configure with the SQL Server: Blocking SQL condition. Response Rulesets control how often actions are taken in response to conditions. In this case, apply a Response Ruleset to limit the Blocking alerts you receive, while still retaining Blocking information on the Blocking SQL tab.

SQL Textdata Length

By default, textdata for captured events is limited to 10,000 characters. Change this maximum length in the Monitoring Service Settings (Navigator pane > Configuration > Global Settings > Monitoring Service Settings > Advanced tab).

SentryOne Monitoring Service Settings Advanced tab Query Collection Maximum Text Length setting

Display/Controls

Toolbar

SentryOne toolbar

The Blocking SQL tab has two modes, Real Time and History. The active mode is controlled through the toolbar. When the auto-refresh Play button is selected, the tab enters Real Time mode. In Real Time mode, any blocking SQL that happened within the last 10 minutes displays.

SentryOne Blocking SQL tab Real-time mode

When the auto-refresh Pause button is selected, the tab goes into History mode. Select a time range and then select Go on the toolbar to view blocks that happened during that time period.

SentryOne Blocking SQL tab History mode

Alternatively, use the Jump To Last Block button to navigate to the last block.

SentryOne Blocking SQL tab Jump To Last Block button

Filter

The top pane provides client filters for specifying which records to display for the defined interval.SentryOne Blocking SQL tab Filter

Note:  The Filter pane is for client side filtering only and doesn't impact what's collected. For more information, see the Blocking SQL Collection section.

After changing any of the filters, it's necessary to select Refresh, or press F5, to apply the filter. To select more than one item in a list, use Shift-Click or CTRL-Click. The following are available filters:

FilterImage
SQL ServersSentryOne Blocking SQL tab SQL Servers Filter
ApplicationsSentryOne Blocking SQL tab Applications Filter
DatabasesSentryOne Blocking SQL tab Databases Filter
HostsSentryOne Blocking SQL tab Hosts Filter
Wait Resources
SentryOne Blocking SQL tab Wait Resources Filter
Show Top
SentryOne Blocking SQL tab Show Top Filter

Blocking SQL Gridview

Each block displays in a hierarchical format, showing the relationships between all blocking and blocked SPIDs in a blocking chain.

SentryOne Blocking SQL tab Blocking SQL Gridview

The head of the blocking chain is the top node in the hierarchy which contains the blocking statement, and displays with an orange node if the statement is still running, and a green node once it's completed.

SentryOne Blocking SQL tab Head Spid Completed

Sub-nodes in the chain are the statements that are being blocked, and they display with a red node. Any subsequent statements that are being blocked are nested underneath, giving you a complete picture of the blocking chain.

SentryOne Blocking SQL tab subsequent block example

A unique record is shown for each version of a blocking chain, denoted by the Version column. A new record is created every time the blocking chain changes, meaning that a blocked SPID is either added or removed from the chain between polling intervals.

Note:  For some blocks, this may happen frequently, causing multiple records to be created, while others may not change at all for the duration of the block.

Color CodeDescription
Green The head of the blocking chain is the top node in the hierarchy which contains the blocking statement and displays with a green node once it's completed.
OrangeThe head of the blocking chain is the top node in the hierarchy which contains the blocking statement and displays with an orange node if the statement is still running.
RedWhen sub-nodes in the chain are the statements that are being blocked they display with a red node.

The polling interval for blocks is determined by the Live Event Monitor Polling Interval (Navigator pane > Configuration > Global Settings > Monitoring Service Settings > Advanced tab).

SentryOne Monitoring Service Settings Advanced tab Live Event Monitor Polling Interval

Text Data

The bottom pane shows the T-SQL Text Data for the currently highlighted Blocking SQL record.

SentryOne Blocking SQL tab Text Data

Note:  For some SPIDs the bottom pane may be blank if SentryOne is unable to collect the TSQL due to timing issues.

Additional Options 

The following are additional options available through the right-click context menu:

Context Menu OptionDescriptionDepiction
Jump to Calendar
Opens the selected event on the Event Calendar (requires server to be watched with EM).SentryOne Blocking SQLtab Jump To Calendar from the context menu
Jump to Dashboard
Opens the Performance Analysis Dashboard with the selected blocking event overlaid on each of the charts.SentryOne Blocking SQL Jump To Dashboard from the context menu
Jump to Top SQL
Opens the Top SQL tab highlighting the query involved in the blocking event.SentryOne Blocking SQL tab Jump To Top SQL from the context menu
Right-clicking on a blocking recordPresents a context menu that allows you to jump to the instance on the calendar or kill the process associated with the blocking event.
Clear Sorting commandRemoves any grid orderings.SentryOne Blocking SQL tab Clear Sorting context menu option

Additional context menu options include the ability to expand and collapse individual trees or all the records in the Grid view.

Blocking SQL Metrics

NameDescription
SPID [ecid]The session process ID of the associated blocked/blocking process.
Version

A unique record is shown for each version of a blocking chain, denoted by the Version column. A new record is created 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 records, while others may not change at all for the duration of the block.

Note:  The Polling Interval for blocks is determined by the Event Monitor Polling Interval  (Navigator pane > Configuration > Global Settings > Monitoring Service Settings > Advanced tab).
SQL ServerName of the SQL Server hosting the SPID.
Start TimeStart time of the request.
DurationThe length of time that the block exists.
PlanOpens a Plan Explorer session for the associated query plan.
StatementThe command text associated with the request.
ObjectThe object associated with the request.
Wait TimeDuration of wait time in milliseconds.
Wait TypeName of the wait Ttpe. For more information about wait types, see the sys.dm_os_wait_stats (Transact-SQL) MSDN article.
Wait ResourceName of the resource on which the request is currently waiting.
HostThe client workstation specific to this session..
ApplicationThe associated application.
DatabaseThe associated database.
LoginThe login name associated with the session.
Last BatchThe last time a client process completed batch execution.
Host Process IDThe process ID of the client program which initiated the session.