Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 258

First, look at the join types, and make sure the right join type has been chosen. For example, nested loop
joins work best when uniting two relatively small inputs. If you see a nested loop join operating against
two large inputs, then something's wrong. Try creating indexes that will prompt the optimizer to choose
another join type. You could also force the query to use a specific join type, but only when you're sure it's
the right thing to do.
Second, in a graphical plan, arrows have different thicknesses to indicate the number of rows returned.
Also recall that graphical plans are read from right to left. Therefore, in most cases, moving from right to
left, the arrow thickness should lessen. However, if you notice an arrow near the left that is very thick,
and the previous arrows where not as thick, that's a visual indicator that your query may be sub-optimal.
As you move toward the end of the query the arrows should diminish in thickness. If you notice that
the opposite has happened, or that the arrow sizes were trending then suddenly one arrow is noticeably
thick that may indicate a problem.
Also, look for scans, spools (especially a rebinding eager spool), and RID lookups. All these operations
can cause some major degradation due to excessive I/O. Try rewriting the query to minimize these
operators. You may also be able to create some indexes that would cause some of these operators to not
be used.
OtherQueryPlanDisplayModes
Another option for displaying query execution plans is through the use of T-SQL SET SHOWPLAN
statements. The various SHOWPLAN statements are described below. However, note that when using
some of these statements the queries submitted aren't actually executed. This can be an issue if the
queries you want to analyze contain temp tables that are created in an initial statement, then refer-
enced later in subsequent statements. SQL Server analyzes the query and returns the results of that
analysis.
Set Showplan_XML
When this option is enabled SQL Server returns then plan information for a query in a well-formed XML
document. Consider the following query for the AdventureWorks database:
SELECT ProductID FROM Sales.SalesOrderDetail
When using SHOWPLAN_XML, the following output is generated. As you can see, the simplest query
will generate a fairly large XML plan.
< ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/
showplan" Version="1.0"
Build="9.00.1406.00" >
< BatchSequence >
< Batch >
< Statements >
< StmtSimple StatementText="SELECT&#x9;ProductID FROM&#x9;Sales
.SalesOrderDetail &#xD;&#xA;" StatementId="1" StatementCompId="1"
StatementType="SELECT" StatementSubTreeCost="0.303397"
StatementEstRows="121317" StatementOptmLevel="TRIVIAL" >
< StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" / >