Log Reader in Activity Monitor

[Joe]

Hello!

I observe and see that the Log Reader Agent which runs on the Publisher
server consume more and more resources. For example, when I monitor the
server using Activity Monitor, I see that Log Reader Job' s CPU value: 416551
and IO value: 450093. I see that these values are continually increasing. Is
this a normal behaviour? If not what am I supposed to do?

Distributor is the Subscriber and transactions are pulled by the distributor
from the Publisher.

SQL Server 2005 SP2 CU10 - Windows Server 2003 R2 SP2

P.S.
Do I have a chance to pull transactions from the publisher to the
distributor every hour instead of continually?

Thank you,
Joe

[Hilary Cotter]

You can schedule the log reader to run at whatever schedule you want.
Continuously, every minute, 10 minutes, every hour.

The values for cpu are cumulative.

You might want to query msdistribution_status in the distribution database
and group by article. This way you can tell where all the replication
activity is coming from. Then note the commands/transactions comming from
this table(s), and set readbatchsize and readbatchthreshold to roughly match
this.

Post by Joe
Hello!
I observe and see that the Log Reader Agent which runs on the Publisher
server consume more and more resources. For example, when I monitor the
server using Activity Monitor, I see that Log Reader Job' s CPU value: 416551
and IO value: 450093. I see that these values are continually increasing. Is
this a normal behaviour? If not what am I supposed to do?
Distributor is the Subscriber and transactions are pulled by the distributor
from the Publisher.
SQL Server 2005 SP2 CU10 - Windows Server 2003 R2 SP2
P.S.
Do I have a chance to pull transactions from the publisher to the
distributor every hour instead of continually?
Thank you,
Joe

[Joe]

Then PollingInterval property has nothing to do with scheduling Replication?

There are three jobs running at the publisher and these are Log Reader,
Distributor and Snaphot. I don't care about the Snapshot as I've set up this
environment using a backup and without a snapshot. But how about the other
two?
Am I supposed to change their Schedule properties' s Schedule type to:
"Recurring" and configuring the frequency accordingly and everything is OK?
Also, am I supposed to perform this for Log Reader and Distributor or only
for Log Reader?

And I think I'll need to stop and restart the job(s) after the new settings
as it'll still keep running and I don't think it'll use the new settings
without a restart for the jobs?

Thanks for the valuable info,
Joe

Post by Hilary Cotter
You can schedule the log reader to run at whatever schedule you want.
Continuously, every minute, 10 minutes, every hour.
The values for cpu are cumulative.
You might want to query msdistribution_status in the distribution database
and group by article. This way you can tell where all the replication
activity is coming from. Then note the commands/transactions comming from
this table(s), and set readbatchsize and readbatchthreshold to roughly match
this.

Post by Joe
Hello!
I observe and see that the Log Reader Agent which runs on the Publisher
server consume more and more resources. For example, when I monitor the
server using Activity Monitor, I see that Log Reader Job' s CPU value: 416551
and IO value: 450093. I see that these values are continually increasing. Is
this a normal behaviour? If not what am I supposed to do?
Distributor is the Subscriber and transactions are pulled by the distributor
from the Publisher.
SQL Server 2005 SP2 CU10 - Windows Server 2003 R2 SP2
P.S.
Do I have a chance to pull transactions from the publisher to the
distributor every hour instead of continually?
Thank you,
Joe

[Hilary Cotter]

Polling interval determines how long the log reader will rest before reading
the log again. The way it works is the log reader starts up, it reads all
the transactions from the log until it has read all the transactions from
the log and then it goes to sleep for the length of time set in the polling
interval.

If it keeps on finding transactions in the log it will never go to sleep.

So for databases with continual activity, or continual dml on published
tables the log reader will always be working, and the polling interval will
be irrelevant.

Post by Joe
Then PollingInterval property has nothing to do with scheduling Replication?
There are three jobs running at the publisher and these are Log Reader,
Distributor and Snaphot. I don't care about the Snapshot as I've set up this
environment using a backup and without a snapshot. But how about the other
two?
"Recurring" and configuring the frequency accordingly and everything is OK?
Also, am I supposed to perform this for Log Reader and Distributor or only
for Log Reader?
And I think I'll need to stop and restart the job(s) after the new settings
as it'll still keep running and I don't think it'll use the new settings
without a restart for the jobs?
Thanks for the valuable info,
Joe

Post by Hilary Cotter
You can schedule the log reader to run at whatever schedule you want.
Continuously, every minute, 10 minutes, every hour.
The values for cpu are cumulative.
You might want to query msdistribution_status in the distribution database
and group by article. This way you can tell where all the replication
activity is coming from. Then note the commands/transactions comming from
this table(s), and set readbatchsize and readbatchthreshold to roughly match
this.

Post by Joe
Hello!
I observe and see that the Log Reader Agent which runs on the Publisher
server consume more and more resources. For example, when I monitor the
server using Activity Monitor, I see that Log Reader Job' s CPU value: 416551
and IO value: 450093. I see that these values are continually
increasing.
Is
this a normal behaviour? If not what am I supposed to do?
Distributor is the Subscriber and transactions are pulled by the distributor
from the Publisher.
SQL Server 2005 SP2 CU10 - Windows Server 2003 R2 SP2
P.S.
Do I have a chance to pull transactions from the publisher to the
distributor every hour instead of continually?
Thank you,
Joe

[Joe]

Thanks for the explanation.

However I still wonder how to set up Log Reader' s agent to run once every
hour instead of continually.

I've set its Schedule to:
Occurs every day every 1 hour(s) between 16:29:31 and 16:30:31. Schedule
will be used starting on 07.01.2009. And changed the Schedule Type to
Recurring.

However it still runs continuously. What am I missing here?

Thanks indeed,
Joe

Post by Hilary Cotter
Polling interval determines how long the log reader will rest before reading
the log again. The way it works is the log reader starts up, it reads all
the transactions from the log until it has read all the transactions from
the log and then it goes to sleep for the length of time set in the polling
interval.
If it keeps on finding transactions in the log it will never go to sleep.
So for databases with continual activity, or continual dml on published
tables the log reader will always be working, and the polling interval will
be irrelevant.

[Hilary Cotter]

Edit the job step run agent. Remove the -Continuous command from the step.
It should be the last parameter.

Post by Joe
Thanks for the explanation.
However I still wonder how to set up Log Reader' s agent to run once every
hour instead of continually.
Occurs every day every 1 hour(s) between 16:29:31 and 16:30:31. Schedule
will be used starting on 07.01.2009. And changed the Schedule Type to
Recurring.
However it still runs continuously. What am I missing here?
Thanks indeed,
Joe

Post by Hilary Cotter
Polling interval determines how long the log reader will rest before reading
the log again. The way it works is the log reader starts up, it reads all
the transactions from the log until it has read all the transactions from
the log and then it goes to sleep for the length of time set in the polling
interval.
If it keeps on finding transactions in the log it will never go to sleep.
So for databases with continual activity, or continual dml on published
tables the log reader will always be working, and the polling interval will
be irrelevant.

[Hilary Cotter]

BTW - I don't really advise scheduling the log reader agent. You will not be
able to backup the transaction log completely until the log reader has run.
I think you will find your tlog sizes increasing significantly.

Post by Joe
Thanks for the explanation.
However I still wonder how to set up Log Reader' s agent to run once every
hour instead of continually.
Occurs every day every 1 hour(s) between 16:29:31 and 16:30:31. Schedule
will be used starting on 07.01.2009. And changed the Schedule Type to
Recurring.
However it still runs continuously. What am I missing here?
Thanks indeed,
Joe

Post by Hilary Cotter
Polling interval determines how long the log reader will rest before reading
the log again. The way it works is the log reader starts up, it reads all
the transactions from the log until it has read all the transactions from
the log and then it goes to sleep for the length of time set in the polling
interval.
If it keeps on finding transactions in the log it will never go to sleep.
So for databases with continual activity, or continual dml on published
tables the log reader will always be working, and the polling interval will
be irrelevant.

[Joe]

Post by Hilary Cotter
BTW - I don't really advise scheduling the log reader agent. You will not be
able to backup the transaction log completely until the log reader has run.

What do you exactly? Do you mean the transaction log will not be truncated?
Which is understandable as there will be transactions which are waiting to be
replicated. Is this the only reason you do not recommend changing the
configuration of the Log Reader?

P.S.
Transaction Log backups are taken every 15 minutes.

Thanks
Joe

Post by Hilary Cotter
BTW - I don't really advise scheduling the log reader agent. You will not be
able to backup the transaction log completely until the log reader has run.
I think you will find your tlog sizes increasing significantly.

[Hilary Cotter]

The sequence is that the inactive transactions in the log will not be
cleared until they are read from the transaction log by the log reader agent
and then a transaction log back up is done. I have no idea how frequently
you backup the log right now, but it will have to be at the same frequency
as the log reader agent and be done after the log reader agent has done its
work. Most people backup their transaction log for point in time recovery.
Your point in time recovery goes from whatever you have now to at least 1
hour. If you are using replication for your dr solution your exposure to
data loss is well over 1 hour now.

However if you don't care about your exposure to data loss, this is
acceptable to you. Again it all depends on what your goal is.

At the start of this post you say "I observe and see that the Log Reader
Agent which runs on the Publisher
server consume more and more resources. For example, when I monitor the
server using Activity Monitor, I see that Log Reader Job' s CPU value:
416551
and IO value: 450093. I see that these values are continually increasing. Is
this a normal behaviour? If not what am I supposed to do?"

I have mentioned that these counters are cumulative. For example right now
the CPU value for the log reader agent is 416551, one second later it might
be 416600. These are not counters I pay any attention to.

How you done any analysis to determine that this is causing performance
degradation on your SQL Server and is the primary bottleneck?

It seems that your goal is to lessen the impact of the log reader agent and
I don't really understand why. The log reader agent is almost never a
bottleneck. In most replication systems the dba focuses on the distribtuion
agent as that is where the latency problems typically lie. The log reader
agent is almost always up to date.

In most replication topologies DBA focus on latency or throughput. Having
the log reader agent run every hour will not help latency at all. If you
don't care about latency (how up to date your subscriber is), this may work
for you - and I am sure there are use cases out there which make this a
goal.

However I am just confused about your goals, and how we are meeting them by
having the log reader agent running each hour.

Post by Joe

Post by Hilary Cotter
BTW - I don't really advise scheduling the log reader agent. You will not be
able to backup the transaction log completely until the log reader has run.

What do you exactly? Do you mean the transaction log will not be truncated?
Which is understandable as there will be transactions which are waiting to be
replicated. Is this the only reason you do not recommend changing the
configuration of the Log Reader?
P.S.
Transaction Log backups are taken every 15 minutes.
Thanks
Joe

Post by Hilary Cotter
BTW - I don't really advise scheduling the log reader agent. You will not be
able to backup the transaction log completely until the log reader has run.
I think you will find your tlog sizes increasing significantly.

[Chuby Doesy]

Hi Hilary,
sorry...I know this is a year back so the response is outdated or irrevelant likely. Thought I'd give some background to what I'm doing also.

I'm trying to do the same thing ie schedule the log reader agent so it's not "continuous". Short of disabling replication altogether, it's for the purpose of ensuring BCP type operations on the publication system are not affected by the IO reads caused by the log reader agent AT A PARTICULAR POINT IN TIME.

What I'm seeing now with my BCP operations is a doubling of the elapsed completion time. So what took 3 hours to do, now takes 6 hours.

IO, Latency, etc... they are all affected at the publisher and subscriber systems. Maybe a different approach is required...updateable transactions but I guess that's another conversation. Thanks

Post by Joe
Hello!
I observe and see that the Log Reader Agent which runs on the Publisher
server consume more and more resources. For example, when I monitor the
server using Activity Monitor, I see that Log Reader Job' s CPU value: 416551
and IO value: 450093. I see that these values are continually increasing. Is
this a normal behaviour? If not what am I supposed to do?
Distributor is the Subscriber and transactions are pulled by the distributor
from the Publisher.
SQL Server 2005 SP2 CU10 - Windows Server 2003 R2 SP2
P.S.
Do I have a chance to pull transactions from the publisher to the
distributor every hour instead of continually?
Thank you,
Joe

Post by Hilary Cotter
You can schedule the log reader to run at whatever schedule you want.
Continuously, every minute, 10 minutes, every hour.
The values for cpu are cumulative.
You might want to query msdistribution_status in the distribution database
and group by article. This way you can tell where all the replication
activity is coming from. Then note the commands/transactions comming from
this table(s), and set readbatchsize and readbatchthreshold to roughly match
this.

Post by Joe
Then PollingInterval property has nothing to do with scheduling Replication?
There are three jobs running at the publisher and these are Log Reader,
Distributor and Snaphot. I don't care about the Snapshot as I've set up this
environment using a backup and without a snapshot. But how about the other
two?
"Recurring" and configuring the frequency accordingly and everything is OK?
Also, am I supposed to perform this for Log Reader and Distributor or only
for Log Reader?
And I think I'll need to stop and restart the job(s) after the new settings
as it'll still keep running and I don't think it'll use the new settings
without a restart for the jobs?
Thanks for the valuable info,
Joe

Post by Hilary Cotter
Polling interval determines how long the log reader will rest before reading
the log again. The way it works is the log reader starts up, it reads all
the transactions from the log until it has read all the transactions from
the log and then it goes to sleep for the length of time set in the polling
interval.
If it keeps on finding transactions in the log it will never go to sleep.
So for databases with continual activity, or continual dml on published
tables the log reader will always be working, and the polling interval will
be irrelevant.

Post by Joe
Thanks for the explanation.
However I still wonder how to set up Log Reader' s agent to run once every
hour instead of continually.
Occurs every day every 1 hour(s) between 16:29:31 and 16:30:31. Schedule
will be used starting on 07.01.2009. And changed the Schedule Type to
Recurring.
However it still runs continuously. What am I missing here?
Thanks indeed,
Joe

Post by Hilary Cotter
Edit the job step run agent. Remove the -Continuous command from the step.
It should be the last parameter.

Post by Hilary Cotter
BTW - I don't really advise scheduling the log reader agent. You will not be
able to backup the transaction log completely until the log reader has run.
I think you will find your tlog sizes increasing significantly.

Post by Joe
What do you exactly? Do you mean the transaction log will not be truncated?
Which is understandable as there will be transactions which are waiting to be
replicated. Is this the only reason you do not recommend changing the
configuration of the Log Reader?
P.S.
Transaction Log backups are taken every 15 minutes.
Thanks
Joe

Post by Hilary Cotter
The sequence is that the inactive transactions in the log will not be
cleared until they are read from the transaction log by the log reader agent
and then a transaction log back up is done. I have no idea how frequently
you backup the log right now, but it will have to be at the same frequency
as the log reader agent and be done after the log reader agent has done its
work. Most people backup their transaction log for point in time recovery.
Your point in time recovery goes from whatever you have now to at least 1
hour. If you are using replication for your dr solution your exposure to
data loss is well over 1 hour now.
However if you don't care about your exposure to data loss, this is
acceptable to you. Again it all depends on what your goal is.
At the start of this post you say "I observe and see that the Log Reader
Agent which runs on the Publisher
server consume more and more resources. For example, when I monitor the
416551
and IO value: 450093. I see that these values are continually increasing. Is
this a normal behaviour? If not what am I supposed to do?"
I have mentioned that these counters are cumulative. For example right now
the CPU value for the log reader agent is 416551, one second later it might
be 416600. These are not counters I pay any attention to.
How you done any analysis to determine that this is causing performance
degradation on your SQL Server and is the primary bottleneck?
It seems that your goal is to lessen the impact of the log reader agent and
I don't really understand why. The log reader agent is almost never a
bottleneck. In most replication systems the dba focuses on the distribtuion
agent as that is where the latency problems typically lie. The log reader
agent is almost always up to date.
In most replication topologies DBA focus on latency or throughput. Having
the log reader agent run every hour will not help latency at all. If you
don't care about latency (how up to date your subscriber is), this may work
for you - and I am sure there are use cases out there which make this a
goal.
However I am just confused about your goals, and how we are meeting them by
having the log reader agent running each hour.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Review of Redgate ANTS Performance Profiler 6
http://www.eggheadcafe.com/tutorials/aspnet/945b0f4a-55b9-4799-aaa3-bcbed4131446/review-of-redgate-ants-performance-profiler-6.aspx