Query xml field in Query Plan DMV

I anot expertin XML and need to query a value in the field "query_plan" of
the "sys.dm_exec_query_plan" DMV. The value I is the whether queries has
Parallelism value more than 0. I remeber have seen something like
sys.dm_exec_query_plan.query_plan.value ('declare namespace.......
Is there in BOL a chapter on how querying special SQL xml schemas ?
Thanks in advance
Hello eliassal,
You could query the XML column in the DMV as any other XML column in SQL by
using the XQuery.
For example, I use this Statement to query the query_Plan:
select query_plan.query('declare namespace
showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
/showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
sys.dm_exec_query_plan(<my plan handle id>)
For more detailed information about how to query the XML date using XQuery,
please refer this article:
XQuery Against the xml Data Type
http://msdn2.microsoft.com/en-us/library/ms189075.aspx
For more detailed information about the schema of the query_plan, pleaser
refer this article:
sys.dm_exec_query_plan
http://msdn2.microsoft.com/en-us/ms189747.aspx
http://schemas.microsoft.com/sqlserver/
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||So many thanks, I tried so many times, so many syntaxes to work with the
"RelOp" element without success
In the sql schemq it is defined as follows :
<xsd:element name="RelOp" type="shp:RelOpType" />
<xsd:element name="ParameterList" type="shp:ColumnReferenceListType"
minOccurs="0" maxOccurs="1" />
As I said yesterday, I understood in a sql article that if the
max(...RelOp/@.parelle.....) > 0 gives us an idea if the query would be
paralleize.
I am deserate!!!HELP :-)
Thanks again
"Wei Lu [MSFT]" wrote:

> Hello eliassal,
> You could query the XML column in the DMV as any other XML column in SQL by
> using the XQuery.
> For example, I use this Statement to query the query_Plan:
> select query_plan.query('declare namespace
> showplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> /showplan:ShowPlanXML/showplan:BatchSequence/showplan:Batch') from
> sys.dm_exec_query_plan(<my plan handle id>)
> For more detailed information about how to query the XML date using XQuery,
> please refer this article:
> XQuery Against the xml Data Type
> http://msdn2.microsoft.com/en-us/library/ms189075.aspx
> For more detailed information about the schema of the query_plan, pleaser
> refer this article:
> sys.dm_exec_query_plan
> http://msdn2.microsoft.com/en-us/ms189747.aspx
> http://schemas.microsoft.com/sqlserver/
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
|||Hello Eliassal,
I found that article. The statement should be:
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace
p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@.Parallel)', 'float') > 0
Troubleshooting Performance Problems in SQL Server 2005
https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
px
Is this article you want?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||LOVELY, so many thanks
"Wei Lu [MSFT]" wrote:

> Hello Eliassal,
> I found that article. The statement should be:
> select
> p.*,
> q.*,
> cp.plan_handle
> from
> sys.dm_exec_cached_plans cp
> cross apply sys.dm_exec_query_plan(cp.plan_handle) p
> cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
> where
> cp.cacheobjtype = 'Compiled Plan' and
> p.query_plan.value('declare namespace
> p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
> max(//p:RelOp/@.Parallel)', 'float') > 0
>
> Troubleshooting Performance Problems in SQL Server 2005
> https://www.microsoft.com.nsatc.net/technet/prodtechnol/sql/2005/tsprfprb.ms
> px
> Is this article you want?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
|||Hello Eliassal,
My pleasure. If you have any question, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)