Link to home
Start Free TrialLog in
Avatar of TRACEYMARY
TRACEYMARY

asked on

Replication uses high cpu

Anyone running replication.

when i query sysprocesses i see high
cpu            
6783530
6390070
5134730
and memory
897318
28904
0
456826


Any way to reduce these or thats just replication.



Avatar of Sowmya_K
Sowmya_K

Databases on all server instances involved in replication must be created with logging.

It is recommended that you replicate tables only from databases created with unbuffered logging.  Replication evaluates the logical log for transactions that modify tables defined for replication. If a table defined for replication resides in a database that uses buffered logging, the transactions are not immediately written to the logical log, but are instead buffered and then written to the logical log in a block of logical records. When this occurs, Replication evaluates the buffer of logical-log records all at once, which consumes excess CPU time and memory.
To create a database with unbuffered logging, use:
CREATE DATABASE db_name WITH LOG

Hope this solves your problem
Avatar of TRACEYMARY

ASKER

Im using the actual sql replication
tools ...replication.

You can enhance the general performance for all types of replication in SQL server by any of these following steps:

•Setting a minimum amount of memory allocated to Microsoft SQL Server.
•Using a separate disk drive for the transaction log for all databases involved in replication.
•Consider adding memory to servers used in replication.
•Using multiprocessor computers.
•Setting a fixed size for the distribution database.
•Publishing only the amount of data required.
•Running the Snapshot Agent only when necessary and at off-peak times.
•Placing the snapshot folder on a drive not used to store database or log files.
•Using a single snapshot folder per publication.
•Consider using compressed snapshot files.
•Reducing the distribution frequency when replicating to numerous Subscribers.
•Consider use of pull or anonymous subscriptions.
•Reduce the verbose level of replication agents to '0' except during initial testing, monitoring, or debugging.
•Run agents continuously instead of on very frequent schedules.
•Consider using the –UseInprocLoader agent property.

Hope this helps.

•Using a separate disk drive for the transaction log for all databases involved in replication.
  This you mean the distribution

•Setting a fixed size for the distribution database.
  Where you fix this....and what happens if need more will it just halt.

•Reducing the distribution frequency when replicating to numerous Subscribers.
  This i believe we have on continuous loop.
  -Subscriber [AS2] -SubscriberDB [DeltekCP] -Publisher [AS1] -  
   Distributor [AS1] -DistributorSecurityMode 1 -PublisherDB [ASDB]  -Continuous
   So remove continuous ?


•Consider using the –UseInprocLoader agent property.
  I read this but could not determine where to put it ?
--> You can decrease the time it takes to write transactions by storing the log files on a disk drive different than the one used to store the database. You can mirror that drive, using a Redundant Array of Inexpensive Disks (RAID)-1
--> The –UseInprocLoader parameter can be passed to the Distribution Agent when applying the initial snapshot at the Subscriber. When you use this parameter, the Distribution Agent will use the in-process BULK INSERT operation, decreasing the amount of time taken to apply the snapshot. To enhance performance further, use –UseInprocLoader in conjunction with –MaxBCPThreads.
The snapshot occurs only once is that right the ....do you have that on your machine can you post the dts script with it in.
Thanks

I have the MaxBCPThreads at 16

The snapshot is replication to distribution

Server1 to distribution to Server2
ASKER CERTIFIED SOLUTION
Avatar of Sowmya_K
Sowmya_K

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks i do some digging around.