Recently we have been facing couple of issues with regard to the way the jobs have been scheduled. To investigate this, we needed a query that can identify the execution time of jobs to see if they overlap. In addition, we needed to see which steps within the job are taking unusually long to complete as well as information about jobs that are slow overall. There are plenty of scripts already available on the Internet that gives it information but this query summarizes it in a way that the same query can be used for high level as well as detailed level information. In addition, we have used a new logic to calculate the duration rather than do a substring or character manipulation logic. The query uses sysjobsteps table to identify the execution time of the last run. By replacing sysjobsteps with sysjobhistory we would be able to achieve a historical aggregate over multiple runs with some slight manipulation to the query.
/*
This SQL script analyzes SQL Server Agent job execution statistics.
- It defines a Common Table Expression (CTE) named 'cte' that retrieves job and job step details from msdb..sysjobs and msdb..sysjobsteps.
- For each job step, it selects:
- Job name and enabled status
- Step ID and step name
- Last execution outcome (as a descriptive string: Succeeded, Failed, Retry, Canceled, Unknown, or No Status)
- Last execution timestamp (using a user-defined function [dbo].[agent_datetime])
- Last run duration
- Duration in seconds (calculated as the difference in seconds between the start and end of the step)
- The main SELECT aggregates execution time statistics at the job level:
- Total duration (SumDurationSec)
- Minimum step duration (MinDurationSec)
- Maximum step duration (MaxDurationSec)
- Number of steps (NoOfSteps)
- Results are ordered by total duration in descending order.
Usage:
- Uncomment the first SELECT to see detailed execution times at the step level.
- The default SELECT provides aggregated statistics at the job level.
Note:
- Requires the user-defined function [dbo].[agent_datetime] to convert SQL Agent date/time integers to datetime.
*/
WITH cte
AS (
SELECT j.name
,j.enabled
,sjs.step_id
,sjs.step_name
,'LastState' = CASE
WHEN last_run_outcome = 0
THEN 'Failed'
WHEN last_run_outcome = 1
THEN 'Succeeded'
WHEN last_run_outcome = 2
THEN 'Retry'
WHEN last_run_outcome = 3
THEN 'Canceled'
WHEN last_run_outcome = 5
THEN 'Unknown'
ELSE 'No Status'
END
,[dbo].[agent_datetime](sjs.last_run_date, sjs.last_run_time) AS LastExecTimestamp
,sjs.last_run_duration
,'DurationInSec' = datediff(SECOND, [dbo].[agent_datetime](last_run_date, '000000'), ([dbo].[agent_datetime](last_run_date, last_run_duration)))
FROM msdb..sysjobs j
LEFT OUTER JOIN msdb..sysjobsteps sjs ON j.job_id = sjs.job_id
)
-- uncomment for detailed execution time at the step level
--select * from cte
-- uncomment for aggregated execution time at the job level
SELECT name AS JobName
,sum(DurationInSec) AS SumDurationSec
,Min(DurationInSec) AS MinDurationSec
,MAX(DurationInSec) AS MaxDurationSec
,count(*) AS NoOfSteps
FROM cte
GROUP BY name
ORDER BY sum(DurationInSec) DESCPlease Consider Subscribing
