目录
问题
解决方案
结论
附录
问题
当您必须在繁忙的生产服务器上管理数百个计划作业时,不可避免地会出现作业需要很长时间才能完成的情况,从而导致大量等待或影响其他进程的性能。在深入调查性能下降的原因之前,我们想知道一项工作何时开始花费太长时间。有些工作不仅需要很长时间才能完成,而且它们可能需要比通常完成的时间更长的时间。
哪些工作的表现正在倒退?缓慢的工作现在是否一直成为一个问题,还是一次性发生?当需要调查一项工作时,通常在时间限制内,运行时长是我们分析中最先考虑的首要指标。
为了将当前运行时间与同一作业的先前执行的持续时间进行比较,我们通常会查看作业的先前运行时间的历史记录,并看到当前运行时间比之前的几个持续时间长。然而,如果您以DBA、开发人员或DevOps工程师的身份管理500份工作,一次调查一项工作的任务可能需要半天时间,但仍然无法让您更接近结论。
您可能需要检查20个作业。您不想一直单独照看每份工作。在这种情况下,发送到您的邮箱的自动警报会派上用场。本文中的解决方案是创建一个将接受参数的存储过程。此参数是用于计算过去作业平均持续时间的天数。该过程会生成一份报告,显示当前正在运行的作业的性能正在下降(退化)以及可选的电子邮件警报。
理解这种方法的某些步骤以及我们已经在生产中使用的方便的存储过程如下所示。在运行受监控作业的同一服务器上安排为单独的作业(有关此类作业的脚本,请参阅“附录”)以帮助您收到有关在轮询时回归的一个或多个作业的电子邮件警报。我们每分钟在我们的环境中执行存储过程asp_long_running_Regressing_Jobs_Alerts,因为我们的作业持续时间从几秒到几小时不等。在提供SP DDL之前,SP的某些组件被分解为先决条件(小代码片段),有助于全面了解SP警报。
所需工具列表:SQL Server(以下代码在及更高版本上测试)
这里的目标是将每个当前正在运行的作业的持续时间与给定时间段内同一作业的所有运行时间的平均持续时间进行比较。
注意:在继续编译下面给出的存储过程之前,请确保您在要分析计划作业的环境中具有高访问权限。理想情况下,管理员。要检查您的访问级别,如果您不是该服务器的管理员,请运行此或类似的T-SQL语句。如果您没有此查询的结果,则您没有对MSDB表的选择权限。
USE MSDBGOSELECT HAS_PERMS_BY_NAME(QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name), 'OBJECT', 'SELECT') AS have_select, *FROM sys.tablesGO
图 1:结果集显示您对其具有SELECT权限的表。
解决方案
下面存储过程的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@',@ignore_zero_durations bit = 0AS/* 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@;' ,@ignore_zero_durations = 1AUTHOR(s):Vladimir Isaev;-- + V.B., S.L;-- contact@*//*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 ONBEGINselect 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_durationinto #Regressing_Jobsfrom msdb..sysjobactivity sjaleft 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_durationfrom msdb..sysjobhistorywhere step_id=0and run_date >CONVERT(varchar(8),GETDATE() - @history_days,112)and ((run_duration <> 0 or @ignore_zero_durations = 0))and run_duration < 240000group by job_id)ajt on sja.job_id=ajt.job_idjoin msdb..sysjobs sj on sj.job_id=sja.job_idwheresja.session_id = (SELECT TOP 1 session_idFROM msdb.dbo.syssessionsORDER BY agent_start_date DESC)AND start_execution_date is not nulland stop_execution_date is nulland datediff(ss, start_execution_date, getdate()) >ajt.avg_run_duration * @avg_duration_multiplierselect 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_durationinto #Regressing_Jobs_DurAsDatefrom #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 style="text-align:center">%i</td>' _+ Char(13) --@history_days+ '<td style="text-align:center">%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 Durconvert(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_DurAsDateorder by avg_run_duration desc, JobNameselect @tableHTML = @tableHTML + '</tr></table>' + Char(13)select @sHtml = @tableHTML--select @sHtmldeclare @DateStr varchar(30) = convert(varchar,getdate(),121)IF @bEmail = 1 and (select count(*) from #Regressing_Jobs) > 0begindeclare @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) + @sHtmlendIF @bSaveToTable = 1begininsert 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_durationfrom #Regressing_Jobs_DurAsDateendbeginSELECT '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_durationfrom #Regressing_Jobs_DurAsDateend--all currently running jobs:beginSELECT ' 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_nameFROM msdb.dbo.sysjobactivity jaLEFT JOIN msdb.dbo.sysjobhistory jhON ja.job_history_id = jh.instance_idJOIN msdb.dbo.sysjobs jON ja.job_id = j.job_idJOIN msdb.dbo.sysjobsteps jsON ja.job_id = js.job_idAND ISNULL(ja.last_executed_step_id,0)+1 = js.step_idWHERE ja.session_id =(SELECT TOP 1 session_idFROM msdb.dbo.syssessionsORDER BY agent_start_date DESC)AND start_execution_date is not nullAND stop_execution_date is null;endENDGO-- <end of stored procedure DDL/>
asp_long_running_Regressing_Jobs_Alerts使用的2 UDF
-- dependencies of asp_long_running_Regressing_Jobs_Alerts:-- udf_convert_int_timeCREATE FUNCTION [dbo].[udf_convert_int_time] (@time_in INT)RETURNS TIMEASBEGINDECLARE @time_out TIMEDECLARE @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_outENDGO-- udf_convert_int_time2ssCREATE FUNCTION [dbo].[udf_convert_int_time2ss] (@time_in INT)RETURNS intASBEGINDECLARE @time_out intselect @time_out = datediff(ss, 0, dbo.udf_convert_int_time(@time_in))RETURN @time_outENDGO
除了SP标头中列出的调用示例之外,以下是一个典型调用的示例:
exec dba_DB.dbo.asp_long_running_Regressing_Jobs_Alerts@history_days = 45,@avg_duration_multiplier = 2,@bEmail = 1,@bSaveToTable = 0,@RecipientsList = 'myName@;AssociateName@’@ignore_zero_durations = 1
SP的此调用意味着以下内容:
给我一份报告(或警报),说明在45天内完成相同作业的平均运行时间所需时间的两倍的所有作业。通过电子邮件将此类报告发送给我(myName)和我的同事(myAssociateName)。不要将此数据保存到基线表中,也不包括持续时间为零的作业。
图 2:SSMS中对SP的另一个类似调用的示例输出:在这种情况下,显示现在花费的时间比过去2天花费评价多10%的作业。
下图显示了由SP生成的电子邮件警报在HTML中的外观示例。
此电子邮件通知仅在parameter @bEmail = 1时通过调用此SP生成。
图 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@;',@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并向BigShotAdmin(BigShotAdmin@)发送警报。
USE [msdb]GO/****** Object: Job [MyMonitoredServerName_Maintenance - Regressing_Jobs] ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT nameFROM msdb.dbo.syscategoriesWHERE name=N'[BigLoad]'AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[BigLoad]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @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 OUTPUTIF (@@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@;'',@ignore_zero_durations = 1',@database_name=N'master',@flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @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=,@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 QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback:IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO
享受有关回归/长时间运行的SQL代理作业的警报!
/Tips/5311874/Monitoring-SQL-Agent-with-Alerts-for-Long-Running
如果觉得《使用长时间运行作业的警报监控SQL代理》对你有帮助,请点赞、收藏,并留下你的观点哦!