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 eligibility

SET 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.