Home, Parent: SQL Server Cost Based Optimizer
AdventureWorks, Index Seek, Key Lookups, Table Scan, Joins, Insert, Update, Delete,
Parallelism I, Parallelism II, Parallelism III,

Insert Update and Delete

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

Key Lookup

The clustered Insert operator details for 1 and 2 rows:

Key Lookup Key Lookup

The Production.ProductProductPhoto table is a heap, has zero nonclustered indexes and two foreign keys.

Key Lookup

The heap Insert operator details for 1 and 2 rows:

Key Lookup Key Lookup

Insert into clustered index with 1 nonclustered index

Key Lookup

The clustered Insert operator with 1 nonclustered details for 1 and 2 rows:

Key Lookup Key Lookup

The clustered Insert operator with 2 and 3 nonclustered details for 1 row2:

Key Lookup Key Lookup


XML Sqlplan Files

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" >