Say you’re at Dell DBA Days, and you want to build a really ugly query, fast. You want to generate a StackOverflow database query that will take a long time to compile, and maybe demand a huge amount of memory to run, but not actually take any time to execute.
You might start by building a numbers table with Method #7 from this StackOverflow answer, and then:
SELECT 1 AS Ordered, 'SELECT p1.* FROM dbo.Posts p1'
SELECT nt.Number AS Ordered, 'INNER JOIN dbo.Posts p' + CAST(nt.Number AS VARCHAR(10)) +
' ON p' + CAST(nt.Number AS VARCHAR(10)) + '.Id = p' + CAST(nt.Number - 1 AS VARCHAR(10)) + '.ParentId'
FROM dbo.NumbersTest nt
WHERE nt.Number > 1 AND nt.Number < 10000
ORDER BY 1
Which gives you a 10,000 join query, but I’m only showing the first 10 lines here:
So then you can copy/paste the contents of that second column into a new window. You could also assemble a string, but I like having it available for copy/paste because it, uh, doesn’t exactly work the first time.
For example, when I run it with 10,000 joins:
Msg 8631, Level 17, State 1, Line 1
Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.
When I drop it down to a much more realistic 5,000 joins:
Msg 4074, Level 16, State 1, Line 1
Client drivers do not accept result sets that have more than 65,535 columns.
Ah! Okay, that’s fair. (That’s also two error messages I’ve never seen before. Probably a good thing.) Alright, let’s take out the SELECT * and replace it with SELECT p1.* and see what happens:
Msg 8621, Level 17, State 1, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.
Come on, SQL Server. Work with me. Cowboy up. Alright, let’s try 2,000 joins, and….
Fifteen minutes later, SQL Server is still trying to compile an execution plan for the two-thousand-join query – and I know for a fact that the query won’t produce a single row.
So as long as I’m waiting for it to compile, might as well have a little fun with Task Manager. On a 72-core box, this query:
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 0 TO 1,4 TO 5,7,10,12,14,16,19,22,24,26,
28 TO 29,31,34,64,66,68 TO 69,71,74,76,78,80,83,86,88 TO 89,92 TO 93,95 TO 96,98 TO 99
When combined with a really ugly CPU-burning query, you get:
Over an hour later, it was still trying to build a query plan for the 2,000 join query, so I figured hey, let’s put affinity masking back the right way, then throw the query into SQLQueryStress and run 72 executions of it:
Which gives me a slightly different Task Manager:
It’s surprisingly hard to achieve 100% CPU usage across all 72 cores, but be patient, and you can get it. SQL Server is in bad shape, too – if you run sp_BlitzFirst to check the running queries:
Most queries are waiting on RESOURCE_SEMAPHORE_QUERY_COMPILE – which means they can’t get enough memory in order to start compiling.
I canceled that – fun, but not all that useful – and over 90 minutes later, I was still waiting for the 2,000 join query to compile.
Wanna watch these kinds of shenanigans? Watch the recordings of Dell DBA Days 2016 webcasts.
Update: after 12 hours and 43 minutes, the compilation gave up: