Toad World® Forums

High Overhead Query References @Dell_EnumJobs and @Dell_JobRunDetails

Hi, Spotlight Essentials reveals the SQL behind the worst performing queries in one of our instances. (See SQL below). The query is from a larger script that must allocate declared temporary tables named @Dell_EnumJobs and @Dell_JobRunDetails.

Can anyone provide sample SQL for allocating (and loading?) these table variables, so I can go about tuning the larger query?

Thanks.

Here is the full query:

select
j.name as JobName,
c.name as Category,
j.enabled as Enabled,
case ej.running
when 1 then N’Running’
else case h.run_status
when 2 then N’Inactive’
when 4 then N’Inactive’
else N’Completed’
end
end as CurrentStatus,
coalesce(ej.current_step,0) as CurrentStepNbr,
LastRunDateTimeX as LastRunTime,
dateadd(s, LastRunDurationSec, LastRunDateTimeX) as LastRunFinishTime,
case h.run_status
when 0 then N’Fail’
when 1 then N’Success’
when 2 then N’Retry’
when 3 then N’Cancel’
when 4 then N’In progress’
end as LastRunOutcome,
case h.run_status
when 0 then – combine message from job outcome (step 0) and last step that ran
(select top 1 message from msdb.dbo.sysjobhistory with (nolock) where job_id = j.job_id and step_id = 0 order by instance_id desc) + N’ - ’

  • isnull((select top 1 message from msdb.dbo.sysjobhistory with (nolock) where job_id = j.job_id and step_id > 0 order by instance_id desc, step_id desc), N’Unknown’)
    else ‘’
    end as LastRunMessage,
    LastRunDurationSec as LastRunDuration,
    case when ej.next_run_date > 20000000 then
    convert(datetime,
    substring(ej.next_run_date,1,4) + N’-’
  • substring(ej.next_run_date,5,2) + N’-’
  • substring(ej.next_run_date,7,2) + N’ ’
  • substring(ej.next_run_time,1,2) + N’:’
  • substring(ej.next_run_time,3,2) + N’:’
  • substring(ej.next_run_time,5,2) + N’.000’
    , 121)
    else null
    end as NextRunTime,
    j.description as Description,
    case when datediff(day,(case when ej.last_run_date > 20000000 then
    dateadd(ss,
    datediff(ss,convert(datetime,0,112),
    dateadd(hh,(cast(h.run_duration as bigint)/10000),
    dateadd(mi,(cast(h.run_duration as bigint)/10000%100),
    dateadd(ss,(cast(h.run_duration as bigint)%100),
    convert(datetime,0,112))))),
    LastRunDateTimeX)
    else null end),CURRENT_TIMESTAMP) < 8
    and h.run_status = 0
    and ej.running <> 1
    and j.enabled = 1 then 1
    else 0

end as RaiseAlarm,
case
when not (c.name like N’REPL-%’) and c.name <> N’Replication’ then
case when ej.running = 1 then datediff(s, l.start_execution_date,getdate())
else null
end
else null
end as currentrunduration,
averagerunduration
from
@Dell_EnumJobs ej
left join @Dell_JobRunDetails l on ej.job_id = l.job_id
left join msdb.dbo.sysjobs j on ej.job_id = j.job_id
left outer join msdb.dbo.syscategories c on j.category_id = c.category_id
left outer join msdb.dbo.sysjobhistory h with (nolock) on ej.job_id = h.job_id
and ej.last_run_date = h.run_date
and ej.last_run_time= h.run_time
and h.step_id = 0
cross apply
(
select LastRunDateTimeX =
case when ej.last_run_date > 20000000 then
convert(datetime,
substring(ej.last_run_date,1,4) + N’-’

  • substring(ej.last_run_date,5,2) + N’-’
  • substring(ej.last_run_date,7,2) + N’ ’
  • substring(ej.last_run_time,1,2) + N’:’
  • substring(ej.last_run_time,3,2) + N’:’
  • substring(ej.last_run_time,5,2) + N’.000’
    , 121)
    else
    null
    end
    ) LRDT
    cross apply
    (
    select LastRunDurationSec = case when cast(h.run_duration as bigint) > 0 then
    (cast(h.run_duration as bigint)/1000000)(360024)+ – incase it goes to days!
    (cast(h.run_duration as bigint)/10000%100)*3600+
    (cast(h.run_duration as bigint)/100%100)*60+
    (cast(h.run_duration as bigint)%100) end
    ) LRDS

Hi,

@Dell_EnumJobs and @Dell_JobRunDetails are temporary tables used in two Spotlight background scripts. Here’s SQL extract of those scripts allocating these tables.

declare @Dell_EnumJobs table

(

job_id uniqueidentifier not null,

last_run_date nvarchar(20) not null,

last_run_time nvarchar(20) not null,

next_run_date nvarchar(20) not null,

next_run_time nvarchar(20) not null,

next_run_schedule_id int not null,

requested_to_run int not null, – bool

request_source int not null,

request_source_id sysname collate database_default null,

running int not null, – bool

current_step int not null,

current_retry_attempt int not null,

job_state int not null

)

declare @Dell_JobRunDetails table

(

job_id uniqueidentifier not null,

averagerunduration bigint,

run_requested_date datetime,

start_execution_date datetime

)