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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @TheRootOfAllEvil TABLE (Id INT PRIMARY KEY CLUSTERED); WITH CTE1 AS (SELECT * FROM @TheRootOfAllEvil r1), CTE2 AS (SELECT cA.* FROM CTE1 cA INNER JOIN CTE1 cB ON cA.Id = cB.Id), CTE3 AS (SELECT cA.* FROM CTE2 cA INNER JOIN CTE2 cB ON cA.Id = cB.Id), CTE4 AS (SELECT cA.* FROM CTE3 cA INNER JOIN CTE3 cB ON cA.Id = cB.Id), CTE5 AS (SELECT cA.* FROM CTE4 cA INNER JOIN CTE4 cB ON cA.Id = cB.Id), CTE6 AS (SELECT cA.* FROM CTE5 cA INNER JOIN CTE5 cB ON cA.Id = cB.Id), CTE7 AS (SELECT cA.* FROM CTE6 cA INNER JOIN CTE6 cB ON cA.Id = cB.Id), CTE8 AS (SELECT cA.* FROM CTE7 cA INNER JOIN CTE7 cB ON cA.Id = cB.Id), CTE9 AS (SELECT cA.* FROM CTE8 cA INNER JOIN CTE8 cB ON cA.Id = cB.Id) SELECT * FROM CTE9; |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @TheRootOfAllEvil TABLE (Id INT PRIMARY KEY CLUSTERED); WITH CTE1 AS (SELECT * FROM @TheRootOfAllEvil r1), CTE2 AS (SELECT cA.* FROM CTE1 cA INNER JOIN CTE1 cB ON cA.Id = cB.Id), CTE3 AS (SELECT cA.* FROM CTE2 cA INNER JOIN CTE2 cB ON cA.Id = cB.Id), CTE4 AS (SELECT cA.* FROM CTE3 cA INNER JOIN CTE3 cB ON cA.Id = cB.Id), CTE5 AS (SELECT cA.* FROM CTE4 cA INNER JOIN CTE4 cB ON cA.Id = cB.Id), CTE6 AS (SELECT cA.* FROM CTE5 cA INNER JOIN CTE5 cB ON cA.Id = cB.Id), CTE7 AS (SELECT cA.* FROM CTE6 cA INNER JOIN CTE6 cB ON cA.Id = cB.Id), CTE8 AS (SELECT cA.* FROM CTE7 cA INNER JOIN CTE7 cB ON cA.Id = cB.Id), CTE9 AS (SELECT cA.* FROM CTE8 cA INNER JOIN CTE8 cB ON cA.Id = cB.Id), CTE10 AS (SELECT cA.* FROM CTE9 cA INNER JOIN CTE9 cB ON cA.Id = cB.Id) SELECT * FROM CTE10; |
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.
7 Comments. Leave new
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 :-).
Martin – why not try it with the demo code that I give you in the post? You’ll be able to see it for yourself.
It isn’t a fixed amount of time.
https://docs.microsoft.com/en-us/archive/blogs/psssql/understanding-optimizer-timeout-and-how-complex-queries-can-be-affected-in-sql-server
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.
Some times a bad idea is just a bad idea.
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.
Well, I thought my debug a megabyte and a half of SQL in one statement to find why compiling the query plan was timing out was bad (something was imposing a 30 second timeout on the query compile step; maybe Azure). It spent too much time considering worthless index scans that wouldn’t speed it up because the fastest way was clearly evaluate subexpression immediately and fold the resulting constant before joining. But here was have an honest compiler bomb. Foooom.