SQL Server建立Job，實現執行相同指令碼而產生不同作業計劃的探究

SQLServer SQL · 發表 2018-12-01 18:52:00

摘要： 1 . 背景描述本公司的SQL Server 伺服器近百臺，為了收集伺服器執行的狀態，需要在各個例項上部署監控Job，將收集到的資訊推送到中央管理伺服器。收集的資訊主要包括：慢查詢、阻塞、資源等待、Connection_Trace log 、Job執行狀態、複製訂閱狀態、Alway...

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)
EXECmsdb.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的時間點是不同的，完美的錯開了。

本文版權歸作者所有，未經作者同意不得轉載,謝謝配合！！！

SQL Server建立Job， 實現執行相同指令碼而產生不同作業計劃的探究