all OutputList nodes from different parent nodes?

Question

all OutputList nodes from different parent nodes?

Lowell

SSC Guru

Points: 323518
More actions
November 29, 2011 at 5:38 am

#247404

In another thread, the question came up to find all dependancies of an executed SQL statement.
Looking over some sample execution plans, I found it's not something that is simple, although all the data is in the execution plan.
i can see that a sqlplan file will have multiple <RelOp><OutputList><ColumnReference> tags, all at different levels within the plan.
I know how to use xquery to drill down to a specific tag, all the way from the top, like my script below,
but my problem is there are multiple <RelOp><OutpoutList><ColumnReference> nodes, some under nested loops, others under other tags, all depending on the plan itself.
how can i select <RelOp><OutpoutList><ColumnReference> nomatter what node/level in the xml it sits under?
WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT p.query_plan,
DatabaseName = p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Database)[1]', 'NVARCHAR(256)'),
TableName = p.query_plan.value(N'(sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Table)[1]', 'NVARCHAR(256)'),
SchemaName = p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Schema)[1]', 'NVARCHAR(256)'),
ColumnName = p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Column)[1]', 'NVARCHAR(256)'),
Alias = p.query_plan.value(N'(/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference/@Alias)[1]', 'NVARCHAR(256)')
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.DM_EXEC_QUERY_PLAN(s.plan_handle) AS p
WHERE p.query_plan.exist(N'/sp:ShowPlanXML/sp:BatchSequence/sp:Batch/sp:Statements/sp:StmtSimple/sp:QueryPlan/sp:RelOp/sp:OutputList/sp:ColumnReference') = 1
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76987 More actions · Answer 1

Use the '//' that you want to find all occurrences. Something like p.query_plan.nodes('//sp:RelOp/sp:OutputList/sp:ColumnReference')

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lowell SSC Guru Points: 323518 More actions · Answer 2

thanks drew!

I'd hang my head in shame for not knowing that but since it's outside of my typical comfort zone, I don't feel too bad.

That cleared up my query really quick! I'll continue fiddling with it now...awesome!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!