Long Running Query - Variable Run Durations
Cintra_JohnC
Posts: 2 New member
Hello,
We have a situation in our organisation where several SQL Agent Jobs are setup to run every night. These queries check for data in our databases and then will process the data and send emails. Because the operation of these jobs depends on data being generated on a day to day basis sometimes the jobs only run for a few seconds and other times they can run for 3-4minutes.
When Redgate Monitor checks for long running queries it seems to be finding a baseline average of the job duration by querying the last 10 runs from sysjobs. Because our jobs fluctuate between 0 and 3-4mins this skews the average time and throws more alerts than we like.
Is there anyway to exclude runs that were less than set time such as 1 second?
I have tried to create a custom alert to replace the standard one but am unsure how to deploy it if the result is only a single value to define the Low/Medium/High Alert:
The following code is an example which returns the Job Name, Average Duration, LastRunDuration and Flag Value
Can this be turned into a custom alert to tell us which Job Name is running as an unusual duration?
We have a situation in our organisation where several SQL Agent Jobs are setup to run every night. These queries check for data in our databases and then will process the data and send emails. Because the operation of these jobs depends on data being generated on a day to day basis sometimes the jobs only run for a few seconds and other times they can run for 3-4minutes.
When Redgate Monitor checks for long running queries it seems to be finding a baseline average of the job duration by querying the last 10 runs from sysjobs. Because our jobs fluctuate between 0 and 3-4mins this skews the average time and throws more alerts than we like.
Is there anyway to exclude runs that were less than set time such as 1 second?
I have tried to create a custom alert to replace the standard one but am unsure how to deploy it if the result is only a single value to define the Low/Medium/High Alert:
The following code is an example which returns the Job Name, Average Duration, LastRunDuration and Flag Value
WITH JobRuns AS (
SELECT
j.job_id,
j.name AS JobName,
h.run_duration,
ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY h.run_date DESC, h.run_time DESC) AS RunRank
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
WHERE h.step_id = 0 -- Ensures we only get the job outcome, not individual steps
AND h.run_duration > 5
),
Last10Runs AS (
SELECT
job_id,
JobName,
run_duration
FROM JobRuns
WHERE RunRank <= 10
),
AverageDurations AS (
SELECT
job_id,
JobName,
AVG(run_duration) AS AvgDuration
FROM Last10Runs
GROUP BY job_id, JobName
),
LatestRun AS (
SELECT
job_id,
JobName,
run_duration AS LatestDuration
FROM JobRuns
WHERE RunRank = 1
)
SELECT
l.JobName,
a.AvgDuration,
l.LatestDuration,
CASE
WHEN l.LatestDuration > a.AvgDuration * 2
OR l.LatestDuration < a.AvgDuration * 0.5 THEN 2
WHEN l.LatestDuration > a.AvgDuration * 1.5
OR l.LatestDuration < a.AvgDuration * 0.75 THEN 1
ELSE 0
END AS IsDurationUnusual
FROM LatestRun l
JOIN AverageDurations a
ON l.job_id = a.job_id
ORDER BY l.JobName;
Can this be turned into a custom alert to tell us which Job Name is running as an unusual duration?
Answers