xquery against query

xquery against query_plan

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

a - 25 Mar 2008 23:36 GMT

When I run an xquery against a query plan I do not get anything until I
remove the first element containing the schema. Is work around?

This si the query
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thr
ead/thread/880f7ff6483fa50f/e76ef170b02e555e?lnk=st&q=%40plan.nodes+relop#e76ef1
70b02e555e

Reply to this Message

a - 25 Mar 2008 23:39 GMT

I am running this script into a temp table and want to query the query_plan
xml column

SELECT TOP 100
qs.execution_count,
DatabaseName = DB_NAME(qp.dbid),
ObjectName = OBJECT_NAME(qp.objectid,qp.dbid),
StatementDefinition =
SUBSTRING (
st.text,
(
qs.statement_start_offset / 2
) + 1,
(
(
CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END -
qs.statement_start_offset
) / 2
) + 1
),
query_plan,
st.text, total_elapsed_time
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan (qs.plan_handle) qp
WHERE
st.encrypted = 0

> When I run an xquery against a query plan I do not get anything until I
> remove the first element containing the schema. Is work around?
>
> This si the query
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thr
ead/thread/880f7ff6483fa50f/e76ef170b02e555e?lnk=st&q=%40plan.nodes+relop#e76ef1
70b02e555e

Reply to this Message

Mike C# - 26 Mar 2008 00:06 GMT

Try adding this just above the SELECT query where you're shredding the XML
with the .nodes() method:

WITH XMLNAMESPACES(DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

>I am running this script into a temp table and want to query the query_plan
>xml column
[quoted text clipped - 31 lines]
>> This si the query
>> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thr
ead/thread/880f7ff6483fa50f/e76ef170b02e555e?lnk=st&q=%40plan.nodes+relop#e76ef1
70b02e555e

Reply to this Message

Mike C# - 25 Mar 2008 23:56 GMT

Not sure what you mean here, but is this what you're after?

DECLARE @plan XML ;

SET @plan = '<MissingIndexes>
<MissingIndexGroup Impact="95.8296">
<MissingIndex Database="[AdventureWorks]" Schema="[Sales]"
Table="[SalesOrderHeader]">
<ColumnGroup Usage="EQUALITY">
<Column Name="[TerritoryID]" ColumnId="14" />
</ColumnGroup>
<ColumnGroup Usage="INEQUALITY">
<Column Name="[ShipMethodID]" ColumnId="17" />
<Column Name="[SubTotal]" ColumnId="21" />
<Column Name="[Freight]" ColumnId="23" />
</ColumnGroup>
<ColumnGroup Usage="INCLUDE">
<Column Name="[SalesOrderNumber]" ColumnId="8" />
<Column Name="[CustomerID]" ColumnId="11" />
</ColumnGroup>
</MissingIndex>
</MissingIndexGroup>
</MissingIndexes> ' ;

SELECT mi.value('(@Database)[1]', 'SYSNAME') AS database_name,
mi.value('(@Schema)[1]', 'SYSNAME') AS schema_name,
mi.value('(@Table)[1]', 'SYSNAME') AS table_name,
cg.value('(@Usage)[1]', 'VARCHAR(100)') AS usage_group,
col.value('(@Name)[1]', 'SYSNAME') AS column_name
FROM @plan.nodes('//MissingIndex') X(mi)
CROSS APPLY mi.nodes('ColumnGroup') Y(cg)
CROSS APPLY cg.nodes('Column') Z(col);

Reply to this Message

a - 26 Mar 2008 17:16 GMT

Thanks for the with xmlnamespace! I am trying to get procs and their
physicalops to see which ones need tuning ie have index scan etc.
#queryplan is created in the previous post.

if object_id('tempdb.dbo.#xmlshred') is not null

drop table #xmlshred

if object_id('tempdb.dbo.#report') is not null

drop table #report

create table #xmlshred (PhysicalOp varchar(100), LogicalOp varchar(100),
EstimateCPU decimal(18,3), EstimateIO decimal(18,3),
EstimatedTotalSubtreeCost decimal(18,3), AvgRowSize int)

create table #report (ProcName sysname, PhysicalOp varchar(100), LogicalOp
varchar(100), EstimateCPU decimal(18,3), EstimateIO decimal(18,3),
EstimatedTotalSubtreeCost decimal(18,3), AvgRowSize int)

declare @plan xml

declare @pName sysname

declare curNodes cursor for select objectname, query_plan from #queryplan
where query_plan is not null

open curNodes

fetch curNodes into @pName, @plan

while @@fetch_status <> -1

begin

WITH XMLNAMESPACES(DEFAULT
'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

insert #xmlshred select ro.RelOp.value('@PhysicalOp', 'varchar(100)')
PhysicalOp

, ro.RelOp.value('@LogicalOp', 'varchar(100)') LogicalOp

, convert(decimal(18,6),CAST (CAST
(ro.RelOp.value('@EstimateCPU', 'varchar(100)') AS float(24)) AS
decimal(18, 13))) EstimateCPU

, convert(decimal(18,6),CAST (CAST
(ro.RelOp.value('@EstimateIO', 'varchar(100)') AS float(24)) AS decimal(18,
13))) EstimateIO

, convert(decimal(18,6),CAST (CAST
(ro.RelOp.value('@EstimatedTotalSubtreeCost', 'varchar(100)') AS float(24))
AS decimal(18, 13))) EstimatedTotalSubtreeCost

, convert(decimal(18,6),CAST (CAST
(ro.RelOp.value('@AvgRowSize', 'varchar(100)') AS int) AS int)) AvgRowSize

FROM @plan.nodes('//RelOp') as ro(RelOp)

insert #report select @pName, PhysicalOp, LogicalOp, EstimateCPU,
EstimateIO, EstimatedTotalSubtreeCost, AvgRowSize from #xmlshred

fetch curNodes into @pName, @plan

end

close curNodes

deallocate curNodes

select * from #report

> Not sure what you mean here, but is this what you're after?
>
[quoted text clipped - 34 lines]
>> This si the query
>> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thr
ead/thread/880f7ff6483fa50f/e76ef170b02e555e?lnk=st&q=%40plan.nodes+relop#e76ef1
70b02e555e

Reply to this Message