I’m looking at the history from this morning in SQL DM. I have 13 sessions being blocked by entry 152 which for a while there I wasn’t able to find. It was suggested that I check the filters and when I turned off the filter that removed the SQL DM threads it showed up. It only blocks for a few seconds but seems to be creating a chain reaction that the system can’t catch up from.
The query that was blocking is below. I’m not seeing it holding any locks so I’m not sure why it would be blocking the queries that it is.
insert into #ActiveWaits
select
wt.session_id,
case when @Elapsed > isnull(wait_duration_ms,0) then @Elapsed else wait_duration_ms end,
wt.wait_type,
host_name,
program_name,
login_name,
statement_txt = case when isnull(is_user_process,0) = 0 then null else
(select substring(text, statement_start_offset/2 + 1,
(case when statement_end_offset < 1
then len(convert(nvarchar(max),text)) * 2
else statement_end_offset
end – statement_start_offset)/2 + 1)
from sys.dm_exec_sql_text(sql_handle)) end,
database_id = r.database_id,
case when @Elapsed > isnull(wait_duration_ms,0) then @PrevTicks else wait_started_ms_ticks end,
task_bound_ms_ticks,
statement_start_offset,
statement_end_offset
from
sys.dm_os_waiting_tasks wt
left join sys.dm_os_workers w
on wt.waiting_task_address = w.task_address
left join sys.dm_exec_sessions s
on wt.session_id = s.session_id
left join
sys.dm_exec_requests r
on r.session_id = s.session_id
where
wt.wait_type not in (‘BAD_PAGE_PROCESS’,’BROKER_EVENTHANDLER’,’BROKER_RECEIVE_WAITFOR’,’BROKER_TASK_STOP’,’BROKER_TO_FLUSH’,’BROKER_TRANSMITTER’,’CHECKPOINT_QUEUE’,’CHKPT’,’CLR_SEMAPHORE’,’DBMIRROR_EVENTS_QUEUE’,’FT_IFTS_SCHEDULER_IDLE_WAIT’,’KSOURCE_WAKEUP’,’LAZYWRITER_SLEEP’,’LOGMGR_QUEUE’,’MISCELLANEOUS’,’ONDEMAND_TASK_QUEUE’,’REQUEST_FOR_DEADLOCK_SEARCH’,’RESOURCE_QUEUE’,’SLEEP_SYSTEMTASK’,’SLEEP_TASK’,’SQLTRACE_BUFFER_FLUSH’,’WAITFOR’,’XE_DISPATCHER_WAIT’,’XE_TIMER_EVENT’,’FILESTREAM_WORKITEM_QUEUE’,’SP_SERVER_DIAGNOSTICS_SLEEP’)
and r.status <> ‘sleeping’
and r.session_id >= 50
and s.session_id >= 50
and isnull(is_user_process,0) = 1
and r.session_id <> @@spid
and lower(program_name) not like lower(‘SQL diagnostic manager%’)