Browse by Tag : introduction (RSS)

Welcome to Pro SQL Server XML

By Michael Coles in Pro SQL Server XML | 01-21-2008 5:38 PM | Categories: Filed under: XML, new features, methods, tutorial, introduction

Rating: (not yet rated) Rate this |

Discuss | 5,451 Reads | 372 Reads in Last 30 Days |1 comment(s)

Hi everyone, in this blog I plan to discuss the new SQL Server 2005 and SQL Server 2008 XML features. While I have several topics I plan to cover already, if you have specific SQL Server + XML related questions, feel free to contact me via this blog and I'll try to answer, or at least help you get a little closer to the answer.

I decided to kick this off with a simple example to demonstrate the utility of XML in SQL Server. SQL Server 2008 and 2005 store cached XML query plans that are accessible via the sys.dm_exec_query_plan dynamic management function. This function takes a query plan handle as an argument and returns the XML query plan, along with some other metadata.

While I was at the PASS Conference in Denver, I threw together a quick example to demonstrate shredding the cached XML query plans on SQL 2005. I expanded it a little bit to include some other information from the sys.dm_exec_sql_text dynamic management function and the sys.dm_exec_cached_plans dynamic management view.

WITH Plans(nodeid, physicalop, estimated_cost, plan_handle, text, query_plan, cacheobjtype, objtype) AS ( SELECT RelOp.op.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; @NodeId', 'int'), RelOp.op.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; @PhysicalOp', 'varchar(50)'), RelOp.op.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; @EstimatedTotalSubtreeCost ', 'float'), cp.plan_handle, st.text, qp.query_plan, cp.cacheobjtype, cp.objtype 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('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; //RelOp') RelOp (op) ) SELECT ROW_NUMBER() OVER (PARTITION BY p.plan_handle ORDER BY p.NodeId) AS Operation_Num, p.physicalop, p.text, p.cacheobjtype, p.objtype, p.estimated_cost FROM Plans p WHERE p.cacheobjtype = 'Compiled Plan';

Sample results of running this query on my local server are shown in the image below.

Sample xml query plan shredding

This sample query uses a few of the new features available starting with SQL 2005: the CROSS APPLY operator, Common Table Expressions (CTEs), dynamic management views/functions, and the ROW_NUMBER() windowing function.

The most important feature we're using here is the xml data type and its nodes() and value() methods. Since the XML query plans are stored as xml data type data, we are using the nodes() method to shred the XML data nodes into rows. Then we apply the value() method to each of these new rows to extract individual query plan operators and operator information from the plan. In this case we pull the physical operator name ("Nested Loops", "Filter", etc.) and the estimated subtree cost for each operator. As a bonus we are grabbing the initial SQL statement that is the basis for the plan from the sys.dm_exec_sql_text dynamic management view. That way we can easily relate the query plan, and its individual operators, back to the source SQL statement.

This is just one example of the capabilities of the xml data type. In upcoming posts I'll describe more precisely how the xml data type methods and XQuery work.