SQL SERVER – Practical SQL Server XML: Part One – Query Plan Cache and Cost of Operations in the Cache

I am very fortunate that I have friends like Michael Coles. Michael Coles is SQL Server and XML expert and have written many books on SQL Server as well XML. He has previously written book which I have reviewed on this blog SQLAuthority News – Book Review – Pro T-SQL 2005 Programmer’s Guide (Paperback). I am currently reading his latest book Pro SQL Server 2008 XML (Hardcover) which can be found on amazon. I will be writing review of the book once I am done reading it.

Michael Coles and I met last at Microsoft MVP Summit 2009 at Seattle and I come to know he very much young and dynamic person who is friend of everybody. His knowledge of SQL Server is exception and I had requested him to write one practical demonstration of XML and he took up this challenge very well and the out come of that challenge is I am going to publish in this blog as it is.

I spoke to Pinal Dave at the MVP Summit this week, and he challenged me to provide his readers with a practical demonstration of using XML in the database.  I took up the challenge, and decided to go one better by giving two practical demonstrations.

This post is the first demonstration, which queries the SQL Server query plan cache and returns the cost of operations in the cache.  The query simply shreds the cached XML query plans and grabs all the RelOp nodes, which represent all the operators used by the query plan. In the second post at I’ll demonstrate passing XML parameter “sets” into a stored procedure.  As a bonus, SQL MVP Jacob Sebastian has agreed to add a third post on his blog, at that will give a practical example of using XML Schema in SQL Server.

The XML Query plans look like this in the cache (note that I simplified the XML and this is only a small portion that’s relevant):

<QueryPlan xmlns:p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
CachedPlanSize="112"
CompileTime="500"
CompileCPU="53"
CompileMemory="1968">

…

<RelOp NodeId=“0”
PhysicalOp=“Sequence Project”
LogicalOp=“Compute Scalar”
EstimateRows=“227.951”
EstimateIO=“0”
EstimateCPU=“1.82361e-005”
AvgRowSize=“5202”
EstimatedTotalSubtreeCost=“930.972” …>

…

<RelOp NodeId=“1”
PhysicalOp=“Segment”
LogicalOp=“Segment”
EstimateRows=“227.951”
EstimateIO=“0”
EstimateCPU=“4.55901e-006”
AvgRowSize=“5202”
EstimatedTotalSubtreeCost=“930.972” …>

…

</RelOp>

…

</RelOp>
</QueryPlan>

The equivalent graphical execution plan looks like this (note this is a partial screenshot):

(Click on image to see enlarged image)

(Note that you can save your graphical execution query plans as XML also – just right-click in the graphical execution query plan window and save it as a .sqlplan file. The .sqlplan file is in XML format – you can verify this by opening it in NotePad).

Since SQL Server 2005 and 2008 store the query plans in the cache as XML we can get all of the same information in relational format.  The sample query below returns attributes of each operator in each query plan including physical and logical operators, estimated cost, estimated IO, estimated CPU cost, and an estimated number of rows returned.

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows,
PlanHandle,
QueryText,
QueryPlan,
CacheObjectType,
ObjectType)
AS
(
SELECT
RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
RelOp.op.value(N'@NodeId', N'int') AS OperationID,
RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
cp.plan_handle AS PlanHandle,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan,
cp.cacheobjtype AS CacheObjectType,
cp.objtype AS ObjectType
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
)
SELECT
PlanHandle,
ParentOperationID,
OperationID,
PhysicalOperator,
LogicalOperator,
QueryText,
CacheObjectType,
ObjectType,
EstimatedCost,
EstimatedIO,
EstimatedCPU,
EstimatedRows
FROM CachedPlans
WHERE CacheObjectType = N'Compiled Plan';

The results look similar to the following:

(Click on image to see enlarged image)

Since the XML query plan is hierarchical in structure, with operations nested within other operations, I’ve added the columns ParentOperationID and OperationID.  The ParentOperationID values point to the current operator’s parent operator.  I added the arrows in the results above to demonstrate.

So what’s the practical use of this?  Well, once you have the XML query plan information in relational format it’s relatively easy to manipulate.  You can determine, for instance, the Top 10 most costly queries in your cache and the operators in those queries are the most expensive. You can even log this information to a table or send it out in other formats.

In part 2 of this series I’ll talk about another practical use for SQL Server-based XML, at http://sqlblog.com/blogs/michael_coles/archive/2009/03/03/practical-sql-server-xml-part-2.aspx.  SQL Server MVP Jacob Sebastian will finish the series with part 3 on his blog at .

Reference : Pinal Dave (https://blog.sqlauthority.com)