SQL Server – Find SQL Text from Plan Diagram in SentryOne Plan Explorer

execution-plansql server

I had SSMS create an execution plan and opened it through the context menu in SentryOne Plan Explorer. I go to Top Operations, select one with huge cost and switch to Plan Diagram. The node corresponding to the option is scrolled into view.

I'm now wondering how I can find out to what part of my SQL statement this node relates to. To generate comma separated lists I use some calls to STUFF ... FOR XML PATH. I guess it's one of those that's slow. Hence it reports some [XML Reader].

How can I find out which one?

SentryOne Plan Explorer

Best Answer

Unfortunately, there's not really a way to do this "automatically" that I'm aware of.

An execution plan operator doesn't necessarily map to something in the query text. Some simple cases of this are functions and views. Imagine a simple query like this:

SELECT * FROM dbo.vwOrderDetails;

The execution plan will probably have scans or seeks against dbo.Orders and dbo.OrderLineItems (whatever is in the view definition). But there is nothing in the plan XML that indicates those scans and seeks "map back" to dbo.vwOrderDetails.

Another example would be execution plan operators that don't map easily to any specific SQL text. For instance, a Sort operator might map to an ORDER BY clause. But it might be a sort introduced to satisfy a MERGE JOIN.

In your specific case, your best bet is probably to look at the STUFF ... FOR XML PATH portions of your query and try to reason about which one is the node in question (maybe based on what operators are flowing into it, or based on the output list).

Another option would be to break the query up into smaller chunks that can be run separately to narrow down what is what. You could comment out JOINs and / or items in the SELECT list until the XML Reader TVF node disappears, and then you'll know which part of the query text it's coming from.

Related Question