SQL Query for Monitoring Delays in Task Queue Execution

The following SQL statement returns the number of tasks in queue that are delayed by more than ten minutes:

Select count(id) as QueuedTasks
from [confirm].dbo.taskinstance (nolock)
where status=0
and datediff(mi,schedule,getdate())>10;

If a single project is queued up with many different tasks and this is expected behavior that could be ignored as long as it doesn’t affect other projects, the above query may be modified to return a count of distinct projects having delayed tasks in queue instead of the total number of tasks:

select count(distinct(td.projectid)) as QueuedTasks
from [confirm].dbo.taskinstance ti (nolock)
left join [confirm]..taskdefinition td (nolock)
on td.id = ti.taskdefinitionid
where ti.status=0
and datediff(mi,ti.schedule,getdate())>10;