August 16, 2016 at 1:50 am
we have a job that runs every 5 minutes to synchronize (merge) data from a remote database to a local database. the job is a series of steps such as below (it does this for about 6 tables). There are approx 200,000 records in each table that are read and synchronized. Occasionally, about once or twice a week, we get a failure due to deadlock victim. What could be done to eliminate this deadlock victim failure? i tried adding transaction isolation level read uncommitted (don't care if 100% accurate, it will correct in the next run in 5 minutes) but still get the error. would adding indexes help? i'm not clear on whether the optimizer can optimize across servers like that.
any ideas? thanks!
MERGE INTO LOCALSERVER.dbo.LOCALDB AS DataTarget
USING REMOVESERVER.REMOTEDB.dbo.EventApplication AS DataSource
ON DataTarget.Id = DataSource.Id
WHEN MATCHED THEN
UPDATE SET Name = DataSource.Name
WHEN NOT MATCHED THEN
INSERT (Id, Name) VALUES (DataSource.Id, DataSource.Name);
August 16, 2016 at 3:05 am
Is that deadlock related to the remote server or local server? being that you are updating the local server I would expect it to be related to local resources, not remote ones.
But in any case first thing I would look at is to see if there is a field on the source table that can be used to identify if the record has been changed since last time the data was copied - something like a created date and modified date.
If this is available use that to reduce the number of rows to retrieve.
now regarding the process.
As far as my experience goes on that type of situation the merge is being split by the engine into 2 steps
step 1 - execute the select query on the remote server and insert into a local temp table
step 2 - merge the temp table with the local table.
This still being the case you might be better off splitting the merge into the same 2 steps explicitly where you will have better control over how the query is executed on the source, and at what point the local resource is used.
In this situation it may even be better to do a initial join to the local table to identify the changed records and put these on a temporary table before doing the merge as there are situations where a set of insert/delete/update steps is faster than a single merge even if it requires more coding.
August 16, 2016 at 3:50 am
Fundamentally, a deadlock is a performance issue. If the transactions complete fast enough, you won't see a deadlock. The trick to fixing them is to address both sides. Yeah, you need to try to improve performance for the remote query, but you should also look to the other query involved in the deadlock to see what you can do for performance enhancement there. Indexes may help, but examining the queries and understanding what they're doing, how they're doing it, and if there are any fundamental issues, such as they're processing the tables in different orders, or the queries have underlying problems such as functions on columns in WHERE/ON/HAVING clauses, stuff like that. Where necessary, dive into the execution plan to understand how the optimizer sees the queries. That should tell you if indexes will help or not.
"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
August 17, 2016 at 2:59 am
thanks guys. the deadlocks are occurring on the local server. the table is very frequently updated depending on user activity (it's a logging table). i've added missing indexes for all the joins possible, run times have dropped from 20-30 seconds to 10-15 seconds. No errors so far. knock on wood.
August 17, 2016 at 6:02 am
JarJar (8/17/2016)
...i've added missing indexes for all the joins possible...
based on an evaluation of the execution plans to understand where indexes are needed... I hope. Not just a blanket, there's a join, toss an index at it. Please?
"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
August 17, 2016 at 1:55 pm
To avoid pulling the entire remote table columns over every time the query runs, you need to add an index to the remote table that will identify rows updated or inserted within the last nn minutes and specify the query such that the conditions can be applied remotely, before bringing all rows back to the local server.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 17, 2016 at 2:06 pm
You are joining on the primary key, which is likely an identity, correct?
So, your logic says if we do not have a record, add it, and if we do, update the "Name" field.
You are updating every single record by only matching on the ID column. Try including the "name" field in the matching condition.
That way, it will only update the changed records, not every record.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 18, 2016 at 1:33 am
Grant Fritchey (8/17/2016)
JarJar (8/17/2016)
...i've added missing indexes for all the joins possible...based on an evaluation of the execution plans to understand where indexes are needed... I hope. Not just a blanket, there's a join, toss an index at it. Please?
yep and all joins benefitted. there were only clustered indexes on the primary key, no nonclustered on any other columns being joined. i also did what is suggested below, some of the indexes are covering indexes.
so far so good....thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply