1 . 背景描述
本公司的 SQL Server 服务器近百台, 为了收集服务器运行的状态, 需要在各个实例上部署监控 Job, 将收集到的信息推送到中央管理服务器.
收集的信息主要包括: 慢查询, 阻塞, 资源等待, Connection_Trace log ,Job 执行状态, 复制订阅状态, Alwayson 状态, 数据库字典 (特别是索引) 等各种记录.
因此, 对这些的 Job 的维护工作也是日常工作的一部分, 为了集中统一管理, 我们将各个 SQL Server 都注册到了一台服务器上. 这样也方便集中统一部署, 更新 Job Script, 不需要逐个登入服务器进行部署更新了.
注册服务器的管理是通过 SSMS 来实现的. 点击 视图 -->已注册的服务器
<图 - 1>
打开后, 我们可以进行管理. 为便于管理, 我们将 本公司 SQL Server 分成了三组 FileDB ,Master,Slave. 主要考虑这三种类型部署管理的脚本不一样, 例如部署在 Master 机器上的脚本根本不需要部署在 Slave 上面, 相同型的机器归拢到一个组别中.
<图 - 2>
2. 通过中央管理服务器部署 Job
监控 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 属性, 其开始时间是一模一样的. 这不是我们想要的.
<图 - 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 代码 部分.
代码中的开始时间 为参数 @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, 而查看其作业计划的开始时间不同, 为随机产生的, 符合了代码优化的要求.
<图 - 5>
此 Server 上的这个 Job 的开始时间是 00:04:40 执行, 每隔 5 分钟执行一次.
<图 - 6>
此 Server 上的 Job 为 00:00:28 分钟执行, 每隔 5 分钟执行一次, 与图 - 5 的时间点是不同的, 完美的错开了.
本文版权归作者所有, 未经作者同意不得转载, 谢谢配合!!!
本文版权归作者所有, 未经作者同意不得转载, 谢谢配合!!!
本文版权归作者所有, 未经作者同意不得转载, 谢谢配合!!!
来源: https://www.cnblogs.com/xuliuzai/p/10050614.html