如何使用長時間運行作業的警報監控SQL代理

這篇文章主要介紹如何使用長時間運行作業的警報監控SQL代理，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

問題

當你必須在繁忙的生產服務器上管理數百個計劃作業時，不可避免地會出現作業需要很長時間才能完成的情況，從而導致大量等待或影響其他進程的性能。在深入調查性能下降的原因之前，我們想知道一項工作何時開始花費太長時間。有些工作不僅需要很長時間才能完成，而且它們可能需要比通常使用的時間更長的時間才能完成。

哪些工作的表現正在倒退？緩慢的工作現在是否一直成為問題，還是一次性發生？當需要調查一項工作時，通常在時間限制內，運行時長是我們分析中首先要考慮的指標。

為了將當前運行時間與同一作業之前執行的持續時間進行比較，我們通常會查看作業的前一個運行時間的歷史記錄，并看到當前運行時間比之前的幾個持續時間長。然而，如果你以 DBA、開發人員或 DevOps 工程師的身份管理 500 份工作，一次調查一項工作的任務可能需要半天時間，但仍然無法讓你更接近結論。

你可能需要檢查 20 個作業，你不想一直單獨照看每份工作。在這種情況下，發送到你的郵箱的自動警報會派上用場。本文中的解決方案是創建一個將接受參數的存儲過程。此參數是用于計算過去作業平均持續時間的天數。該過程會生成一份報告，顯示當前正在運行的作業的性能正在下降（退化）以及可選的電子郵件警報。

理解這種方法的某些步驟以及我們已經在生產中使用的方便的存儲過程如下所示（在運行受監控作業的同一服務器上安排為單獨的作業（有關此類作業的腳本，請參閱“附錄”） ) 以幫助您收到有關在輪詢時正在退化的一個或多個作業的電子郵件警報。我們asp_long_running_Regressing_Jobs_Alerts每分鐘在我們的環境中執行 Stored Proc, ，因為我們的作業持續時間從幾秒到幾小時不等。在給予之前SP DDL，SP 的某些組件被分解為先決條件（小尺寸代碼片段），有助于全面了解警報 SP。

所需工具列表：SQL Server（以下代碼在 2012 及更高版本上測試）

這里的目標是將每個當前正在運行的作業的持續時間與給定時間段內同一作業的所有運行時間的平均持續時間進行比較。

注意：在繼續編譯下面給出的存儲過程之前，請確保你在要分析計劃作業的環境中具有高訪問權限。理想情況下，管理員。要檢查你的訪問級別（如果你不是該服務器的管理員，請運行此或類似的 T-SQL 語句。如果你沒有此查詢的結果，則你沒有對 MSDB 表的選擇權限。

USE MSDB
GO
SELECT HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + 
       QUOTENAME(name), 'OBJECT', 'SELECT') AS have_select, *
FROM   sys.tables
GO

解決方案

下面存儲過程的 T-SQL DDL 代碼可以在您選擇的任何數據庫中編譯。我們使用專用DBA_db于此類管理 SP。

USE [DBA_db]
GO
 
-- <begin stored procedure DDL/>
 
CREATE PROCEDURE asp_long_running_Regressing_Jobs_Alerts
         @history_days int = 7,
         @avg_duration_multiplier float = 1.5,
         @bEmail bit = 0,
         @bSaveToTable bit = 0,
         @RecipientsList Varchar(1000) = 'myName@myCoDomain.com',
         @ignore_zero_durations bit = 0
 
AS
/* example of usage:
   exec DBA_db..asp_long_running_Regressing_Jobs_Alerts
               @history_days = 45,
               @avg_duration_multiplier = 2,
               @bEmail = 0,
               @bSaveToTable = 0,
               @RecipientsList  = 'myName@myCoDomain.com;'   ,
               @ignore_zero_durations = 1
 
AUTHOR(s):
Vladimir Isaev;
-- + V.B., S.L;
-- contact@sqlexperts.org
*/      
 
/*PARAMETERS:
@history_days int          (how many days back we use for AVF run duration)
@avg_duration_multiplier   (how many times longer than AVG will qualify job 
                            for producing an alert)
@bEmail                    (send out Alert Email or just print the msg about Regressing jobs)
                           -- 'REGRESSION' is defined here by Duration only
*/
SET NOCOUNT ON
BEGIN
 
        select sj.name,
               sja.start_execution_date,
               sja.stop_execution_date,
               ajt.min_run_duration,
               ajt.max_run_duration,
               ajt.avg_run_duration,
               datediff(ss, start_execution_date, getdate()) as cur_run_duration
        into #Regressing_Jobs
 
        from msdb..sysjobactivity sja
               left join
               (select job_id,
                       avg(dbo.udf_convert_int_time2ss(run_duration)) as avg_run_duration,
                       min(dbo.udf_convert_int_time2ss(run_duration)) as min_run_duration,
                       max(dbo.udf_convert_int_time2ss(run_duration)) as max_run_duration
                       from msdb..sysjobhistory
                       where step_id=0
                       and run_date >CONVERT(varchar(8),GETDATE() - @history_days,112)
                       and ((run_duration <> 0 or @ignore_zero_durations = 0))
                       and run_duration < 240000
                       group by job_id
               )ajt on sja.job_id=ajt.job_id
        join msdb..sysjobs sj on sj.job_id=sja.job_id
        where
               sja.session_id = (SELECT TOP 1 session_id
                                   FROM msdb.dbo.syssessions
                                  ORDER BY agent_start_date DESC)
               AND start_execution_date is not null
               and stop_execution_date is null
               and datediff(ss, start_execution_date, getdate()) >
                   ajt.avg_run_duration * @avg_duration_multiplier
 
        select name as JobName,
               start_execution_date,
               stop_execution_date,
               dateadd(second, min_run_duration, 0) as min_run_duration,
               dateadd(second, max_run_duration, 0) as max_run_duration,
               dateadd(second, avg_run_duration, 0) as avg_run_duration,
               dateadd(second, cur_run_duration, 0) as cur_run_duration
        into #Regressing_Jobs_DurAsDate
        from #Regressing_Jobs
                                            --  waitfor delay '00:00:10'
        declare @sHtml varchar(max) = ''
 
        declare @tableHTML  nvarchar(max) =
               N'<H1>Job(s) taking longer than recent baseline duration
                (in descending avg duration order):</H1>' + Char(13)
               + N'    <table border="1">'           + Char(13)
               + N'    <tr bgcolor="#ddd">'          + Char(13)
               + N'           <th>Start Time</th>'   + Char(13)
               + N'           <th>Job Name</th>'     + Char(13)
               + N'           <th>Host Name</th>'    + Char(13)
               + N'           <th>History Days</th>' + Char(13)
               + N'           <th>Avg Dur Mul</th>'  + Char(13)
               + N'           <th>Min Dur</th>'      + Char(13)
               + N'           <th>Max Dur</th>'      + Char(13)
               + N'           <th>Avg Dur</th>'      + Char(13)
               + N'           <th>Cur Dur</th>'      + Char(13)
               + N'    </tr>'                        + Char(13)
 
        select @tableHTML =  @tableHTML
               + FORMATMESSAGE(
                       '<tr><td>%s</td>'      _
                          + Char(13) --start_execution_date
                       + '<td>%s</td>'        + Char(13) --name
                       + '<td>%s</td>'        + Char(13) --@@SERVERNAME
                       + '<td >%i</td>' _
                          + Char(13) --@history_days
                       + '<td >%s</td>' 
                          + Char(13) --@avg_duration_multiplier
                       + '<td>%s</td>'        + Char(13) --Min Dur
                       + '<td>%s</td>'        + Char(13) --Max Dur
                       + '<td>%s</td>'        + Char(13) --Avg Dur
                       + '<td>%s</td>'        + Char(13),--Cur Dur
                               convert(varchar, start_execution_date, 120),
                               JobName,
                               @@SERVERNAME,
                               @history_days,
                               convert(varchar, @avg_duration_multiplier),
                               format(min_run_duration, N'HH\hmm\mss\s'),
                               format(max_run_duration, N'HH\hmm\mss\s'),
                               format(avg_run_duration, N'HH\hmm\mss\s'),
                               format(cur_run_duration, N'HH\hmm\mss\s')
                       )
          from #Regressing_Jobs_DurAsDate
          order by avg_run_duration desc, JobName
 
               select @tableHTML = @tableHTML + '</tr></table>' + Char(13)
 
        select @sHtml = @tableHTML
        --select @sHtml
 
        declare @DateStr varchar(30) = convert(varchar,getdate(),121)
        IF @bEmail = 1 and (select count(*) from #Regressing_Jobs) > 0
         begin
              
               declare @sSubject varchar(250)
                   = @@SERVERNAME + ' Job(s) taking longer than recent baseline duration: ' _
                     + @DateStr 
 
               EXEC msdb.dbo.sp_send_dbmail  @profile_name='SQL Server Monitoring Account',
                                              @recipients= @RecipientsList,
                                              @subject=@sSubject, 
                                               @body=@sHtml,
                                              @body_format = 'HTML'
 
               print 'email sent: ' + CHAR(13) + @sHtml
        end
 
        IF @bSaveToTable = 1
          begin
               insert into RegressingJobs
         (
 CaptureDateTime,
 JobName,
 start_execution_date,
 HostName,
 history_days,
 avg_duration_multiplier,
                           min_run_duration,
 max_run_duration,
 avg_run_duration,
 cur_run_duration
 )
select         @DateStr,
JobName,
start_execution_date,
@@SERVERNAME,
@history_days,   
@avg_duration_multiplier,
min_run_duration,
max_run_duration,
avg_run_duration,
cur_run_duration
               from #Regressing_Jobs_DurAsDate
          end
 
        begin
 
         SELECT 'JOBS THAT ARE TAKING LONGER THAN USUAL:  '
         select  @DateStr as CaptureDateTime, JobName, _
            start_execution_date, @@SERVERNAME as 'Server',
                 @history_days as '@history_days', _
                    @avg_duration_multiplier as '@avg_duration_multiplier',
                 min_run_duration, max_run_duration, _
                    avg_run_duration, cur_run_duration
         from    #Regressing_Jobs_DurAsDate
 
        end
 
--all currently running jobs:
       begin
               SELECT ' ALL JOBS THAT ARE CURRENTLY RUNNING:  '
               SELECT
                 -- '',  -- CAST (ja.job_id AS VARCHAR(max)),
                       j.name AS job_name,
                  cast ( ja.start_execution_date as varchar) start_execution_time,  
                  cast ( ja.stop_execution_date  as varchar) stop_execution_time,  
                  -- ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
                       Js.step_name step_name
               FROM msdb.dbo.sysjobactivity ja
               LEFT JOIN msdb.dbo.sysjobhistory jh
                       ON ja.job_history_id = jh.instance_id
               JOIN msdb.dbo.sysjobs j
                 ON ja.job_id = j.job_id
               JOIN msdb.dbo.sysjobsteps js
                 ON ja.job_id = js.job_id
                AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
               WHERE ja.session_id =
                    (SELECT TOP 1 session_id
               FROM msdb.dbo.syssessions
           ORDER BY agent_start_date DESC)
               AND start_execution_date is not null
               AND stop_execution_date  is null;
 
        end
END
 
GO
 
-- <end of stored procedure DDL/>

asp_long_running_Regressing_Jobs_Alerts 使用的 2 個 UDF

-- dependencies of asp_long_running_Regressing_Jobs_Alerts:
 
-- udf_convert_int_time
CREATE   FUNCTION [dbo].[udf_convert_int_time] (@time_in INT)
RETURNS TIME
AS
 BEGIN
        DECLARE @time_out TIME
        DECLARE @time_in_str varchar(6)
        SELECT  @time_in_str = RIGHT('000000' + CAST(@time_in AS VARCHAR(6)), 6)
        SELECT  @time_out    = CAST(STUFF(STUFF(@time_in_str,3,0,':'),6,0,':') AS TIME)
  RETURN @time_out
 END
GO
 
-- udf_convert_int_time2ss
CREATE   FUNCTION [dbo].[udf_convert_int_time2ss] (@time_in INT)
RETURNS int
AS
 BEGIN
        DECLARE @time_out int
        select @time_out = datediff(ss, 0,  dbo.udf_convert_int_time(@time_in))
  RETURN @time_out
 END
GO

除了 SP 標頭中列出的調用示例之外，以下是一個典型調用的示例：

exec dba_DB.dbo.asp_long_running_Regressing_Jobs_Alerts
       @history_days = 45,
       @avg_duration_multiplier = 2,
       @bEmail = 1,
       @bSaveToTable = 0,
       @RecipientsList  = 'myName@myCoDomain.com;
                           AssociateName@myCoDomain.com’
       @ignore_zero_durations = 1

SP 的此調用意味著以下內容：

給我一份報告（或警報），說明在 45 天內完成相同作業的平均運行時間所需時間的兩倍的所有作業。通過電子郵件將此類報告發送給我 ( myName) 和我的同事 ( myAssociateName)。不要將此數據保存到基線表，并且不包括持續時間為零的作業。

圖 2：SSMS 中對 SP 的另一個類似調用的示例輸出：在這種情況下，顯示現在花費的時間比過去 2 天花費 AVG 多 10% 的作業。

下圖顯示了由 SP 生成的電子郵件警報在 HTML 中的外觀示例。

此電子郵件通知僅在 時通過調用此 SP 生成parameter @bEmail = 1。

圖 3

電子郵件主題行如下：<ServerName> 作業花費的時間比最近的基線持續時間長；yyyy-mm-dd mm:ss

將結果保存到表格以供將來的歷史分析

如果你決定將報告保存到表格，請執行以下操作：除了通過電子郵件或在 SSMS 中直接運行 SP 收到警報外，還需要一個表格。用@bSaveToTable = 1呼叫 SP 。（它是 SP 的依賴項之一，因此即使此時您沒有將結果放入表格，也最好創建它）。這是表 DDL：

CREATE TABLE [RegressingJobs](
                            [CaptureDateTime]        [datetime]      NULL,
                            [JobName]                [sysname]       NOT NULL,
                            [start_execution_date]   [datetime]      NULL,
                            [HostName]               [sysname]       NOT NULL,
                            [history_days]           [int]           NULL,
                            [avg_duration_multiplier]    [float]     NULL,
                            [min_run_duration]           [time](7)   NULL,
                            [max_run_duration]           [time](7)   NULL,
                            [avg_run_duration]           [time](7)   NULL,
                            [cur_run_duration]           [time](7)   NULL
 ) ON [PRIMARY]
   GO

此調用具有完整功能的 SP 的示例，包括保存到表格和電子郵件警報 ( bSaveToTable= 1, bEmail=1)：

EXEC DBA_db.dbo.asp_long_running_Regressing_Jobs_Alerts
                             @history_days = 30,
                             @avg_duration_multiplier = 2,
                             @bEmail = 1,
                             @bSaveToTable = 1,
                             @RecipientsList  = 'myName@myCoDomain.com;',
                             @ignore_zero_durations = 1

如何出于各種目的調用此 SP 的其他示例

將當前運行的作業與其過去 30 天的歷史進行比較，并報告當前持續時間超過 30 天平均值 1.5 倍的每個作業。不要發送警報電子郵件，也不要將此信息保存到基線表：

EXECUTE DBA_db.dbo.asp_long_running_Regressing_Jobs_Alerts 30, 1.5, 0, 0

將當前運行的作業與其過去 60 天的歷史進行比較，并報告當前持續時間超過 60 天平均值 2 倍的每個作業。向默認收件人（列表）發送電子郵件警報，并且不要將此信息保存到基線表：

EXECUTE DBA_db.dbo.asp_long_running_Regressing_Jobs_Alerts 60, 1.5, 1, 0

注意：強烈建議對參數進行賦值，明確命名每個參數。給出上述示例是為了簡潔。

結論

本文描述了管理員在高度自動化的工作負載環境中管理和分析多個作業的性能問題時面臨的問題。我在此處共享的存儲過程允許管理員在某些作業在給定時間段內花費的時間超過其過去平均持續時間時收到警報。

附錄

下面是用于創建名為 [ MyMonitoredServerName_Maintenance- Regressing Jobs]的計劃作業的 DDL，以asp_long_running_Regressing_Jobs_Alerts每分鐘執行一次并向BigShotAdminBigShotAdmin@MyCoDomain.com發送警報。

USE [msdb]
GO
 
/****** Object:  Job [MyMonitoredServerName_Maintenance - Regressing_Jobs] ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name
                 FROM msdb.dbo.syscategories
   WHERE name=N'[BigLoad]'
     AND category_class=1)
BEGIN
 EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[BigLoad]'
 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
 
END
 
DECLARE @jobId BINARY(16)
 EXEC   @ReturnCode =  msdb.dbo.sp_add_job
        @job_name=N'MyMonitoredServerName_Maintenance - Regressing_Jobs',
                                @enabled=1,
                                @notify_level_eventlog=0,
                                @notify_level_email=0,
                                @notify_level_netsend=0,
                                @notify_level_page=0,
                                @delete_level=0,
                                @description=N'Send email notifications to _
                                @RrecipientsList (last parameter in SP) on CURRENTLY RUNNING _
                                Agent Jobs that regress in performance by duration compared to _
                                baseline (baseline collected during the number of days before _
                                getdate() specified by the first parameter @history_days.',
                                @category_name=N'[BigLoad]',
                                @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [asp_long_running_Regressing_Jobs_Alerts] ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, _
   @step_name=N'asp_long_running_Regressing_Jobs_Alerts',
                                @step_id=1,
                                @cmdexec_success_code=0,
                                @on_success_action=1,
                                @on_success_step_id=0,
                                @on_fail_action=2,
                                @on_fail_step_id=0,
                                @retry_attempts=0,
                                @retry_interval=0,
                                @os_run_priority=0, @subsystem=N'TSQL',
                                @command=N'exec _
                                           dbmaint..asp_long_running_Regressing_Jobs_Alerts
                                        @history_days = 45,
                                        @avg_duration_multiplier = 2,
                                        @bEmail = 1,
                                        @bSaveToTable = 1,
                                        @RecipientsList  = ''BigShotAdmin@MyCoDomain.com;'',
                                        @ignore_zero_durations = 1',
                                        @database_name=N'master',
                                        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'every 1 min',
                                @enabled=1,
                                @freq_type=4,
                                @freq_interval=1,
                                @freq_subday_type=4,
                                @freq_subday_interval=1,
                                @freq_relative_interval=0,
                                @freq_recurrence_factor=0,
                                @active_start_date=20201222,
                                @active_end_date=99991231,
                                @active_start_time=60000,
                                @active_end_time=235959,
                                @schedule_uid=N'999ac144-4e13-4965-82f2-55555cc37a09'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

享受有關回歸/長時間運行的 SQL 代理作業的警報！

以上是“如何使用長時間運行作業的警報監控SQL代理”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！