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:
|
1 2 3 4 5 6 7 |
SELECT 1 AS Ordered, 'SELECT p1.* FROM dbo.Posts p1' UNION 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:
|
1 2 |
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:
|
1 2 |
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:
|
1 2 |
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:
|
1 2 3 |
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 GO |
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:
10 Comments. Leave new
Yep, this is why there are graph databases like Neo4J.
They laugh when nodes are being ‘joined’…
Totally Boss! I follow you daily, and love everything you post. It’s pure magic.
The way you present use full information in fun and candid ways is just amazing.
I personally can’t wait to have the opportunity to take one of your classes in person.
Keep up the great work.
Looking forward to this afternoons session on Performance Overhead.
Thanks, glad you liked it!
Some days, I aspire to be like you.
This is one of those days.
HAHAHA, thanks sir. Days like this one are a lot of fun.
Appreciate it. Very informative.
I have started learning about databases in depth these days & its always fun to read articles and webcasts by you guys.
Fun post!
What struck me, related to a discussion at work, was the remark “let’s put affinity masking back the right way”. Would that be to just leave the decision making to SQL Server?
Rudy – exactly, you got it! Keep it simple. There are edge cases where you have to set aside specific cores for the OS, but I’m lucky in that I’ve never had to deal with servers that pegged out on CPU use.
I was unlucky enough to inherit servers where this was manually configured. And yes, the right answer was “Set it back to auto”… the hard part was convincing the other DBA that the CPU problems were *caused* by his configuration–and not the other way around.
Brent and Andy,
Thanks for your answers. Always nice to get confirmation for one’s ideas. I think I ‘ll also have some convincing ahead of me!