XML
If your database collects information on costs, such as the running cost of a given stock in the exchange, and you want to know what the maximum cost was during the day, how would you determine that? Obviously you would simply write a query to select the data you needed, aggregating and sorting as necessary to arrive at the answer. Why then should we treat the data about our queries any other way? Let’s write a query to select data from the execution plan.
Starting with SQL Server 2005 and continuing in SQL Server 2008, a new type of execution plan output was available, XML. What a lot of people don’t know is that the graphical execution plan is also XML data. When you save a graphical execution plan to file, the only thing that will determine if it opens as XML or as an execution plan is the file extension. Changing it from XML to SQLPLAN or vice versa will change the way it opens.
We can get XML from our graphical execution plans or directly out of Profiler. Another choice is to take advantage of dynamic management views that store the execution plans used:
SELECT p.query_plan from sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) p
This is a great way to work directly on production systems. Let’s assume, for this operation, we’ve been collecting data through a trace and now have execution plans that we’re going to save to file in order to access.
One note: in SQL Server 2005 and SQL Server 2008, if you save the execution plan, it is accessible as XML, but it gets saved as ANSI but marked as UNICODE in the XML. To actually open it using the following code, you’ll need to open it in some other tool, NotePad works, and then resave it as UNICODE.
Once you have a file try this, it lists the operators from the plan by the Estimated CPU Cost:
DECLARE @xml XML
SELECT @xml =(SELECT *
FROM OPENROWSET
(BULK ‘C:Documents and SettingsfritcheygMy DocumentsArticlesComplexExecutionPlansbigplan.sqlplan’,
SINGLE_BLOB) XMLShowPlan
)
SELECT RelOp.op.value(‘@NodeId’, ‘int’) AS NodeId,
RelOp.op.value(‘@PhysicalOp’, ‘NVARCHAR(75)’) AS Operation,
RelOp.op.value(‘@EstimateCPU’, ‘float’) AS EstimatedCPU,
RelOp.op.value(‘@EstimateIO’, ‘float’) AS EstimatedIO
FROM @xml.nodes(‘declare default element namespace
“http://schemas.microsoft.com/sqlserver/2004/07/showplan“;
//RelOp’) AS RelOp ( op )
ORDER BY [EstimatedCPU] DESC
Since we know that the estimated values and the actual values may not equal each other, and one area where this rears its head is in the row counts. With this query, we’ll find the operations that have the greatest disparity between estimated and actual row counts. This query does assume a non-parallel execution plan:
DECLARE @xml XML
SELECT @xml =(SELECT *
FROM OPENROWSET
(BULK ‘C:Documents and SettingsfritcheygMy DocumentsArticlesComplexExecutionPlansbigplan.sqlplan’,
SINGLE_BLOB) XMLShowPlan
);
WITH ExPlan AS(
SELECT RelOp.op.value(‘@NodeId’, ‘int’) AS NodeId,
RelOp.op.value(‘@PhysicalOp’, ‘NVARCHAR(75)’) AS Operation,
RelOp.op.value(‘@EstimateCPU’, ‘float’) AS EstimatedCPU,
RelOp.op.value(‘@EstimateIO’, ‘float’) AS EstimatedIO,
RelOp.op.value(‘@EstimateRows’,’float’) AS EstimatedRows,
RelOp.op.value(‘(./*:RunTimeInformation/*:RunTimeCountersPerThread)[1]/@ActualRows’,’int’) AS ActualRows
FROM @xml.nodes(‘declare default element namespace
“http://schemas.microsoft.com/sqlserver/2004/07/showplan“;
//RelOp’) AS RelOp ( op )
)
SELECT e.NodeId
,e.Operation
,e.EstimatedRows
,e.ActualRows
,(e.EstimatedRows/ActualRows) AS EstActualRatio
FROM [ExPlan] e
WHERE e.EstimatedRows > 0
and e.ActualRows > 0
ORDER BY [EstActualRatio] ASC
More information is available inside the XML of the execution plan such as the name of the index being scanned, columns referenced, columns output, parameters used, etc. You can use XPath queries to retreive all this information as needed.
Conclusion
Complex queries do present challenges to the DBA tasked with maintaining them or tuning them. Just remember, one bite at a time. Breaking down the statements using Profiler to capture explicit statement execution time and CPU or I/O cost, will allow you to focus your attention in the correct areas of your complex query. Execution plans can be misleading, but they act as the best available guide to understand what’s occurring behind the scenes. When dealing with very large execution plans, use one of the many methods available to get to the XML that underlies the plan. You can then use XPath queries to directly access the data, again allowing you to focus on the parts of the execution plan that are most problematic. Just don’t forget, one bite at a time.
Thanks:
I had some help on parts of this article. Thanks to Jeff Moden for all the assistance identifying when execution plans and “Query Cost as a Percentage Of Batch” go wrong. Also thanks to “Mark” for helping me clean up some of my XPath queries.
]]>