SQL Server has three ways to get execution plans from the plan cache:
- sys.dm_exec_query_plan – around since SQL Server 2005
- sys.dm_exec_text_query_plan – added in 2005 SP2
- sys.query_store_plan – new in SQL Server 2016
And there’s an important problem with the first one. To show it, let’s take one of my many bad ideas, building queries that take 12+ hours to compile. The query in that post hits the StackOverflow.Posts table, but the execution plan isn’t really bad. However, I know one particular system object that produces a kinda scary query every time it runs: master.sys.databases.
A simple select from sys.databases produces a really wide plan.
So, what happens if we join it to itself a few times, using a join condition that won’t actually produce any rows. Let’s try a couple of queries – one has six joins, and the other has seven:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT COUNT(*) AS SixJoins FROM master.sys.databases p1 INNER JOIN master.sys.databases p2 ON p2.service_broker_guid = p1.service_broker_guid AND p2.create_date <> p1.create_date INNER JOIN master.sys.databases p3 ON p3.service_broker_guid = p2.service_broker_guid AND p3.create_date <> p2.create_date INNER JOIN master.sys.databases p4 ON p4.service_broker_guid = p3.service_broker_guid AND p4.create_date <> p3.create_date INNER JOIN master.sys.databases p5 ON p5.service_broker_guid = p4.service_broker_guid AND p5.create_date <> p4.create_date INNER JOIN master.sys.databases p6 ON p6.service_broker_guid = p5.service_broker_guid AND p6.create_date <> p5.create_date WHERE p5.database_id > 100 GO SELECT COUNT(*) AS SevenJoins FROM master.sys.databases p1 INNER JOIN master.sys.databases p2 ON p2.service_broker_guid = p1.service_broker_guid AND p2.create_date <> p1.create_date INNER JOIN master.sys.databases p3 ON p3.service_broker_guid = p2.service_broker_guid AND p3.create_date <> p2.create_date INNER JOIN master.sys.databases p4 ON p4.service_broker_guid = p3.service_broker_guid AND p4.create_date <> p3.create_date INNER JOIN master.sys.databases p5 ON p5.service_broker_guid = p4.service_broker_guid AND p5.create_date <> p4.create_date INNER JOIN master.sys.databases p6 ON p6.service_broker_guid = p5.service_broker_guid AND p6.create_date <> p5.create_date INNER JOIN master.sys.databases p7 ON p7.service_broker_guid = p6.service_broker_guid AND p7.create_date <> p6.create_date WHERE p5.database_id > 100 GO |
Turn on actual execution plans and run those – don’t worry, they actually runs quickly because they don’t produce any rows – and then check out the plans. They’re huge. I’m not even going to bother showing the graphics here, because they’re heinous, like the CGI effects of Arnold’s face blowing up in the Martian atmosphere in Total Recall.
How These Bad Queries Show Up in the Cache
Now take a look at the plan cache, and you’ll see something interesting. (I’m blowing my plan cache with DBCC FREEPROCCACHE before running the above queries in order to make it obvious.)
Here’s the query I’m using to look at the two slightly different plan cache functions:
|
1 2 3 4 5 6 |
SELECT st.text, cp.size_in_bytes, qp.query_plan, tqp.query_plan AS query_plan_text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, 0, -1) tqp ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE); |
The results:
The query with six joins has a cached plan that you can click on – because it’s from sys.dm_exec_query_plan, a management object that returns execution plans as XML.
The query with seven joins does not. Its cached plan is too large or complex for that DMV. Microsoft quickly realized this problem when 2005 came out, so in 2005 Service Pack 2, they introduced sys.dm_exec_text_query_plan. That function returns bigger data – but it only comes back in text format.
You can’t manually format these large plans as XML with the CAST or CONVERT functions, either:
|
1 2 |
Msg 6335, Level 16, State 102, Line 20 XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels. |
The TRY_CONVERT function is no good here either – it simply returns a null.
The lesson: if you wanna work with large plans, you’re going to need to hit sys.dm_exec_text_query_plan or sys.query_store_plan. To make matters worse, you can’t work with the plan data as XML inside SQL Server. If you wanted to use XML functionality, you would need to pull it out of SQL Server and do it in C# or something like that.
Sadly, that means sp_BlitzCache won’t show warnings for what’s happening inside that plan, either, because we rely on SQL Server’s XML functionality. <sigh>
6 Comments. Leave new
Thanks for sharing so much of your work, and your frequent discoveries. I always enjoy reading your articles.
If it weren’t for the fact that it’s absolutely frightening, it would be interesting to dig into just what is going on in that select from sys.databases…
Also, I’m 99% sure the effects in Total Recall were all practical effects, not CG.
this is a cool trick to deal with it
https://simoncbirch.wordpress.com/2016/11/19/xml-datatype-instance-has-too-many-levels-of-nested-nodes-maximum-allowed-depth-is-128-levels-gah/
Is there any update on this (workaround) at all please (in 2023). Microsoft should really address this issue, potentially increase the number of nested nodes levels for XML data type to allow higher maximum depth (than 128 levels).
To get Microsoft to change the product, your best bet is to contact Microsoft.
@Nathan Some additional possible workarounds to view these in SSMS are here https://dba.stackexchange.com/questions/328722/xml-datatype-instance-has-too-many-levels-of-nested-nodes-maximum-allowed-depth
These all require bringing back the problem plans individually though and obviously don’t help in any way for the problem of doing anything programmatically with these plans server side.