CHAPTER 28 ■ PERFORMANCE TUNING
You can also see from this output that a clustered index scan was performed using the PK_ProductReview_ProductReviewID primary key clustered index to retrieve data from the ProductReview table:
|--Clustered Index Scan ( O B J E C T : ( [ A d v e n t u r e W o r k s ] . [ P r o d u c t i o n ] . [ P r o d u c t R e v i e w ] . [ P K _ P r o d u c t R e v i e w _ P r o d u c t R e v i e w I AS [r]), D ]
A clustered index seek, however, was used to retrieve data from the Product table:
|--Clustered Index S e e k ( O B J E C T : ( [ A d v e n t u r e W o r k s ] . [ P r o d u c t i o n ] . [ P r o d u c t ] . [ P K _ P r o d u c t _ P r o d u c t I D ] A S [ p ] ) ,
The SET SHOWPLAN_XML command returned the estimated query plan in an XML document format, displaying similar data as SHOWPLAN_TEXT. The XML data is formatted using attributes and elements.
For example, the attributes of the RelOp element shows a physical operation of Nested Loops and a logical operation of Inner Join—along with other statistics such as estimated rows impacted by the operation:
<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="105" EstimatedTotalSubtreeCost="0.00996111" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
The XML document follows a specific schema definition format which defines the returned XML elements, attributes, and data types. This schema can be viewed at the following URL: http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd.
Forcing SQL Server 2005 to Use a Query Plan
SQL Server 2005 introduces the new USE PLAN command, which allows you to force the query opti- mizer to use an existing, specific query plan for a SELECT query. You can use this functionality to override SQL Server’s choice, in those rare circumstances when SQL Server chooses a less efficient query plan over one that is more efficient. Like plan guides (covered later), this option should only be used by an experienced SQL Server professional, as SQL Server’s query optimizer usually makes good decisions when deciding whether or not to reuse or create new query execution plans.
The syntax for USE PLAN is as follows:
USE PLAN N'xml_plan
The xml_plan parameter is the XML data type representation of the stored query execution plan. The specific XML query plan can be derived using several methods, including: SET SHOWPLAN_XML, SET STATISTICS XML, the sys.dm_exec_query_plan dynamic management view, and via SQL Server Profiler’s Showplan XML events.
In this example, SET STATISTICS XML is used to extract the XML formatted query plan for use in the USE PLAN command:
SET STATISTICS XML ON
SELECT TOP 10 Rate FROM HumanResources.EmployeePayHistory ORDER BY Rate DESC
SET STATISTICS XML OFF