June 2, 2016 at 7:55 pm
Agreed Jeff.
The trigger(s) caused a huge series of updates to several tables, logging, auditing, etc.
This was a one time update. We are a really busy shop..so 30G growth not great but was a win for us..on to the next project.
June 3, 2016 at 7:49 am
krypto69 (6/2/2016)
...The underlying issue turned out to be triggers ...
I called that!! 😀
Glad you have completed the one-time update and can move forward!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 3, 2016 at 12:04 pm
Yes, disable your audit triggers before you purge a table.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 3, 2016 at 3:34 pm
krypto69 (6/2/2016)
This was a one time update.
Heh... you can't imagine how many times I've seen supposedly "one time" things suddenly become a requirement. 😉 The company missed a great opportunity to fix several problems.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2016 at 7:59 am
Jeff Moden (6/3/2016)
krypto69 (6/2/2016)
This was a one time update.Heh... you can't imagine how many times I've seen supposedly "one time" things suddenly become a requirement. 😉 The company missed a great opportunity to fix several problems.
I assume we're giving general advice on how to best implement the next one-off update.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 12, 2016 at 11:03 pm
krypto69 (6/2/2016)
Agreed Jeff.The trigger(s) caused a huge series of updates to several tables, logging, auditing, etc.
This was a one time update. We are a really busy shop..so 30G growth not great but was a win for us..on to the next project.
I hope you did not miss this one:
ChrisM@Work (6/2/2016)
UPDATE eligibility SET primary_plan = 0WHERE primary_plan <> 0
AND EXISTS (
SELECT 1
FROM temp_eligibility e
WHERE e.eligibility_id = eligibility.eligibility_id)
Might eliminate some writes.
_____________
Code for TallyGenerator
June 13, 2016 at 2:12 pm
Jeff Moden (5/25/2016)
krypto69 (5/25/2016)
Hi,I have a pretty simple update statement:
UPDATE eligibilitySET primary_plan = 0
FROM temp_eligibility e
WHERE eligibility.eligibility_id = e.eligibility_id
This statement is updating around 7 million rows. Problem is it is filling up my data volume (TempDB). It is eating up more than 330 Gigs of space!. Guessing it is due to the large number if indexes on this table?
Is there a way I can minimize the Tempdb growth? Change isolation level maybe?
I've run into this several times in the last couple of decades. That is actually an illegal form of UPDATE that, as you're finding out, will sometimes eat the face off of your machine. You won't find that particular form of joined UPDATE anywhere in books online.
Any time you have a joined update, you must either do like Gail did with a correlated subquery or you must include the target of the update in the FROM clause with the correct join or you end up in a situation not unlike "Halloweening" (think "CROSSJOIN" but on steroids). SQL Server has "Halloween" protection built into it but only if the form of the UPDATE is correct and the form you have is NOT correct because you have neither a correlated subquery nor is the target table in the FROM clause.
The insidious part of all this is that the improperly formed UPDATE will sometimes run correctly and then when you least expect it, some tipping point is reached and it goes nuts.
The correct form of the UPDATE that you're trying to do should be as follows...
UPDATE tgt
SET primary_plan = 0
FROM dbo.eligibility tgt
JOIN dbo.temp_eligibility e
WHERE tgt.eligibility_id = e.eligibility_id
;
Why wouldn't you use an "ON" instead of "WHERE" in your join criteria?
edit: I also don't see where its related to the "Halloween" problem unless primary_plan is the clustered index key.
one more edit: asking because I don't know and was hoping for clarification. not trying to be contrary.
June 13, 2016 at 3:06 pm
patrickmcginnis59 10839 (6/13/2016)
Jeff Moden (5/25/2016)
krypto69 (5/25/2016)
Hi,I have a pretty simple update statement:
UPDATE eligibilitySET primary_plan = 0
FROM temp_eligibility e
WHERE eligibility.eligibility_id = e.eligibility_id
This statement is updating around 7 million rows. Problem is it is filling up my data volume (TempDB). It is eating up more than 330 Gigs of space!. Guessing it is due to the large number if indexes on this table?
Is there a way I can minimize the Tempdb growth? Change isolation level maybe?
I've run into this several times in the last couple of decades. That is actually an illegal form of UPDATE that, as you're finding out, will sometimes eat the face off of your machine. You won't find that particular form of joined UPDATE anywhere in books online.
Any time you have a joined update, you must either do like Gail did with a correlated subquery or you must include the target of the update in the FROM clause with the correct join or you end up in a situation not unlike "Halloweening" (think "CROSSJOIN" but on steroids). SQL Server has "Halloween" protection built into it but only if the form of the UPDATE is correct and the form you have is NOT correct because you have neither a correlated subquery nor is the target table in the FROM clause.
The insidious part of all this is that the improperly formed UPDATE will sometimes run correctly and then when you least expect it, some tipping point is reached and it goes nuts.
The correct form of the UPDATE that you're trying to do should be as follows...
UPDATE tgt
SET primary_plan = 0
FROM dbo.eligibility tgt
JOIN dbo.temp_eligibility e
WHERE tgt.eligibility_id = e.eligibility_id
;
Why wouldn't you use an "ON" instead of "WHERE" in your join criteria?
edit: I also don't see where its related to the "Halloween" problem unless primary_plan is the clustered index key.
one more edit: asking because I don't know and was hoping for clarification. not trying to be contrary.
On the first question, massive caffeine deprivation. It should have been ON and not WHERE.
On the "Halloween" thing, call it what you will. The net effect is very similar to "Halloweening" and can cause an update that normally takes just a second to drive multiple CPU's to the wall for hours. The worst case I've seen in the past was a 6 second update take out 4 CPUs for 2 hours.
And understood on the edit, Patrick. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2016 at 1:28 am
Jeff Moden (6/13/2016)
patrickmcginnis59 10839 (6/13/2016)
Jeff Moden (5/25/2016)
krypto69 (5/25/2016)
Hi,I have a pretty simple update statement:
UPDATE eligibilitySET primary_plan = 0
FROM temp_eligibility e
WHERE eligibility.eligibility_id = e.eligibility_id
This statement is updating around 7 million rows. Problem is it is filling up my data volume (TempDB). It is eating up more than 330 Gigs of space!. Guessing it is due to the large number if indexes on this table?
Is there a way I can minimize the Tempdb growth? Change isolation level maybe?
I've run into this several times in the last couple of decades. That is actually an illegal form of UPDATE that, as you're finding out, will sometimes eat the face off of your machine. You won't find that particular form of joined UPDATE anywhere in books online.
Any time you have a joined update, you must either do like Gail did with a correlated subquery or you must include the target of the update in the FROM clause with the correct join or you end up in a situation not unlike "Halloweening" (think "CROSSJOIN" but on steroids). SQL Server has "Halloween" protection built into it but only if the form of the UPDATE is correct and the form you have is NOT correct because you have neither a correlated subquery nor is the target table in the FROM clause.
The insidious part of all this is that the improperly formed UPDATE will sometimes run correctly and then when you least expect it, some tipping point is reached and it goes nuts.
The correct form of the UPDATE that you're trying to do should be as follows...
UPDATE tgt
SET primary_plan = 0
FROM dbo.eligibility tgt
JOIN dbo.temp_eligibility e
WHERE tgt.eligibility_id = e.eligibility_id
;
Why wouldn't you use an "ON" instead of "WHERE" in your join criteria?
edit: I also don't see where its related to the "Halloween" problem unless primary_plan is the clustered index key.
one more edit: asking because I don't know and was hoping for clarification. not trying to be contrary.
On the first question, massive caffeine deprivation. It should have been ON and not WHERE.
On the "Halloween" thing, call it what you will. The net effect is very similar to "Halloweening" and can cause an update that normally takes just a second to drive multiple CPU's to the wall for hours. The worst case I've seen in the past was a 6 second update take out 4 CPUs for 2 hours.
And understood on the edit, Patrick. Thanks.
Whether the join is defined by WHERE or ON, I've done joined updates in the way suggested by Jeff for many years without issues - except where a suboptimal plan is chosen. In those few cases it's sometimes necessary to change the order of tables in the FROM list to get a join hint to work correctly. For instance,
UPDATE tgt
SET primary_plan = 0
FROM dbo.temp_eligibility e
INNER LOOP JOIN dbo.eligibility tgt
ON tgt.eligibility_id = e.eligibility_id doesn't just specify the join type, it also specifies the join direction.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 14, 2016 at 6:36 am
Jeff Moden (6/13/2016)
patrickmcginnis59 10839 (6/13/2016)
Jeff Moden (5/25/2016)
krypto69 (5/25/2016)
Hi,I have a pretty simple update statement:
UPDATE eligibilitySET primary_plan = 0
FROM temp_eligibility e
WHERE eligibility.eligibility_id = e.eligibility_id
This statement is updating around 7 million rows. Problem is it is filling up my data volume (TempDB). It is eating up more than 330 Gigs of space!. Guessing it is due to the large number if indexes on this table?
Is there a way I can minimize the Tempdb growth? Change isolation level maybe?
I've run into this several times in the last couple of decades. That is actually an illegal form of UPDATE that, as you're finding out, will sometimes eat the face off of your machine. You won't find that particular form of joined UPDATE anywhere in books online.
Any time you have a joined update, you must either do like Gail did with a correlated subquery or you must include the target of the update in the FROM clause with the correct join or you end up in a situation not unlike "Halloweening" (think "CROSSJOIN" but on steroids). SQL Server has "Halloween" protection built into it but only if the form of the UPDATE is correct and the form you have is NOT correct because you have neither a correlated subquery nor is the target table in the FROM clause.
The insidious part of all this is that the improperly formed UPDATE will sometimes run correctly and then when you least expect it, some tipping point is reached and it goes nuts.
The correct form of the UPDATE that you're trying to do should be as follows...
UPDATE tgt
SET primary_plan = 0
FROM dbo.eligibility tgt
JOIN dbo.temp_eligibility e
WHERE tgt.eligibility_id = e.eligibility_id
;
Why wouldn't you use an "ON" instead of "WHERE" in your join criteria?
edit: I also don't see where its related to the "Halloween" problem unless primary_plan is the clustered index key.
one more edit: asking because I don't know and was hoping for clarification. not trying to be contrary.
On the first question, massive caffeine deprivation. It should have been ON and not WHERE.
On the "Halloween" thing, call it what you will. The net effect is very similar to "Halloweening" and can cause an update that normally takes just a second to drive multiple CPU's to the wall for hours. The worst case I've seen in the past was a 6 second update take out 4 CPUs for 2 hours.
And understood on the edit, Patrick. Thanks.
The reason I was asking about the Halloween characterization is that in this characterization, the update of rows affect the selection of rows to update. This is why Halloween protection makes sure to separate the selection of rows from the update. So I'm wondering here because the update doesn't seem to affect the selection unless the updated column is part of the criteria whereas the generalization you point to doesn't suggest the Halloween situation.
It looks to me to be more of a depreciated join syntax morphed into an update statement where the target table moves into the update clause.
-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]
Its such an oddball problem that I specifically saved an earlier post of yours describing this as a reference, as its the first I've ever heard about it. It would be interesting to look at the plan if it could be duplicated. I'm interested about whether it instead attempts a Cartesian join as mentioned by Kevin.
June 18, 2016 at 1:28 pm
Either Gail or Jeff's solution should work.
And in Gail's solution it just refers to the matching rows and the beauty with EXISTS clause is that it just need one matching records instead of all.
June 18, 2016 at 2:07 pm
patrickmcginnis59 10839 (6/14/2016)
Jeff Moden (6/13/2016)
patrickmcginnis59 10839 (6/13/2016)
Jeff Moden (5/25/2016)
krypto69 (5/25/2016)
Hi,I have a pretty simple update statement:
UPDATE eligibilitySET primary_plan = 0
FROM temp_eligibility e
WHERE eligibility.eligibility_id = e.eligibility_id
This statement is updating around 7 million rows. Problem is it is filling up my data volume (TempDB). It is eating up more than 330 Gigs of space!. Guessing it is due to the large number if indexes on this table?
Is there a way I can minimize the Tempdb growth? Change isolation level maybe?
I've run into this several times in the last couple of decades. That is actually an illegal form of UPDATE that, as you're finding out, will sometimes eat the face off of your machine. You won't find that particular form of joined UPDATE anywhere in books online.
Any time you have a joined update, you must either do like Gail did with a correlated subquery or you must include the target of the update in the FROM clause with the correct join or you end up in a situation not unlike "Halloweening" (think "CROSSJOIN" but on steroids). SQL Server has "Halloween" protection built into it but only if the form of the UPDATE is correct and the form you have is NOT correct because you have neither a correlated subquery nor is the target table in the FROM clause.
The insidious part of all this is that the improperly formed UPDATE will sometimes run correctly and then when you least expect it, some tipping point is reached and it goes nuts.
The correct form of the UPDATE that you're trying to do should be as follows...
UPDATE tgt
SET primary_plan = 0
FROM dbo.eligibility tgt
JOIN dbo.temp_eligibility e
WHERE tgt.eligibility_id = e.eligibility_id
;
Why wouldn't you use an "ON" instead of "WHERE" in your join criteria?
edit: I also don't see where its related to the "Halloween" problem unless primary_plan is the clustered index key.
one more edit: asking because I don't know and was hoping for clarification. not trying to be contrary.
On the first question, massive caffeine deprivation. It should have been ON and not WHERE.
On the "Halloween" thing, call it what you will. The net effect is very similar to "Halloweening" and can cause an update that normally takes just a second to drive multiple CPU's to the wall for hours. The worst case I've seen in the past was a 6 second update take out 4 CPUs for 2 hours.
And understood on the edit, Patrick. Thanks.
The reason I was asking about the Halloween characterization is that in this characterization, the update of rows affect the selection of rows to update. This is why Halloween protection makes sure to separate the selection of rows from the update. So I'm wondering here because the update doesn't seem to affect the selection unless the updated column is part of the criteria whereas the generalization you point to doesn't suggest the Halloween situation.
It looks to me to be more of a depreciated join syntax morphed into an update statement where the target table moves into the update clause.
-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]
Its such an oddball problem that I specifically saved an earlier post of yours describing this as a reference, as its the first I've ever heard about it. It would be interesting to look at the plan if it could be duplicated. I'm interested about whether it instead attempts a Cartesian join as mentioned by Kevin.
It IS an oddball problem and I like your description of the syntax. I've only run into it a half dozen times in the last 2 decades... twice at whatever company I was working for and about 4 or so times on forums. Of course, the times I ran into it at the companies I was working for constituted an emergency and couldn't do something like try a rerun with the actual execution plan running so no real help there. It's also difficult to duplicate the problem as an experiment. It seems that both Mars and Jupiter must be in opposition and then only when you have a full moon.
I may have something similar to the problem going on at work right now but for a different reason. We have an UPDATE that's seems like it should only take a single table scan worth of time but is taking 2 hours instead. I don't know why someone would put a CROSS APPLY against a view that returns a single row based on SPID but I can see the possible problems with that because a CROSS APPLY is so similar to a correlated subquery. This time, I'm analyzing what the cause of the problem is instead of just fixing it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply