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 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).
Change blocking collection to 20 seconds globally by completing the following steps:
Change the blocking collection for an individual instance by completing the following steps:
By default, SQL text data length 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> Query Collection Maximum Text Length).
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.
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.
The Blocking SQL tab has two modes: Real Time and History. Use the toolbar options to switch between these modes.
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.
When the auto-refresh Pause button is selected, the tab goes into History mode. Select a time range, then select Go on the toolbar to view blocks that happened during the specified time range.
If you want to go directly to the most recent block, use the Jump To Last Block button.
The top pane provides client filters for specifying which records to display for the defined interval.
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:
Each block displays in a hierarchical format, showing the relationships between all blocking and blocked SPIDs in a blocking chain.
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.
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.
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.
|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.|
|Orange||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.|
|Red||When 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).
The bottom pane shows the T-SQL Text Data for the currently highlighted Blocking SQL record.
The following are additional options available through the right-click context menu:
|Context Menu Option||Description|
|Jump to Calendar||Opens the selected event on the Event Calendar (requires server to be watched with EM).|
|Jump to Dashboard||Opens the Performance Analysis Dashboard with the selected blocking event overlaid on each of the charts.|
|Jump to Top SQL||Opens the Top SQL tab highlighting the query involved in the blocking event.|
|Right-clicking on a blocking record||Presents 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 command||Removes any grid orderings.|
Additional context menu options include the ability to expand and collapse individual trees or all the records in the Grid view.
|SPID [ecid]||The session process ID of the associated blocked/blocking process.|
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 Server||Name of the SQL Server hosting the SPID.|
|Start Time||Start time of the request.|
|Duration||The length of time that the block exists.|
|Plan||Opens a Plan Explorer session for the associated query plan.|
|Statement||The command text associated with the request.|
|Object||The object associated with the request.|
|Wait Time||Duration of wait time in milliseconds.|
|Wait Type||Name of the wait type. For more information about wait types, 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||The client workstation specific to this session.|
|Application||The associated application.|
|Database||The associated database.|
|Login||The login name associated with the session.|
|Last Batch||The last time a parent process involved in blocking completed a successful batch execution.
Note: This SPID metric can be useful if you have one that remains open and sends various batches. For example, if you are looking at SPIDs that are currently running, and see a SPID in a sleeping state, then you can see that the last batch execution was two hours ago.
|Host Process ID||The process ID of the client program which initiated the session.|