-- Here's a query that looks at average query time by month, for all queries that took longer then 10 minutes.  

select
  (
    convert(varchar, datepart(year, timestart))+'_'+
    right('00'+convert(varchar,datepart(month, timestart)),2)
  ) as yymm,
  avg (
    datepart(hour, (timeend - timestart)) * 360 +
    datepart(minute, (timeend - timestart)) * 60 +
    datepart(second, (timeend - timestart))
  ) as avgseconds
from jobs
where
  timeend - timestart > 0
  and timeend is not null
  and (
    datepart(hour, (timeend - timestart)) * 60 +
    datepart(minute, (timeend - timestart))
  ) >= 10
  and taskname not like '%__SYSTEM__%'
  and estimate > 1
group by
  convert(varchar, datepart(year, timestart))+'_'+
  right('00'+convert(varchar,datepart(month, timestart)),2)
order by
  convert(varchar, datepart(year, timestart))+'_'+
  right('00'+convert(varchar,datepart(month, timestart)),2)

