First, resolving the current immediate blocking situation is important, but to prevent the issue from reoccurring or at least to make an informed decision as to what to do about the root cause (even if it is nothing), we need to track down the offending statement and hopefully the logic it supports to see if any changes to the statement, the business logic or the data model is needed.
I recommend using Adam Mechanics sp_whoisactive, go get it. Specifically, I would run it with these options:
Exec sp_whoisactive @find_block_leaders=1, @get_additional_info =1, @get_locks=1, @get_full_inner_text=1, @get_outer_command = 1;
How to use the result.
As so often with metrics in MSSQL, several metrics should be viewed in context to get the beste understanding of what is going on. Indeed, not all blocking is problematic, and some is usually expected and, in some cases, even essential. For instance when using RCSI, and row versioning for a certain action is undesirable and can be fatal to application logic. Relevant columns with its associated help information (taken from the script) for clarity:
blocking_session_id:
When applicable, shows the blocking SPID
blocked_session_count:
(Requires @find_block_leaders option)
The total number of SPIDs blocked by this session, all the way down the blocking chain.
wait_info:
Aggregates wait information, in the following format:
(Ax: Bms/Cms/Dms)E
A is the number of waiting tasks currently waiting on resource type E. B/C/D are wait times, in milliseconds. If only one thread is waiting, its wait time will be shown as B. If two tasks are waiting, each of their wait times will be shown (B/C). If three or more tasks are waiting, the minimum, average, and maximum wait times will be shown (B/C/D). If wait type E is a page latch wait and the page is of a “special” type (e.g. PFS, GAM, SGAM), the page type will be identified. If wait type E is CXPACKET, the nodeId from the query plan will be identified
Breakdown:
Running as default you can identify blocking by the column blocking_session_id. Any non-null values indicates a blocked statement.
To see where the problem lies
@find_block_leaders: Walk the blocking chain and count the number of total SPIDs blocked all the way down by a given session. Also enables task_info Level 1, if @get_task_info is set to 0.
In case you need to see more details about the problem queries themselves:
@get_additional_info:
From the help: Get additional non-performance-related information about the session or request text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type.
If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error).
If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id, applock_hash, metadata_resource, metadata_class_id, object_name, schema_name
And you might need this as well if you need to se an entire batch/procedure and not just the active statement.
@get_outer_command:
From the help: Get the associated outer ad hoc query or stored procedure call, if available (in a separate column sql_command)
@get_full_inner_text:
From the help: If 1, gets the full stored procedure or running batch, when available If 0, gets only the actual statement that is currently running in the batch or procedure. Makes the content of the sql_text column be the entire batch and not just the active statement.
What if you do not have access to sp_whoisactive on the server?
Use sp_who2 to identify blocking if you have no other tools available (shipped with mssql), but to be honest, activity monitor can also do the trick if this is all that is available (despite it’s bad reputation). If you find blocking, resolving the issue will probably involve more legwork, i.e. querying and joining those pesky DMVs manually. Unless you are satisfied with simply killing the blocking query, but then the problem is simply postponed, and the offending query not identified.