Bad Idea Jeans: Building Big Query Plans

When you build a monitoring tool that sends advice via email, you discover two things:

  • Some people have really, really big execution plans
  • Email servers have reasonable limits on file attachment sizes

Add those two things together, and Richie found himself working on a new feature for SQL ConstantCare® that would automatically break up advice into multiple emails if we needed to attach several big query plans. He needed to test it, so I whipped up some T-SQL that would quickly build a large query plans.

You can copy/paste this directly into SSMS, no prep required, and get the estimated execution plan:

When I started writing the query gradually, layering on one CTE at a time, the first several 7 were no big deal. The query compilation time was noticeable for 8, but that 9th CTE, hoowee. You should see it yourself.

Copy/paste that query into SSMS, and just getting the estimated query plan takes tens of seconds. Once it generates, you can right-click on the Select operator and go into properties to see just how hard it worked – in my case, it took 26 seconds of CPU time and clock time to build the plan.

If you save the file, the resulting sqlplan file is over 10MB in size, and that causes a bit of a problem for SSMS, too. If you right-click on the plan and click Show XML, you get an error about needing to edit a registry key if you want to show a plan that big.

Not bad for 12 lines of T-SQL.

And that certainly got the job done for Richie.

But imagine what we could do with 13:

Adding that one extra join takes compilation time to 10 minutes on my machine.

For bonus points, you can run several of these at a time with SQLQueryStress. At first, the queries don’t appear to be waiting on anything at all – they’re just churning through CPU as fast as they can get it, and the server doesn’t show any wait times:

But wait for a little while, and RESOURCE_SEMAPHORE_QUERY_COMPILE times pop up:

Fun times.

So, what’s the moral of this story, you ask? Like so many posts in my Bad Idea Jeans category, and so many of my readers, this story has no morals.

Previous Post
Finally, You Can Buy Brent Ozar Unlimited Swag.
Next Post
How to Upgrade SQL Server Evaluation Edition to Developer Edition

6 Comments. Leave new

  • Martin Guth
    June 23, 2020 9:16 am

    Hi Brent,

    thanks for sharing. I am curious that I read “compilation time” 10 Minutes. My understanding was that queries are only given a few seconds to compile until they hit a compilation timeout (dangerous half knowledge). Did you turn some knobs in order to get such a high compilation time or is this standard behaviour? If you know a ressource with more info on how the maximum time spent on compilation is calculated please mention it in order for me to learn better :-).

    Reply
  • One time one of my servers spat out error 8623, which is basically summarized as “You nested ~28 CTE’s and SQL Server has given up on creating a plan, please for the love, of the flying spaghetti monster, simplify your query”. And the developer and I then had a long discussion about how CTEs are not materialized views.

    Reply
  • Vegard Hagen
    June 24, 2020 1:19 pm

    Some times a bad idea is just a bad idea.

    Reply
  • Peter Rovnak
    June 28, 2020 2:39 pm

    I executed the 9 CTE and the 10 CTE query; they compiled in 77 seconds and 32 minutes (1929 seconds) of elapsed time, respectively. I then added this hint:
    OPTION (USE HINT(‘FORCE_LEGACY_CARDINALITY_ESTIMATION’))
    The 9 CTE and the 10 CTE query compiled in 7 seconds and 62 seconds of elapsed time, respectively. Looks like new Cardinality Estimation strikes again.

    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":""}