[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Replication uses high cpu

Posted on 2006-05-17
8
Medium Priority
?
757 Views
Last Modified: 2006-11-18
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.



0
Comment
Question by:TRACEYMARY
  • 4
  • 4
8 Comments
 
LVL 4

Expert Comment

by:Sowmya_K
ID: 16706799
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
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16708178
Im using the actual sql replication
tools ...replication.

0
 
LVL 4

Expert Comment

by:Sowmya_K
ID: 16708403
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.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16708695
•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 ?
0
 
LVL 4

Expert Comment

by:Sowmya_K
ID: 16716049
--> 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.
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16724208
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
0
 
LVL 4

Accepted Solution

by:
Sowmya_K earned 2000 total points
ID: 16731771
Sorry , i do not have those scripts  with me. I worked with it in my previous project.

I think your snapshot rate is fine.

The performance benefit from using –MaxBCPThreads also depends on the number of processors on the server. Specifying a high number for –MaxBCPThreads can overburden the system, because the system must spend too much time managing threads. Using more threads than the total number of articles provides no additional benefit.

I suggest you to spend sometime reading the article http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql2k_replperf_tran4.asp 
It will help you understand the significance of these factors vey well (Experience speaks ;-)
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 16734048
Thanks i do some digging around.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question