February 6, 2012 at 2:22 pm
Hi folks,
I have a table partitioned on the date, with one partition per year.
I need to update one field in this table, and I thought I could run parralel updates on the table like
Query 1:
UPDATE my_table
SET to_be_updated_field = XXX
WHERE YEAR(my_table.date)=2011
Query 2:
UPDATE my_table
SET to_be_updated_field = XXX
WHERE YEAR(my_table.date)=2010
Query 3:
UPDATE my_table
SET to_be_updated_field = XXX
WHERE YEAR(my_table.date)=2009
Query 4:
UPDATE my_table
SET to_be_updated_field = XXX
WHERE YEAR(my_table.date)=2009
ETC.
The problem is some of the UPDATE query stop with an error message
Msg 1205, Level 13, State 52, Line 5
Transaction (Process ID 56) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Is there a way to update in parralel queries the same table if the WHERE clause excludes a particular row to be accessed by two different statements ?
Does the fact that the table is partitioned by year and the updates are also by year can change anything ?
Thanks for your help !
Bruno
February 6, 2012 at 2:27 pm
What's the partition function?
The first thing that strikes me is that your where clause is not SARGable and will require SQL to do an index scan. It may not be able to eliminate partitions correctly as a result of the non-SARGable predicate.
Can you post the execution plan for one of those updates? Actual plan please (you can run it in a transaction and roll it back)
Also switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 3:03 pm
The partition function:
/****** Object: PartitionFunction [PositionPartitionFunction] Script Date: 02/07/2012 08:30:15 ******/
CREATE PARTITION FUNCTION [PositionPartitionFunction](datetime) AS RANGE LEFT FOR VALUES (N'2006-01-01T00:00:00.000', N'2007-01-01T00:00:00.000', N'2008-01-01T00:00:00.000', N'2009-01-01T00:00:00.000', N'2010-01-01T00:00:00.000', N'2011-01-01T00:00:00.000', N'2012-01-01T00:00:00.000')
GO
my update statement uses a Scalar-valued function (GetPortIdFromLatLon) that takes two parameters.
See attached the actual execution plan.
I also ran
DBCC TRACEON(1222,-1)
Thanks !
Bruno
February 6, 2012 at 3:18 pm
Not a picture of the plan please, a picture of the operators are near-useless. Save the plan as a .sqlplan file and attach it to your post.
Also, try changing the queries to be SARGable, so
UPDATE my_table
SET to_be_updated_field = XXX
WHERE my_table.date>='2011/01/01' AND my_table.date < '2012/01/01'
Better chance of partition elimination and makes the query able to use an index seek.
p.s. Are you sure you want RANGE LEFT for your partition function? RANGE LEFT means that your boundary values belong to the partition on the left. So, as an example, the first partition will contain all values that are <= 2006-01-01 00:00:00.000, the 2nd partition will not contain 2006-01-01 00:00:00.000, but will have values between 2006-01-01 00:00:00.003 and 2007-01-01 00:00:00.000', etc.
That means that a filter for a single year will always touch 2 partitions. RANGE RIGHT is far more common for datetime partition functions
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 3:48 pm
Here is the sql plan, thanks for the hint on the left range right range, I must confess I used the wizzard and did not think about it
February 6, 2012 at 3:55 pm
At the moment the only predicate used to filter rows is the Vessel ID. All of the others are evaluated later when the rows are read.
First thing, fix the date predicates so that they are SARGable (the year one that I did should give you a hint), and fix the partition function so that the dates are in the desired partitions, then see if you still have deadlocks. If you do, post the new exec plan and I'll have another look at it.
Just be warned, changing the partition function won't be easy. I suspect you'll have to create a new one, maybe a new scheme and rebuild the table's clustered index onto the new partition scheme.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 3:59 pm
Will do, thanks for the help !
February 7, 2012 at 1:56 pm
Hi GilaMonster,
thanks again for your help, it worked like a charm !
Bruno
February 7, 2012 at 1:57 pm
Glad to hear it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply