I have an enourmous amount of wating_tasks shown in the dashboard (>30000) and I am wondering how they count is calculatet. I found the table [sqlwatch_logger_dm_exec_requests_stats] but don´t know how the column Waiting_tasks is calculated, as I have no idea how the table is populated. Marcin, can you give me a hint? As there is no real performance-problem on the server, I really would like to know what this measure means.
Does the number in the dashboard correspond with the one in the table? Are we saying the dashboard is wrong, or the data in the table is wrong?
The table sqlwatch_logger_dm_exec_requests_stats holds a summarised data from sys.dm_exec_requests and sys.dm_os_waiting_tasks
, sleeping
, suspended
, wait_time
, cpu_time
, waiting_tasks
, waiting_tasks_wait_duration_ms
, snapshot_time
, snapshot_type_id
, sql_instance
)
select
'type' = case when r.session_id > 50 then 1 else 0 end
, 'background' = sum(case status when 'Background' then 1 else 0 end)
-- exclude our own session from counting. This way, if there are no other sessions we can still get a count that shows 0
-- if we excluded it in the where clause, we would have had a missing for this snapshot time which would have upset dashboards
, 'running' = sum(case when status = 'Running' and session_id <> @@SPID then 1 else 0 end)
, 'runnable' = sum(case status when 'Runnable' then 1 else 0 end)
, 'sleeping' = sum(case status when 'Sleeping' then 1 else 0 end)
, 'suspended' = sum(case status when 'Suspended' then 1 else 0 end)
, 'wait_time' = sum(convert(real,wait_time))
, 'cpu_time' = sum(convert(real,cpu_time))
Not quite sure yet. I will run the query tomorrow againt the database and will come back to you. Thank you very much!