April 19, 2016 at 3:36 pm
I am trying to retrieve actual execution plan from production for a certain stored procedure, I copied this query from simple-talk, with running results to grid:
SELECT deqp.dbid ,
deqp.objectid ,
deqp.encrypted ,
deqp.query_plan
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE deqp.objectid = OBJECT_ID('My_SP_name', 'p') ;
It selects NULL for query_plan. For cacheobjtype it has "Compiled Plan", for objtype it has "Proc", for plan_handle it has long 0x..... 16-based value.
If I run it without Where clause, it selects all available plans and I noticed that some other objects don't have query_plan either. Why? And how I can get around it?
Thanks
April 19, 2016 at 4:12 pm
There are a couple reasons for this I've run across.
It's possible for really complicated queries to exceed the allowed nesting level for the XML returned.
For those you can use sys.dm_exec_text_query_plan instead. Do note that if the plan is very long, you may also have to save the output to a file to avoid the SSMS character limitations.
Another reason I've run across surprisingly frequently is that the plan shows up NULL if you have a stored procedure with conditional branches that query temporary objects, and not all the branches have executed so far (see http://db-pub.com/forum-80537640/unable-to-get-the-xml-plan-with-conditional-statements-using-temp-tables.html for an example).
For these, both sys.dm_exec_query_plan and sys.dm_exec_text_query_plan with the default offsets will show NULL.
However, if you pass the plan handle and the statement_start_offset and statement_end_offset from sys.dm_exec_query_stats to sys.dm_exec_text_query_plan, you'll see the plans for the branches that have executed.
I suspect there's some naive concatenation going on such that a NULL for one branch (I presume because of deferred compile) returns NULL for the whole thing. That's just speculation, though.
Cheers!
EDIT: Fixed a typo.
April 20, 2016 at 3:16 am
Another point, that query fetches the estimated plan (plan without run-time information) because the run-time info that makes a plan 'actual' isn't stored in cache. It would be too expensive and take too much space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2016 at 8:51 am
Jacob Wilkins (4/19/2016)
Another reason I've run across surprisingly frequently is that the plan shows up NULL if you have a stored procedure with conditional branches that query temporary objects, and not all the branches have executed so far
Thanks Jacob. This is my case, with exception that I don't have temporary objects. This S.P. has a conditional branch depending of what value is passed as one of the parameters. Both branches are very complex. For most of the cases one value is passed to this parameter and it executes within 30 sec. However, sometimes another value is passed that chooses another branch in this S.P. and execution time may run between 4 and 10 hours. What I suspect that in the latter case SQL Server chooses another, existing plan, which is very inefficient in this case. And that was the reason why I need to select actual execution plan from production.
Now I found a query from a DMV book (it's on this site: http://www.sqlservercentral.com/articles/books/70486/ ) page 85-86 and ran it. I got a message:
Msg 6335, Level 16, State 102, Line 1
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
For now, I recommended to developer to break this procedure on 2 and make decision from within application. But I'd like to see that exec plan and I am stuck with it.
April 20, 2016 at 9:00 am
Try using sys.dm_exec_text_query_plan instead of sys.dm_exec_query_plan. You get the same plan back, but it's a varchar/nvarchar type, not xml, and so avoids that error.
And again, that's not going to be an actual plan. What you get from the plan cache is a plan without runtime information, aka an estimated plan.
And some reading material for your developer: https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 20, 2016 at 9:03 am
Then that sounds like the first problem I mentioned with the nesting levels, not the problem with the branches.
Either way, did you try using sys.dm_exec_text_query_plan? That should get around the nesting level issue, and will also deal with the conditional case if used with the offsets from exec_query_stats.
Cheers!
EDIT: I see Gail already responded while I was typing. Consider this a +1 🙂
April 20, 2016 at 9:57 am
This is a query that I ran now. I took it from DMV book and modified it. It uses sys.dm_exec_text_query_plan
SELECT detqp.dbid ,
detqp.objectid ,
CAST(detqp.query_plan AS XML) AS singleStatementPlan ,
detqp.query_plan AS batch_query_plan ,
ROW_NUMBER() OVER ( ORDER BY Statement_Start_offset )AS query_position ,
CASE WHEN deqs.statement_start_offset = 0
AND deqs.statement_end_offset = -1
THEN '-- see objectText column--'
ELSE '-- query --' + CHAR(13) + CHAR(10) +
SUBSTRING(execText.text, deqs.statement_start_offset / 2,
((CASE WHEN deqs.statement_end_offset = -1
THEN DATALENGTH(execText.text)
ELSE deqs.statement_end_offset
END
) - deqs.statement_start_offset
) / 2
)
END AS queryText
FROM sys.dm_exec_query_stats deqs
outer APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,deqs.statement_start_offset,deqs.statement_end_offset)AS detqp
outer APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
WHERE detqp.objectid = OBJECT_ID('My_SP_name, 'p') ;
I still receive this error:
Msg 6335, Level 16, State 102, Line 1
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
April 20, 2016 at 10:00 am
That's because it's casting the text plan as XML in the SELECT list. Just pull the query plan column without casting it to XML.
Cheers!
April 20, 2016 at 11:13 am
Thanks. Now I got it.
But I am still thinking how can I use it. It's XML in text format, all in one long line and I can hardly figure out execution steps from there. Did they remove limitation of 128 XML levels in more recent versions? We are going to migrate to 2014.
April 20, 2016 at 11:48 am
Just save it as a .xml file and read it with your favorite XML reader.
Most of them should format it pretty nicely (even just opening it with IE does a not too terrible job).
It's going to be a pain to investigate from that, but with a plan that large, I'm not sure I'd like looking at the graphical plan more than looking at the XML anyway 🙂
As far as I'm aware there is no plan yet to remove SQL Server's self-imposed limit on nesting nodes in XML.
There is an active connect item you can upvote, but it looks pretty lonely:
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply