March 19, 2009 at 9:38 am
Hi,
I have a transactional replication setup between 2 SQL 2000 Server
64bit Enterprise Endition. the database on subscriber we are using
for read only (our reports run from it).
Every time the heavy query is executed to generate a report on a
subscriber the replication times out and CPU usage on the server goes
through the roof and all get's locked up. We have to start killing
SPIDs in order to release the SQL Server from that state.
Anything that I can do to remediate this?
We are not using SQL Reporting Services - reports are being generated
from the Java application.
I don't see any errors on the drives though they are in RAID 5 (for
both DATA and LOGS).
Do I have to rebuild indexes on the replicated database?
Thank you,
T.
March 19, 2009 at 5:34 pm
"When the snapshot is applied at the Subscriber, the Distribution Agent first applies the snapshot files (schema and .bcp files). It then reconciles each captured transaction to see if it has already been delivered to the Subscriber. During this reconciliation process, the tables on the Subscriber are locked. Depending on the number of transactions captured at the Publisher while the snapshot was created, you should expect an increase in the amount of time required to apply the snapshot at the Subscriber. Conceptually, this is similar to the process of recovery that SQL Server uses when it is restarted." BOL Ref.
So in your subscriber to take long time processing transactions; you can def look at indexes that will help. Other question to what the report is selecting? If the report is selecting the entire table it will take a temporary shared long for the duration of the select. So that might causing your time out issues.
Other option is to use (nolock) hint in reports; but this can cause dirty reads/double reads... so you will have to way your risks.
How up-to-date the reporting database has to be? If it doesn't have to be up-to-date maybe look at a single snapshot after hours?
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 19, 2009 at 10:06 pm
ya.. using nolock hint and suitable indexes will do for you..
you have to keep in mind that how frequently you run those reports, meaning on daily basis/hourly basis/on demand.
and how frequent the tables are updated. depending on what we can have better plans..
I donot think the snap shot will be running all the time in transactional replication, only Log reader and distribution will be running for updating the subscriber.
Shekhar.. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply