CHAPTER 28 ■ PERFORMANCE TUNING
SET SHOWPLAN XML OFF GO _
This returns the following (this is an abridged snippet, because the actual output is more than a page long):
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan Version="0.5" Build="9.00.1187.07">
<BatchSequence> <Batch> <Statements> ...
<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">
<OutputList> <ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Alias="[p]" Column="Name" />
<ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[Product]" Alias="[p]" Column="ProductNumber" />
<ColumnReference Database="[AdventureWorks]" Schema="[Production]" Table="[ProductReview]" Alias="[r]" Column="ReviewerName" />
How It Works
You can use SHOWPLAN_ALL, SHOWPLAN_TEXT, or SHOWPLAN_XML to tune your Transact-SQL queries and batches. These commands show you the estimated execution plan without actually executing the query. You can use the information returned in the command output to take action towards improving the query performance (for example, adding indexes to columns being using in search or join conditions). Looking at the output, you can determine whether SQL Server is using the expected indexes, and if so, whether SQL Server is using an index seek, index scan, or table scan operation.
In this recipe, the SET SHOWPLAN for both TEXT and XML was set to ON, and then followed by GO:
SET SHOWPLAN TE
A query referencing the Production.Product and Production.ProductReview was then evaluated. The two tables were joined using an INNER join on the ProductID column, and only those products with a product rating of 2 or higher would be returned:
SELECT p.Name, p.ProductNumber, r.ReviewerName FROM Production.Product p INNER JOIN Production.ProductReview r ON
p.ProductID = r.ProductID WHERE r.Rating > 2
The SHOWPLAN was set OFF at the end of the query, so as not to keep executing SHOWPLAN for subsequent queries for that connection.
Looking at snippets from the output, you can see that a nested loop join (physical operation) was used to perform the INNER JOIN (logical operation):