Performance Tuning Transactional Replication

Hello,
I have configured replication in a test lab using three identical two processor based servers. One server is being used as the distributor. I am running an application on a fourth machine(laptop) that is directly connected to the Source Server. This appli
cation inserts records into the source database at a designated rate. when I query against the source and target tables simultaneaously, there is huge latency in the number of records that is being replicated (Target is minutes behind the source). I have
modified the the log agent and distribution agent properties (polling interval, the maxbcpthreads, etc.). After doing that, I did notice some improvement but it is not enough. I need to have the two database synchronized within seconds of each other. Is t
here any other modifications that I can make to speed up the synchronization of data? By the time I have inserted 10K records into the source, the target has only half 7K.
it is possible that you are loading your server too much. Latency is a
function of throughput, the more through put you put on your system the
greater your overall latency.
Can you run this command in your distribution database so we can get an idea
of undelivered commands?
select * from MSdistribution_status
Also can you check to see if you can use the replication of stored
procedure's execution. This can radically improve performance.
"Nupee" <anonymous@.discussions.microsoft.com> wrote in message
news:7B6AC640-D49F-4EB7-8EBF-4C394D6D0F5F@.microsoft.com...
> Hello,
> I have configured replication in a test lab using three identical two
processor based servers. One server is being used as the distributor. I am
running an application on a fourth machine(laptop) that is directly
connected to the Source Server. This application inserts records into the
source database at a designated rate. when I query against the source and
target tables simultaneaously, there is huge latency in the number of
records that is being replicated (Target is minutes behind the source). I
have modified the the log agent and distribution agent properties (polling
interval, the maxbcpthreads, etc.). After doing that, I did notice some
improvement but it is not enough. I need to have the two database
synchronized within seconds of each other. Is there any other modifications
that I can make to speed up the synchronization of data? By the time I have
inserted 10K records into the source, the target has only half 7K.

Performance Problem - Request for advice

I am a DBA working for a large business in the North East of England.

One of our main Database servers (SQL Server 2000 SP4) is configured as follows:

Storage - 1. Logical Drive configured as RAID5 with about 800MB of storage, 40% free space. Used for main database files. 8 Physical drives.
- 2. Logical Drive configured as RAID1 with about 140MB of storage, 20% free space. Used for sql log files.

Both 1 and 2 are on the same controller (SCSI).

- 3. System Drive (C:)

Storage configuration was done by an external consultancy and disk performance (when benchmarked) was excellent.

Memory - 8GB of memory 6.5GB allocated to SQL Server
Operating System: Windows server 2003 Enterprise Edition
SQL Server: 2000 SP4

We would appreciate your advice regarding the following problem that we have on busy times:

Typical number of users/connections to SQL Server is between 200 to 250 (sysprocesses)
Typical number of active processes can vary between 5 to 30 (all sorts of applications)

The problem we have is that on some busy days a combination of processes grinding the server to a halt.
When we check the activity (normally using sp_who2) we can see that there are no blocked processes, cpu and DiskIO progressing as normal
(some values maybe large but not beyond expectation).


However, when "the problem" occurs, everything (queries, updates, etc) is extremely slow for 5-20 minutes until the bottleneck is freed.

We would appreciate any advice (tempdb?, disk bottleneck?, etc) and recommendation for any useful tools.

Thank you,You say when the bottleneck is freed...do you know what it is? Do you assume that it's freed because performance come back.

IMHO, I would not mess with memory allocation. I would let SQL Server do whatever it wants

BTW, the number of users/connections seems pretty low.

My guess is that there is some background batch job kibking off during that time which is a pig....|||Hows your tempdb during the time of extremely slow?|||Other simple stuff,

Are your statistics upto date on tables?
Is your disk storage fragmented?
Any bad disk warnings from the raid control?

The memory allocation for db growth (i.e 10% or 10MB) in the Transaction log can bugger it up too depending on it's growth and backups.

..and the killer... any virus checking going on?
I've had issues where {insert top end brand name here} have caused massive performance reductions due to unnecessary scheduled scans (company policy) or signature update downloads.

Is someone starting up a games server on the box?

Cheers
Phil

--
Special one just for you:
Police in Yorkshire are concerned about an alarming rise in the new trend of drug takers using dentists's syringes to inject drugs directly into the mouth.
They have described this tactic as "E by Gum":shocked: