Discussion:
SQL 2005 Replication - Querying msdistribution_status slow
(too old to reply)
Ben Anderson
2006-09-05 08:56:24 UTC
Permalink
Hi,

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
follows:

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?

Thanks,

Ben Anderson
Paul Ibison
2006-09-05 09:16:39 UTC
Permalink
Ben,
the view definition of 'MSdistribution_status' is identical across versions,
so it can't be a problem in that sense.
Is the amount of commands comparable in the 2 systems?
Are you getting any blocking when you run the query on the 2005 system?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Ben Anderson
2006-09-05 09:33:33 UTC
Permalink
Hey Paul,

There's no bloking going on at all. I used to get some blocking with
the log reader vs. the distribution cleanup but thats to be expected,
and I've resolved that.

In comparing load, both distributors are pushign somewhere in the
region of 10 million + changes / new rows a day I would say, but the
tables in the 2005 publications are a lot narrower in row length than
the tables in 2000, so I would expect this box to be able to push
quicker. I have found replication to be slower in general in 2005 so
far. Obviously I need to sit down and test all the tweaks that can be
made to profiles etc, but not being able to query the status of
replication is a big brick wall in being able to do this as you can
imagine.

There are though 3.5 million rows (1GB data including indexes) in
MSrepl_commands, and 15,000 in MSrepl_transactions, so I would expect
querying the table to be slower, but not hours?

Regards,

Ben Anderson
Post by Paul Ibison
Ben,
the view definition of 'MSdistribution_status' is identical across versions,
so it can't be a problem in that sense.
Is the amount of commands comparable in the 2 systems?
Are you getting any blocking when you run the query on the 2005 system?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Paul Ibison
2006-09-05 09:52:33 UTC
Permalink
Ben,
we must be missing something here. Your server is performing fine from the
point of view of processor, disk and RAM and yet the performance is
strangely poor, while the system tables are just as full. Perhaps you could
look at the execution plan of the view (below) in both SQL Server 2000 and
SQL Server 2005 and find which step is consuming differently.
Also, can you update the statistics (update statistics MSrepl_commands;
update statistics MSrepl_transactions) and free the cache (DBCC
FREEPROCCACHE) on SQL Server 2005 to ensure that the plans are correct then
look at the execution plan and run the query to see if it has improved.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

SELECT t.article_id,s.agent_id,
'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0
END),
'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)
FROM (SELECT article_id,publisher_database_id, xact_seqno
FROM MSrepl_commands (NOLOCK) ) as t
JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions
(NOLOCK) ) AS s
ON (t.article_id = s.article_id AND
t.publisher_database_id=s.publisher_database_id )
JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM
MSdistribution_history (NOLOCK) GROUP BY agent_id) as h
ON (h.agent_id=s.agent_id)
GROUP BY t.article_id,s.agent_id
Ben Anderson
2006-09-05 12:08:00 UTC
Permalink
Hey Paul,

Thanks for these tips. They seem to have done the trick!

As I didn't want to wait hours for the original plan to come back I
compared it to the estimates 2005 plan. They were different in that
more IO was required to query the history table containing 29 rows than
the repl_commands table which was odd.

After runnign undate stats and flushing the cache the query now returns
in 45 seconds so maybe it was a bad plan that caused this? I guess
that maybe stats aren't updating as frequently as they should either so
I will force this to happen daily.

Thanks a lot with your help on this one - its been driving me crazy!

Regards,

Ben Anderson.
Post by Paul Ibison
Ben,
we must be missing something here. Your server is performing fine from the
point of view of processor, disk and RAM and yet the performance is
strangely poor, while the system tables are just as full. Perhaps you could
look at the execution plan of the view (below) in both SQL Server 2000 and
SQL Server 2005 and find which step is consuming differently.
Also, can you update the statistics (update statistics MSrepl_commands;
update statistics MSrepl_transactions) and free the cache (DBCC
FREEPROCCACHE) on SQL Server 2005 to ensure that the plans are correct then
look at the execution plan and run the query to see if it has improved.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
SELECT t.article_id,s.agent_id,
'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0
END),
'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)
FROM (SELECT article_id,publisher_database_id, xact_seqno
FROM MSrepl_commands (NOLOCK) ) as t
JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions
(NOLOCK) ) AS s
ON (t.article_id = s.article_id AND
t.publisher_database_id=s.publisher_database_id )
JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM
MSdistribution_history (NOLOCK) GROUP BY agent_id) as h
ON (h.agent_id=s.agent_id)
GROUP BY t.article_id,s.agent_id
Loading...