高頻交易在使用交易式複寫時，如果使用的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://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/
DBA 黑白講 