В предыдущей статье мы обсудили, как определить, выполняется ли задание в SQL Server Agent дольше, чем следовало бы. В этой статье мы сосредоточимся на отдельных шагах задания и рассмотрим, как определить шаги задания, которые выполняются дольше ожидаемого.

Решение

SQL Server хранит данные в базе данных msdb для истории заданий между перезапусками. В этой истории заданий содержится информация о каждом отдельном шаге задания. Анализируя данные истории шагов задания, мы можем определить шаги задания, которые выполняются долго. В данном случае мы определяем “долгое выполнение” как шаги задания, которые в настоящее время выполняются дольше, чем на два стандартных отклонения от среднего значения, что указывает на выброс.

Для этого мы можем использовать следующие переменные:

• @MinHistExecutions: Минимальное количество выполнений шага задания, которое мы хотим учитывать. Это служит базовым значением для начала работы.
• @MinAvgSecsDuration: Пороговое значение для минимальной продолжительности шага задания в секундах. Нас не интересуют задания, у которых историческая средняя продолжительность меньше этого порога.
• @HistoryStartDate: Дата начала для оценки исторической средней продолжительности.
• @HistoryEndDate: Дата окончания для оценки исторической средней продолжительности.

Изменяя эти переменные, мы можем контролировать факторы, определяющие, какие шаги задания мы хотим отслеживать.

Пример кода

DECLARE @HistoryStartDate datetime
  ,@HistoryEndDate datetime
  ,@MinHistExecutions int
  ,@MinAvgSecsDuration int

SET @HistoryStartDate = '19000101'
SET @HistoryEndDate = GETDATE()
SET @MinHistExecutions = 1.0
SET @MinAvgSecsDuration = 1.0

DECLARE @currently_running_jobs TABLE (
    job_id UNIQUEIDENTIFIER NOT NULL
    ,last_run_date INT NOT NULL
    ,last_run_time INT NOT NULL
    ,next_run_date INT NOT NULL
    ,next_run_time INT NOT NULL
    ,next_run_schedule_id INT NOT NULL
    ,requested_to_run INT NOT NULL
    ,request_source INT NOT NULL
    ,request_source_id SYSNAME NULL
    ,running INT NOT NULL
    ,current_step INT NOT NULL
    ,current_retry_attempt INT NOT NULL
    ,job_state INT NOT NULL
)

--захватить детали о заданиях
INSERT INTO @currently_running_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

;WITH JobStepsHistData AS
(
  SELECT job_id, step_id
  ,date_executed=msdb.dbo.agent_datetime(run_date, run_time)
  ,secs_duration=run_duration/10000*3600
                      +run_duration%10000/100*60
                      +run_duration%100
  FROM msdb.dbo.sysjobhistory
  WHERE run_status = 1 -- Успешно
)
,JobHistStats AS
(
  SELECT job_id, step_id
        ,AvgDuration = AVG(secs_duration*1.)
        ,AvgPlus2StDev = AVG(secs_duration*1.) + 2*stdevp(secs_duration)
  FROM JobStepsHistData
  WHERE date_executed >= DATEADD(day, DATEDIFF(day,'19000101',@HistoryStartDate),'19000101')
  AND date_executed < DATEADD(day, 1 + DATEDIFF(day,'19000101',@HistoryEndDate),'19000101')
  GROUP BY job_id, step_id
  HAVING COUNT(*) >= @MinHistExecutions
  AND AVG(secs_duration*1.) >= @MinAvgSecsDuration
)

SELECT jd.job_id
      ,j.name AS [JobName]
      ,sjs.step_id
      ,sjs.step_name
      ,MAX(act.start_execution_date) AS [ExecutionDate]
      ,AvgDuration AS [Historical Avg Duration (secs)]
      ,AvgPlus2StDev AS [Min Threshold (secs)]
FROM JobStepsHistData jd
JOIN JobHistStats jhs on jd.job_id = jhs.job_id AND jd.step_id = jhs.step_id
JOIN msdb..sysjobs j on jd.job_id = j.job_id
JOIN msdb..sysjobsteps sjs on jd.job_id = sjs.job_id AND jd.step_id = sjs.step_id
JOIN @currently_running_jobs crj ON crj.job_id = jd.job_id
JOIN msdb..sysjobactivity AS act ON act.job_id = jd.job_id
AND act.stop_execution_date IS NULL
AND act.start_execution_date IS NOT NULL
WHERE DATEDIFF(SS, act.start_execution_date, GETDATE()) > AvgPlus2StDev
AND crj.job_state = 1
GROUP BY jd.job_id, j.name, sjs.step_id, sjs.step_name, AvgDuration, AvgPlus2StDev

Резюме

Традиционные оповещения, которые фокусируются на продолжительности задания, не учитывают историческую среднюю продолжительность. В результате операторы получают уведомление о том, что задание выполняется долго на основе фиксированного времени. Однако лучшим методом является анализ данных, уже хранящихся в базе данных msdb. Анализируя продолжительность конкретных шагов задания, вы можете определить место, где возникает узкое место. Таким образом, если отправлено оповещение, вы знаете, что требуется действие.