欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

程序员文章站 2024-01-26 22:54:16
1 . 背景描述 本公司的SQL Server 服务器近百台,为了收集服务器运行的状态,需要在各个实例上部署监控Job,将收集到的信息推送到*管理服务器。 收集的信息主要包括:慢查询、阻塞、资源等待、Connection_Trace log 、Job执行状态、复制订阅状态、Alwayson状态、数 ......

1 . 背景描述

本公司的sql server 服务器近百台,为了收集服务器运行的状态,需要在各个实例上部署监控job,将收集到的信息推送到*管理服务器。

收集的信息主要包括:慢查询、阻塞、资源等待、connection_trace log 、job执行状态、复制订阅状态、alwayson状态、数据库字典(特别是索引)等各种记录。

因此,对这些的job的维护工作也是日常工作的一部分,为了集中统一管理,我们将各个sql server 都注册到了一台服务器上。这样也方便集中统一部署、更新 job script,不需要逐个登入服务器进行部署更新了。

注册服务器的管理是通过ssms来实现的。点击 视图-->已注册的服务器 

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

<图-1>

打开后,我们可以进行管理。为便于管理,我们将 本公司sql server分成了三组 filedb 、master、slave。主要考虑这三种类型部署管理的脚本不一样,例如部署在master机器上的脚本根本不需要部署在slave上面, 相同型的机器归拢到一个组别中。

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

 <图-2>

2. 通过*管理服务器部署job 

监控job的部署,如果通过注册服务来管理的话,只能通过脚本来实现。

我们先在一台sql server上产生需要执行的脚本,然后,再在*管理服务器上执行。产生创建脚本可以通过点击  配置job界面上的【脚本】按钮产生。(逐步配置,最后点击【脚本】即可)

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

<图-3>

在这个案例中,我们设置的job的功能 是 每隔5分钟执行 master数据库下面的一个sp: usp_testfordba_testjobschedule,即运行的sql命令 为 exec usp_testfordba_testjobschedule。job的名称为dba_testjob_schedule。

那么导出的创建job的script如下:

use [msdb]
go
declare @jobid binary(16)
exec  msdb.dbo.sp_add_job @job_name=n'dba_testjob_schedule', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @description=n'此为测试案例,无实际意义', 
        @category_name=n'[uncategorized (local)]', 
        @owner_login_name=n'sa', @job_id = @jobid output
select @jobid
go
exec msdb.dbo.sp_add_jobserver @job_name=n'dba_testjob_schedule', @server_name = n'xxxxxxxxxx'
go
use [msdb]
go
exec msdb.dbo.sp_add_jobstep @job_name=n'dba_testjob_schedule', @step_name=n'step1', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_fail_action=2, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=n'tsql', 
        @command=n'exec usp_testfordba_testjobschedule', 
        @database_name=n'master', 
        @flags=0
go
use [msdb]
go
exec msdb.dbo.sp_update_job @job_name=n'dba_testjob_schedule', 
        @enabled=1, 
        @start_step_id=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=2, 
        @notify_level_netsend=2, 
        @notify_level_page=2, 
        @delete_level=0, 
        @description=n'此为测试案例,无实际意义', 
        @category_name=n'[uncategorized (local)]', 
        @owner_login_name=n'sa', 
        @notify_email_operator_name=n'', 
        @notify_netsend_operator_name=n'', 
        @notify_page_operator_name=n''
go
use [msdb]
go
declare @schedule_id int
exec msdb.dbo.sp_add_jobschedule @job_name=n'dba_testjob_schedule', @name=n'schedule1', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=5, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20181201, 
        @active_end_date=99991231, 
        @active_start_time=0, 
        @active_end_time=235959, @schedule_id = @schedule_id output
select @schedule_id
go

 

3. 待优化的问题

这份代码,如果同时部署在多台服务器上,有没有潜在的问题,或者说有没有优化的地方呢?

答案是肯定的,有!!!

收集监控数据的sp中,有向*管理服务器insert数据的功能,如果各个服务器同时插入,则会出现排队,甚至出现超时报错的问题。

查看每台服务器job,以上代码对应的schedel属性,其开始时间是一模一样的。这不是我们想要的。

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

<图-4>

如果是每隔五分钟执行一次,其实,我们希望各个job之间的 开始时间可以随机 在00:00:00--00:05:00分布。这样,各个job 之间开始执行的时间随机, 撞车的概率大大减少,向*服务器插入数据排队也会减少

那么如何实现呢?

4. 代码优化

仔细分析上面的创建代码,其实它包含了sp_add_job、sp_add_jobserver、sp_add_jobstep、sp_update_job、sp_add_jobschedule五个部分。每个部分基本上对应添加job界面的一个动作。如果调整各个job的开始时间,就要想办法调整 【作业计划属性】界面上的【开始时间】 设置。图-4 作业计划 属性 对应  sp_add_jobschedule 代码 部分。

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

代码中的开始时间 为参数 @active_start_time

分析到这儿,相信部分同学已经有思路了。

ok,我们直接分享更新后的代码。其它代码部分不做调整,只调整最后 sp_add_jobschedule 部分的代码

调整后如下:

use [msdb]
go
declare @schedule_id int
------------------------------- start -- job schedule 开始时间在指定范围内随机产生 20181015 carson xu-----------------
declare @randstart_time int ----分布式数据库job 开始时间指定范围随机产出,减少并发等待
select @randstart_time=cast( floor(rand()*5) as int) * 100 +cast( floor(rand()*60) as int)--5 代表5分钟内的随机数,60 代表60 秒内随机数。
---将参数随机值 赋予 存储中的 sp_add_jobschedule 的@active_start_time就ok了。即代码中的@active_start_time=@randstart_time.
---active_start_time 的数据类型为 int,无默认值。时间格式为 hhmmss,采用 24 小时制。
---cast( floor(rand()*5) as int) * 100 +cast( floor(rand()*60) as int) 其实默认了开始时间(小时是00开始),完整应该是0*100000+cast( floor(rand()*5) as int) * 100 +cast( floor(rand()*60) as int)
---如果是8点就应该是8*100000+cast( floor(rand()*5) as int) * 100 +cast( floor(rand()*60) as int)
--------------------------------end ----------------------------------------------------------
exec msdb.dbo.sp_add_jobschedule @job_name=n'dba_testjob_schedule', @name=n'schedule1', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=4, 
        @freq_subday_interval=5, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=1, 
        @active_start_date=20181201, 
        @active_end_date=99991231, 
        @active_start_time=@randstart_time, 
        @active_end_time=235959, @schedule_id = @schedule_id output
select @schedule_id
go

 

5. 验证

图-5、图-6 是用上面相同代码生成的job, 而查看其作业计划的开始时间不同,为随机产生的,符合了代码优化的要求。

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

<图-5> 

此server上的这个job的开始时间是00:04:40执行,每隔5分钟执行一次。

SQL Server创建Job, 实现执行相同脚本而产生不同作业计划的探究

<图-6> 

此server 上的job为00:00:28 分钟执行,每隔5分钟执行一次,与图-5的时间点是不同的,完美的错开了。

 

 

 本文版权归作者所有,未经作者同意不得转载,谢谢配合!!!

 本文版权归作者所有,未经作者同意不得转载,谢谢配合!!!

本文版权归作者所有,未经作者同意不得转载,谢谢配合!!!