May 26, 2009 at 5:35 pm
Hi
Just wondered what one can do with a replicated database which is also used for a reporting database as well, the problem now is that the replication is being blocked by reporting queries that users are running.
Is there a way around this problem ?
Thanks
May 26, 2009 at 6:15 pm
I could say:
1. change the schedule for distributor so as to run could be after 1hr(if possible)
2. as you said its a reporting database try to use (NOLOCK) hint *this will give dirty reads but here it should be ok.
3. as usual for all blocking/deadlock short your transactions.
Cheers.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 26, 2009 at 6:54 pm
Vinay Thakur (5/26/2009)
I could say:1. change the schedule for distributor so as to run could be after 1hr(if possible)
2. as you said its a reporting database try to use (NOLOCK) hint *this will give dirty reads but here it should be ok.
3. as usual for all blocking/deadlock short your transactions.
Cheers.
Vinay
1) Billing information needs to be streamed live on the reporting box, so it wouldnt be accepted by the bosses.
2) Same as no 1, would not be acceptable.
I didnt quite understand point 3, can you expand more. all the code within the procedures are not in a transaction, just a usual read and it assumes the default locking behaviour.
May 26, 2009 at 9:00 pm
use snapshot isolation level. *This require more tempdb.... manage tempdb carefully.
1. Try to make the distributor job at least for 15mins.
2. *Snapshot isolation will take care of blocking.
3. in the application dont use more begin and commit logic... try to commit the transactions fast.... as short as possible.
Cheers.
Vinay....
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 27, 2009 at 8:03 am
For Deadlock you have to enable the Trace
http://msdn.microsoft.com/en-us/library/ms178104(SQL.90).aspx
And for trubleshooting:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/05/617960.aspx
HTH
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 27, 2009 at 9:05 am
Vinay Thakur (5/26/2009)
use snapshot isolation level. *This require more tempdb.... manage tempdb carefully.
1. Try to make the distributor job at least for 15mins.
2. *Snapshot isolation will take care of blocking.
3. in the application dont use more begin and commit logic... try to commit the transactions fast.... as short as possible.
Cheers.
Vinay....
Can you explain point 3 please ?
May 27, 2009 at 9:19 am
Deadlock is something like suppose you have 2 sessions running. one session started and working on some tables(a,b,c,...) and other session(s) is also started and running DML on same tables (a,b,c,...) both transactions has BEGIN TRAN and COMMIT TRAN (ROLLBACK)
if you are working with defult isolation level read commited. the behaviour if read commited is it will read the commited data only. so eg.
--Session 1.
Begin tran
update a...
select b
.....
---MEan while session 2 also running
--Session 2
Begin TRan
update b...
select a
the these two transactions one of this will be deadlock victim....
so if we keep our transaction short quick begin end. rather then keeping a big loop for begin end so that until the transaction will not commit/rollback we could be having possiblity of having blocking/deadlock.
Their must be some good eg. online.
HTH
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 27, 2009 at 9:34 am
Since this is a reporting server, I do not think there would be much data updated. So the begin tran and commit tran would not be an issue. You said that the replication is being blocked and not Dead Lock. There is a big difference.
From what I read, you are getting latency because of all the blocking. Row level versioning might work in your case. But I have never tested it on a replicated server.
You could look up this article [/url]if you want to go along with that method.
-Roy
May 27, 2009 at 9:51 am
Roy Ernest (5/27/2009)
Since this is a reporting server, I do not think there would be much data updated. So the begin tran and commit tran would not be an issue. You said that the replication is being blocked and not Dead Lock. There is a big difference.From what I read, you are getting latency because of all the blocking. Row level versioning might work in your case. But I have never tested it on a replicated server.
You could look up this article [/url]if you want to go along with that method.
Its a reporting server and users need to be able to query live accurate data, hence the reason why its replicated (transactional).
Deadlock often occurs with SQL replication updating/inserting on the articles. Any solutions here. Also the distribution agent has to be continuous as well.
May 27, 2009 at 10:14 am
I have seen deadlock happening between the clean up job (distribution clean Up) and replication. That can be solved by using different schedule for the clean up job to run.
-Roy
May 27, 2009 at 10:20 am
right, so there is no solution for solving deadlocking on replicated articles.
May 27, 2009 at 10:43 am
I have seen these dead locks when we had a lousy server. At that time what we did was change the clean up job schedule. This reduced it drastically. Changing the schedule for clean up job will not hurt your replication. There wont be additional latency.
Another thing we did was to make sure that there were no bulk transactions being made from the publisher like more than 50000 rows in a table in one stretch. This helped a bit as well.
-Roy
May 27, 2009 at 10:57 am
Roy Ernest (5/27/2009)
I have seen these dead locks when we had a lousy server. At that time what we did was change the clean up job schedule. This reduced it drastically. Changing the schedule for clean up job will not hurt your replication. There wont be additional latency.Another thing we did was to make sure that there were no bulk transactions being made from the publisher like more than 50000 rows in a table in one stretch. This helped a bit as well.
How can one put in the bulk restriction per commit ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply