January 21, 2016 at 10:09 am
Hello everybody,
I am trying to investigate why a particular query takes a very long time to finish today. I am using sp_whoisactive and I see the query in suspended status, while there is no wait_info displayed in sp_whoisactive. When I run:
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
I get the session in suspended task_state with wait_duration of 0. I and wait_type = sleep_task.
I have checked for blocking and there does not seem to be any.
I am not sure what else to do to make the process go forward. What else should I check for? The system is not running high on resources and has plenty of processing power and RAM. IO utilization seems to very low as well.
I have not encountered this issue before. any suggestions on how to troubleshoot?
Petr
January 22, 2016 at 2:44 am
That wait type is not very descriptive and is usually nothing to worry about. However, it can be an indication of issues with the query itself. Rather than focus on the system level performance, I would suggest drilling down on the query itself. What is it doing? What does the execution plan look like? Are there tuning opportunities there?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply