When I was testing SQL Server 2025’s new ability to limit TempDB usage with Resource Governor, I wrote a few fun diabolical demos. One of them was to generate giant spills to TempDB, quickly.
When you’re looking at an actual (not estimated) query plan, and you see a yellow exclamation point on a sort operator, that means:
- SQL Server made a guess about how much memory it would need to accomplish that sort
- The amount of data coming into the sort wildly exceeded SQL Server’s plans
- SQL Server resorted to writing that data to TempDB: aka, the data spilled over from memory to disk
This can happen in a few interesting ways:
- SQL Server under-estimates how much data will be coming into the sort, or
- SQL Server accurately estimates it, but simply doesn’t have enough RAM available to handle it
- The query goes parallel, the memory is evenly divided across threads, but the data is not evenly distributed, so some threads run out of memory while other threads’ memory sits unused
Sorts aren’t the only operators that can spill, either!
So for this week’s Query Exercise, we’re going to play a little code golf: write the shortest query you can that will produce the biggest spills. Code golf is a fun way to think out of the box, freeing yourself from traditional limitations around the tables & queries you usually work with.
Those of you who have my Fundamentals of TempDB course can check out this brand-new class module on Resource Governor, and you’ll get an idea of the approach I took. (If you don’t have that class, check out my Black Friday sale.) I couldn’t let this problem out of my head so I ended up toying around with it, iterating over that approach for a while to make it much shorter than the class demo.
Put your queries in a Github Gist and include those link in your comments. Check out the solutions from other folks, compare and contrast your work, and next week I’ll circle back with my answers & thoughts. To give you a rough idea of what to aim for, my solution uses less than 100 characters, and will run any server out of TempDB space with a ginormous spill. Have fun!
18 Comments. Leave new
This feels lazy & slow… I haven’t tested this with a particularly big tempdb to know that it works reliably, but it does spill!
?????
https://gist.github.com/amtwo/642eb42317aa62fb66d9386cb4feb40f
I love your solution, and you’re gonna love mine, heh.
I couldn’t come up with a solution I’m proud of, but I managed to rule out a few solutions. My thoughts:
* Creating a temp table is stupid. Your connection string lets you connect to tempdb without spending any characters, so making a real table in tempdb is one character cheaper.
* Spills and SORT_IN_TEMPDB are the wrong idea. Spills are just a way to get SQL Server to use tempdb. Why do that when I can just use tempdb myself?
* The version store is also the wrong idea, since it’s just copies of data. If I want to copy data, I’ll just repeat whatever I used to create it with a loop or GO.
In conclusion, the only reasonable solution I could come up with was to connect to tempdb directly, make a big permanent table, and stuff it with data until the server pops. Code golfing that doesn’t have the stroke of genius that I think Brent is expecting.
It sounds like you had a lot of fun with the attempts, though! For me, that’s a good chunk of the fun of code golf – trying to approach the problem in a lot of wacko ways, and then watching how other people solve it.
Got it in 60 characters, but I’m not happy. If Brent has bothered to pose this question, then he probably has a good solution. My solution doesn’t feel like the smart kind that I’m expecting Brent to want, so I can only assume that I’ve missed one.
https://gist.github.com/ReeceGoding/4f4fd837fc1795ec59f3440a51aa8d5e
Now I’m going to re-watch the video he’s linked and feel very ashamed if his solution is shorter.
Ooo, that’s not a spill though. It’s a really cool line, but it’s not a spill.
Landed on this. Doesn’t seem impressive.
https://gist.github.com/ReeceGoding/f06ea0f9a9491335b026622a6da9df55
Got it down to 72 on that previous link. Don’t think I’m going to beat that.
I love it! Great work!
I use the cardinality estimation limitation from using local variables so SQL Server misestimates the rows coming out from the function and going into the sort, guaranteeing a spill to TempDB on any server. A giant string sort key then magnifies the spill. The bigger @N is, the larger the spill, but also much slower the query is.
https://gist.github.com/tnguyen7s/0b11e4cb34f3a7cfdc683bbe92599190
Tuyen – ooo, I love the idea of the local variable! Honestly when I started this task, I wanted to get a lowball estimate, and I tried a few routes before I gave up – your idea didn’t even occur to me! That’s awesome.
Just for reference, here’s the actual plan: https://www.brentozar.com/pastetheplan/?id=OKq1A3YsUY
And if you take the same number of characters, and you switch them to 9s:
https://www.brentozar.com/pastetheplan/?id=mUFLpGZCwV
Thanks, Brent, for the nice trick! I’m down to 87 characters now
https://gist.github.com/tnguyen7s/edf0877f966a16d5c928b24b0e71710c
I have this one, 69 characters
SELECT*FROM sys.objects CROSS JOIN sys.objects CROSS JOIN sys.objects
Msg 1013 Level 16 State 1 Line 1
The objects “sys.objects” and “sys.objects” in the FROM clause have the same exposed names. Use correlation names to distinguish them.
Yeah, like Mash said, that doesn’t even compile, and even after you get it to work, you’re going to want to test your query yourself to make sure it actually produces a spill, which is what this game is all about. You’d wanna be fair to folks on the other side of the screen – it’s not our job to test your work. Hope that’s fair!
91 chars and very random, unpredictable memory consumption / 50 rows estimate. For larger spills just increase the the numbers
SELECT REPLICATE(1,value%3960)FROM GENERATE_SERIES(0,CAST(RAND()*999999 AS INT))ORDER BY 1
UPDATE STATISTICS test WITH ROWCOUNT = 9223372036854775808, PAGECOUNT = 9223372036854775808
GO
select tekst_kolumn from test order by tekst_kolumn desc
You didn’t create the test table in here. 😀