Why Your Biggest Query Plans Don’t Show Up in Some DMVs

SQL Server has three ways to get execution plans from the plan cache:

And there’s an important problem with the first one. To show it, let’s take one of my many bad ideasbuilding 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.

Read the fine print

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:

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:

The results:

One of these is not like the other

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:

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>

Previous Post
Logical Errors And Query Performance
Next Post
First Responder Kit Release: Now With 50% MORE Double Checking

2 Comments. Leave new

  • Steve Armistead
    June 2, 2017 11:33 am

    Thanks for sharing so much of your work, and your frequent discoveries. I always enjoy reading your articles.

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

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