Bad Idea Jeans: Dynamically Generating Ugly Queries and Task Manager Graffiti

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:

Which gives you a 10,000 join query, but I’m only showing the first 10 lines here:

Big dynamic query

Big dynamic query

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:

When I drop it down to a much more realistic 5,000 joins:

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:

Come on, SQL Server. Work with me. Cowboy up. Alright, let’s try 2,000 joins, and….

Fun trivia: SQL Server uses exactly one core to compile a query.

Fun trivia: SQL Server uses exactly one core to compile a query.

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:

When combined with a really ugly CPU-burning query, you get:

Graffiti in Task Manager

Graffiti in Task Manager

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:

FIRE IN THE HOLE

FIRE IN THE HOLE

Which gives me a slightly different Task Manager:

High score!

High score!

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:

The elusive RESOURCE_SEMAPHORE_QUERY_COMPILE

The elusive RESOURCE_SEMAPHORE_QUERY_COMPILE

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:

SQL Server puts in half-days

SQL Server puts in half-days

Previous Post
Announcing Training Class Instant Replay, Plus New Classes in Philly, San Diego, and Online
Next Post
What kind of hardware can you buy for one core’s worth of Enterprise Edition?

10 Comments. Leave new

  • Yep, this is why there are graph databases like Neo4J.
    They laugh when nodes are being ‘joined’…

    Reply
  • Donald Moehling
    August 11, 2016 11:59 am

    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.

    Reply
  • Some days, I aspire to be like you.

    This is one of those days.

    Reply
  • 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.

    Reply
  • 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?

    Reply
    • 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.

      Reply
      • 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.

        Reply
        • 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!

          Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}