February 24, 2011 at 10:11 am
Hello Gurus,
I will try to explain my problem as clearly as I can... please be patient until the end of the post.
We are running a job to delete certain no.of rows in a table every day. we are facing blocking in our environment when this job runs. I have researched online about is_read_committed_snapshot_on. As per my understanding, if we change this from 0 to 1, row versioning will be in effect and will avoid locks in DB.
Now, my question is, the server in the question is a publisher and a subscriber(different databases for pub and sub..transactional replication). Also, we have log shipping going on to a different server from this server.
from your experience, can please you suggest me, if I change the is_read_committed_snapshot_on from 0 to 1, will this effect the high availabilty?
please post a reply at your earliest convinience.
Thanks
February 25, 2011 at 3:25 am
I don't think it would impact high availability. I remember sometime last year I had faced this issue on one of the servers where transactional replication was configured. It didn't have an impact on changing the isolation level.
M&M
February 25, 2011 at 3:58 am
February 25, 2011 at 4:15 am
Instead of changing the isolation level: look at your delete code, in particular: execution plans.
Does the delete query hit appropriate indexes?
Are your statistics up to date?
Carlton.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply