SQL Replication Commands

Question

I'm trying to figure out how i can get the number of commands over the last day for a sql server replication. Transactional / Snapshot / Merge.

I really want to be able to calculate the average number of new sql commands per min and latency per min but i should be able to calculate that if i can understand how to identify new commands for a replication from its history.

i have query that i have shown below but i'm not sure if i'm on the right path.

select 
his.delivered_commands as [NewCommands],
his.time as [time],
his.comments,
HIS.*
from distribution..MSdistribution_history his
inner join distribution.dbo.MSdistribution_agents mda on his.agent_id = mda.id 
where mda.subscriber_db<>'virtual' and mda.publication = 'SomeReplication'
order by his.time desc

Can anyone please help on this one perhaps as i'm completely stumped.

I am using SQL Server 2008 R2.

Iffy.

Answer 1

There are perfmon counters that you can probably use for what you're trying to do. Since it's a counter provided by SQL, it's available in sys.dm_os_performance_counters. Try select * from sys.dm_os_performance_counters where object_name like '%repl%'. Slice and dice as necessary.

Answer 2

You can find some information using sys.dm_repl_tranhash and sys.dm_repl_traninfo DMVs