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, 
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.

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.

  • It’s a shame that you didn’t post screenshots of any of the things you saw with Who Is Active, or the command that you ran. It would have helped you get a more informed answer. If you have any of that saved, I’d suggest editing your question with those details. Jan 12 at 19:15
  • I added the queries but unfortunately didn't capture the results or any usable screenshots. The team was working fast and furious as it had drawn the attention of all those folks you normally don't want to attract attention from.
    – Nate H
    Jan 12 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.