We came across an interesting scenario yesterday and I'm trying to figure out if there was an easier way to diagnose and remediate the issue.
We are running SQL Server 2019 CU 12 Enterprise edition.
We had an INSERT to a large, heavily accessed table that included a bunch of indexes. The session was not showing as being blocked yet was in a suspended state and causing a lot of blocking. There was no wait_info given from sp_whoisactive but querying sys.dm_os_waiting_tasks showed a couple of CXPACKET waits for that session.
SELECT wt.session_id, ot.task_state, wt.wait_type, wt.wait_duration_ms, wt.blocking_session_id, wt.resource_description, es.[host_name], es.[program_name] FROM sys.dm_os_waiting_tasks wt INNER JOIN sys.dm_os_tasks ot ON ot.task_address = wt.waiting_task_address INNER JOIN sys.dm_exec_sessions es ON es.session_id = wt.session_id WHERE es.is_user_process = 1 and wt.session_id = 411;
Seeing this, we eventually came to the conclusion that the INSERT went parallel to update the various indexes (using dirty row counts).
select count(*) from schema.table with (nolock INDEX(PK)); select count(*) from schema.table with (nolock INDEX(Index1));
Digging into the sys.dm_tran_locks DMV, we identified a bunch of IS lock requests for that table coming from the blocked sessions.
SELECT * FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'schema.table');
So we started killing them and then the INSERT started moving again.
We found it odd this scenario didn't show up as blocking and are curious if there was an easier way to identify and provide evidence to what was happening. Going on a hunch that killing blocked sessions would break the logjam is something we'd prefer not to do.