January 13, 2014 at 6:40 am
I have a transnational replication set up on a standard three-server environment (P1->D1->S1). I have a large database that is replicated (over 400 gbs). If I add a specific set of tables to replication, I get deadlocking issues when inserting records into those tables. When I take them off replication, I get no issues. I even tried turning off all the agents to see if it was a performance issue that was causing the deadlocks, but that didn't solve the issue. Am I missing something here? I know replication does not lock tables so what other reasons can there be that can cause the deadlock issues?
January 13, 2014 at 7:28 am
Deadlocks are a performance issue. It's all about the ability of transactions to clear prior to arriving at a deadlock situation. From the sounds of things, you've probably been borderline on deadlocking in this area already and the added load from replication pushed you over the edge. The approach is the same as usual. Identify where the deadlocks are coming from specifically. Tune the queries, add or adjust indexes, modify the code such that all locking is done in the same order by different queries (Table A then Table B for every query as opposed to Table A then B for some and Table B then A for others).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 13, 2014 at 2:22 pm
The strange thing is replication works just fine on all the other tables and objects. When I add a specific table, deadlocking occurs. I just don't quite get it. I understand deadlocking is a performance issue but what is so special about this table? I don't know where to start.
January 13, 2014 at 3:12 pm
But the deadlocking must be occurring in and around that table. You need to concentrate on the queries that are accessing that table. It has to be the one associated with the deadlock.
Since this is 2008, you can look at the system_health extended event session. It automatically captures deadlock graphs, so you can see precisely the queries involved in the deadlock.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply