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

在Replication Monitor 的Subscriptions 右鍵開啟Agent Profile
按New產生新的Agent Profile，因為Default Agent Profile 是沒辦法調整的
命名並調整參數

參數調整
- commitbatchsize >> 2000
- commitbatchthreshold >> 4000
- historyverboselevel >> 2
- KeepAliveMessageInterval >> 600
- pollinginterval >> 5
- querytimeout >> 3600
新增完後勾選使用新建的Agent Profile — High Transaction Distribution
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

在Replication Monitor 的Agents下右鍵開啟Log Agent Profile
按New產生新的Agent Profile
命名並調整參數

參數調整
- LogScanThreshold>> 1000000
- ReadBatchSize >> 2000
- querytimeout >> 3600
新增完後勾選使用新建的Agent Profile — High Transaction LogReader
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/