Extracting metrics from Distribution.dbo.MSlogreader_history messages "Approximately 45000000 log records have been scanned in pass # 4, 0 of which were marked for replication."
What % of total log records scanned are marked for replication?
What are the total commands delivered over each delivery interval?
The below script provides insights into a system under heavy insert/update/delete activity, but very little of the Publisher Database Transaction Log Records are marked for replication. If high # records being scanned and a low % are marked for replication, common options to improve Log Reader throughput are increase the ReadBatchSize of the agent, perform re-indexing during maintenance window or re-architect replication and place the articles into their own database if your setup has few tables out of many configured for replication.
Select t1.Time, t1.agent_id as 'AgentID', t2.name as 'Agent Name', Comments, Delivered_Commands, Convert(int,delivered_commands - LAG(delivered_commands, 1,0) OVER (ORDER BY YEAR([time]))) AS 'Commands Added', Substring(comments,Charindex('y',comments)+1, Charindex('log ',comments)-14) as 'Log Records Scanned', LAG(Substring(comments,Charindex('y',comments)+1, Charindex('log ',comments)-14),1,0) OVER (ORDER BY YEAR([time])) as 'Previous Log Records Scanned', Convert(Decimal(18,3),Convert(Decimal(18,3),(delivered_commands - LAG(delivered_commands, 1,0) OVER (ORDER BY YEAR([time]))+1)) / Convert(Decimal(18,3),(LAG(Substring(comments,Charindex('y',comments)+1, Charindex('log ',comments)-14),1,0) OVER (ORDER BY YEAR([time]))+1)) * 100) as '% All T-Log Records marked for REPL' Into #TMP from Distribution.dbo.MSlogreader_history t1 with (nolock) inner join Distribution.dbo.MSlogreader_agents t2 with (nolock) on t1.agent_id = t2.id where t1.agent_id = 8 and comments like '%marked for replication%' order by time asc Select * from #TMP where [% All T-Log Records marked for REPL] < 100.00 Drop Table #TMP
|
|
|
|
|