There is a small modification we can make to avoid the spools and sorts, and get failure-free operation even at 128MB memory:

DECLARE

@LocID ...

Jeff Moden (1/7/2012)

---

Sorry ol' friend. Didn't mean to come across as snarky. I realize that you good folks have put in a good amount of work on this...

J Livingston SQL (1/7/2012)

---

as I spawned this thread...thought it only polite to report back

based on my pc (with the memory of a toaster) .......

using traceflag 652=ON.....works every time

using WITH (INDEX(0))...

Thanks Chris. I am able to get an error with max memory = 1000MB, but not 1024MB 🙂

Intuitively, the...

Ok so this is getting silly: that's four QotDs in a row that I have wrong, and I *knew* the answer to this one! Or so I thought, perhaps...

Well this was an awkward time to check the thread.

Jeff Moden (1/7/2012)

---

So what is the conclusion here? That you shouldn't try to do "enterprise-scale" quirky updates on a box that has less memory than most of today's handheld...

Fantastic work Peter, and luckily no surprises, so great confirmation.

Looking...

Can I ask that you guys start a new thread for this? Feel free...

Not sure if this...

Three in a row wrong!!! :crying:

It is certainly still possible to write highly dubious code using in-line table-valued functions if you really try. There are a few cases where a...

Lynn Pettis (1/6/2012)

---

Case insensitive vs case sensitive, one of the reasons I write all my queries and t-sql code as if everything was case sensitive. Don't have to worry...

The way I read your comments makes it sound like this mysterious self-blocking only occurs when TF 652...

Certainly. I ran the tests on 2005, 2008, R2, and 2012 64-bit because I wanted to see if this behaviour...