Home, Parent: SQL Server Cost Based Optimizer
AdventureWorks,
Index Seek,
Key Lookups,
Table Scan,
Joins,
Insert, Update, Delete,
Parallelism I,
Parallelism II,
Parallelism III,
A brief look at Insert Update and Delete operations.
The Sales.Individual table has a clustered index, zero nonclustered indexes and two foreign keys.
INSERT [Sales].[Individual]
VALUES
( 1, 1,
"<XML>",
"2010-04-01"
)
The execution plan below
The clustered Insert operator details for 1 and 2 rows:
The Production.ProductProductPhoto table is a heap, has zero nonclustered indexes and two foreign keys.
The heap Insert operator details for 1 and 2 rows:
Insert into clustered index with 1 nonclustered index
The clustered Insert operator with 1 nonclustered details for 1 and 2 rows:
The clustered Insert operator with 2 and 3 nonclustered details for 1 row2:
The XML plan header is shown below. Some of the details were not available in SQL Server 2000. The plan compile cost is displayed in the Properties panel.
<xml
version="1.0"
encoding="utf-8">
<ShowPlanXML
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
Version="1.0"
Build="9.00.4226.00"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence
>
<Batch
>
<Statements
>
<StmtSimple
StatementCompId="1"
StatementEstRows="1"
StatementId="1"
StatementOptmLevel="FULL"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
StatementSubTreeCost="0.00657058"
StatementText="SELECT x FROM A WHERE col1 = Y"
StatementType="SELECT"
>
<StatementSetOptions
ANSI_NULLS="false"
ANSI_PADDING="false"
ANSI_WARNINGS="false"
ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false"
NUMERIC_ROUNDABORT="false"
QUOTED_IDENTIFIER="false"
/
>
<QueryPlan
CachedPlanSize="13"
CompileTime="4"
CompileCPU="4"
CompileMemory="224"
>
<RelOp
AvgRowSize="19"
EstimateCPU="1E-07"
EstimateIO="0"
EstimateRebinds="0"
EstimateRewinds="0"
EstimateRows="1"
LogicalOp="Compute Scalar"
NodeId="0"
Parallel="false"
PhysicalOp="Compute Scalar"
EstimatedTotalSubtreeCost="0.00657058"
>