Friday, 6 February 2015

The SQL below shows the replication performance for a table.

use distribution
go

select min(dh.delivery_latency) min, avg(dh.delivery_latency) avg, MAX(dh.delivery_latency) max
from MSarticles a 
inner join MSpublications (nolock) p on a.publication_id = p.publication_id
inner join MSsubscriptions (nolock) s ON p.publication_id = s.publication_id and s.article_id = a.article_id
inner join master..sysservers (nolock) ss ON s.subscriber_id = ss.srvid 
inner join master..sysservers (nolock) srv ON srv.srvid = p.publisher_id 
inner join MSdistribution_agents (nolock) da ON da.publisher_id = p.publisher_id  AND da.subscriber_id = s.subscriber_id and da.publication = p.publication
inner join distribution.dbo.MSdistribution_history (nolock) dh on dh.agent_id = da.id
where a.destination_object = 'MyTable'
and s.subscriber_db = 'MyDB'

No comments:

Post a Comment