SQL Replication Agent Profile 效能調教

高頻交易在使用交易式複寫時,如果使用的Agent Profile是Defaut值,會有效能問題。 這篇文章會介紹用UI以及用TSQL的作法調整Agent Profile,使得交易式複寫獲得效能改善。

每個Subscription都有自己的Distribution Agent,因此不同的Subscription都可以調整不同的Distribution Agent Profile。

但是Log Agent 則是一個DB只有一個。所以會有很多Publication共用同一個Log Agent。

如何設定Distribution Agent

  1. 在Replication Monitor 的Subscriptions 右鍵開啟Agent Profile
  2. 按New產生新的Agent Profile,因為Default Agent Profile 是沒辦法調整的
    undefined
  3. 命名並調整參數
    undefined
    參數調整
    • commitbatchsize >> 2000
    • commitbatchthreshold >> 4000
    • historyverboselevel >> 2
    • KeepAliveMessageInterval >> 600
    • pollinginterval >> 5
    • querytimeout >> 3600
  4. 新增完後勾選使用新建的Agent Profile — High Transaction Distribution
  5. Resart SQL Agent or Distrubution Agent

上述UI動作也可以透過以下TSQL 完成

--Create Distrubution Agent Profile - High Transaction Distrubution

DECLARE @AgentID Int
DECLARE @Profilename varchar(1000)='High Transaction Distrubution'

select @AgentID=max(profile_id)+1 from [msdb].[dbo].[MSagent_profiles]
-- create the new agent, & capture it's ID to @AgentID
EXEC sp_add_agent_profile
	@profile_id= @AgentID OUTPUT
	, @profile_name= @Profilename
    , @agent_type= 3 --1 Snapshot Agent, 2 Log Reader Agent, 3 Distribution Agent, 4 Merge Agent, 9 Queue Reader Agent
	, @description= 'For High Transaction Replication'
    --, @default= 1
EXEC sp_help_agent_profile

-- change the parameter of the new Agent
EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'commitbatchsize'
	, @parameter_value= '2000'

EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'commitbatchthreshold'
	, @parameter_value= '4000'

EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'historyverboselevel'
	, @parameter_value= '2'

EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'KeepAliveMessageInterval'
	, @parameter_value= '600'

EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'pollinginterval'
	, @parameter_value= '5'

EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'querytimeout'
	, @parameter_value= '3600'
--Remapping Distrubution Agent to new profile
DECLARE @AgentID Int
DECLARE @Profilename varchar(1000)='High Transaction Distrubution'
DECLARE @ProfileID Int	

select @ProfileID=profile_id from [msdb].[dbo].[MSagent_profiles]
where profile_name=@Profilename

--Remapping Publication
SELECT @AgentID=ID
  FROM [distribution].[dbo].[MSdistribution_agents]
  where subscriber_db<>'virtual'
  and subscriber_db='SourceDB'
  and publication='Publication'

EXEC [distribution]..sp_update_agent_profile
 @agent_type =3
    ,@agent_id = @AgentID
    ,@profile_id = @ProfileID


-- Restart SQL Agent

設定Log Reader Agent

  1. 在Replication Monitor 的Agents下 右鍵開啟Log Agent ProfileLog Reader
  2. 按New產生新的Agent Profile
    Log Agent Profi
  3. 命名並調整參數
    Log Agent Profi detail

    參數調整

    • LogScanThreshold>> 1000000
    • ReadBatchSize >> 2000
    • querytimeout >> 3600
  4. 新增完後勾選使用新建的Agent Profile — High Transaction LogReader
  5. Restart Log Reader DB (必須要一個一個DB Restart)

上述UI動作也可以透過以下TSQL 完成

--Create Log Reader Agent Profile - High Transaction LogReader

DECLARE @AgentID Int
DECLARE @Profilename varchar(1000)='High Transaction LogReader'
select @AgentID=max(profile_id)+1 from [msdb].[dbo].[MSagent_profiles]
-- create the new agent, & capture it's ID to @AgentID
EXEC sp_add_agent_profile
	@profile_id= @AgentID OUTPUT
	, @profile_name= @Profilename
    , @agent_type= 2 --1 Snapshot Agent, 2 Log Reader Agent, 3 Distribution Agent, 4 Merge Agent, 9 Queue Reader Agent
	, @description= 'For High Transaction Replication'
    --, @default= 1
EXEC sp_help_agent_profile

-- change the parameter of the new Agent
EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'LogScanThreshold'
	, @parameter_value= '1000000'

EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'QueryTimeout'
	, @parameter_value= '3600'

EXEC sp_change_agent_parameter
	@profile_id= @AgentID
	, @parameter_name= 'ReadBatchSize'
	, @parameter_value= '2000'

 

--Remapping Log Reader Agent to new profile
DECLARE @AgentID Int
DECLARE @Profilename varchar(1000)='High Transaction LogReader'
DECLARE @LogReaderDB varchar(1000)='SourceDB'
DECLARE @ProfileID Int	

select @ProfileID=profile_id from [msdb].[dbo].[MSagent_profiles]
where profile_name=@Profilename

--Remapping SourceDB
SELECT @AgentID=ID
  FROM [distribution].[dbo].[MSlogreader_agents]
  WHERE publisher_db=@LogReaderDB

EXEC [distribution]..sp_update_agent_profile
 @agent_type =2
    ,@agent_id = @AgentID
    ,@profile_id = @ProfileID

--Restart SourceDB Log Reader Agent

可用於檢查SQL Replication Agent Profile的腳本

--Check Log Reader Profile
select a.name, a.publisher_db, b.profile_name
from distribution..MSlogreader_agents a
INNER JOIN
    msdb.[dbo].[MSagent_profiles] b
    ON a.profile_id = b.profile_id

	
--Check Distrubution Profile

SELECT
    [publication] as Publication
    ,c.srvname as SubscriberName 
    ,b.profile_name as Profile
    ,[name] as DistributionJobName
FROM 
    [distribution].[dbo].[MSdistribution_agents] a
INNER JOIN
    msdb.[dbo].[MSagent_profiles] b
    ON a.profile_id = b.profile_id
INNER JOIN 
    master..sysservers c
    ON a.subscriber_id = c.srvid
ORDER BY 
    b.profile_name;

	
--Check Agent Profile
EXEC sp_help_agent_profile

EXEC sp_help_agent_parameter 22


--
select a.name,p.profile_name,p.def_profile, par.parameter_name,par.value 
from distribution.dbo.MSdistribution_agents a
join msdb.[dbo].[MSagent_profiles] p
on a.profile_id = p.profile_id
join msdb.dbo.[MSagent_parameters] par
on p.profile_id=par.profile_id

--

select * from msdb.[dbo].[MSagent_profiles]
select * from msdb.[dbo].[MSagent_parameters]
select * from msdb.[dbo].[MSagentparameterlist]

--

複寫參數設定參考
https://docs.microsoft.com/zh-tw/sql/relational-databases/replication/agents/replication-agent-profiles?view=sql-server-2017

https://www.sqlservergeeks.com/sql-server-fine-tuning-transactional-replication-performance-part-2/

Troubleshooting transactional replication latency issues in SQL Server
https://www.mssqltips.com/sqlservertip/3598/troubleshooting-transactional-replication-latency-issues-in-sql-server/

其他文章參考
https://bartoszlewandowski.blog/2018/12/04/replication-agent-profiles-explained-part-1/

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com 標誌

您的留言將使用 WordPress.com 帳號。 登出 /  變更 )

Google photo

您的留言將使用 Google 帳號。 登出 /  變更 )

Twitter picture

您的留言將使用 Twitter 帳號。 登出 /  變更 )

Facebook照片

您的留言將使用 Facebook 帳號。 登出 /  變更 )

連結到 %s