2006-09-05 08:56:24 UTC
I'm running a SQL 2005 server as a standalone distributor which
forwards data to several subscribers, from two publishers. For my
performance monitoring application I run a proc every 10 minutes to
check the status of the commands in the distribution database as
select @@Servername,a.publication + ':' + s.srvname as [Agent],
v.undelivcmdsindistdb, v.delivcmdsindistdb, a.id
from distribution.dbo.msdistribution_status v
join distribution.dbo.msdistribution_agents a on v.agent_id = a.id
join master.dbo.sysservers s on a.subscriber_id = s.srvid
where v.undelivcmdsindistdb > 0
This will give me data for anything that hasn't deleivered all of its
commands yet. The problem is, that on my SQL 2000 distributor this
runs in less than a second, but on the 2005 distributor it takes approx
10 hours to complete, yet the server isn't maxed out, with no more than
20% cpu, 0 paging, disk queues of maybe 2 or 3 across all disks.
On debugging this its caused by the view msdistribution_status.
Does anyone know of this problem or a work around to this?