Replication taking longer

I've got once a day snapshot replication working between 2 servers. I
take a copy of most of a database for MI and adhoc query processing
purposes. This has been working okay for several months now but I've
noticed that the time to load the database is gradually taking longer.
I don't think this is a product of the source database growing, because
whilst it has grown by 2-3%, the time taken to load the target database
is taking 80-90% longer than it did a month ago. I thought this may be
due to some physical file structure problem and I reindexed all tables
with no effect. I've also noticed that a DTS package that I'm using to
copy a much smaller database across to the same MI server has also
increased in duration over the same time period.
Any ideas to solve this would be welcome.
TIA
Laurence Breeze
Laurence,
indexing the tables wouldn't speed up the snapshot application at the
subscriber - actually quite the opposite . Perhaps you can monitor which
part of the process is taking more time than expected. You could just
monitor it using dbcc inputbuffer at the subscriber while it processes to
get a feel for it. If the DTS task has increased in time then it could be
network issues or the hardware on the subscriber. Loads of possibilities
here. You could copy a file to the server to test the network transfer
speed. I'd also monitor the disk writes/sec as maybe the problem lies there.
Disk fragmentation could be worth looking at, but I wouldn't expect this to
be such a major factor. Finally, check the general performance of the server
outside of data transfers - just adding data to a dummy table - and compare
to another (similar) server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)