| Microsoft SQL Server 2005 XML Showplan |
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.8" Build="14.0.3015.40"><BatchSequence><Batch><Statements><StmtSimple StatementText="DELETE TOP(1) 
FROM [table_A] 
WHERE [id] IN (SELECT [i] FROM [table_B])
OPTION(HASH JOIN)" StatementId="1" StatementCompId="2" StatementType="DELETE" RetrievedFromCache="true" StatementSubTreeCost="0.0343419" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x392E73D522834AD6" QueryPlanHash="0x8E28E4909C2292FE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1056" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="216"><MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1056" RequiredMemory="1024" DesiredMemory="1056" RequestedMemory="1056" GrantWaitTime="0" GrantedMemory="1056" MaxUsedMemory="32" MaxQueryMemory="607512"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1529592"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0343419" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_A]" IndexKind="Heap" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0243409" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></TopExpression><RelOp NodeId="2" PhysicalOp="Hash Match" LogicalOp="Right Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0177716" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0243408" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1056" OutputMemoryGrant="1056" UsedMemoryGrant="32"></RunTimeCountersPerThread></RunTimeInformation><Hash><DefinedValues></DefinedValues><HashKeysBuild><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_B]" Column="i"></ColumnReference></HashKeysBuild><HashKeysProbe><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_A]" Column="id"></ColumnReference></HashKeysProbe><ProbeResidual><ScalarOperator ScalarString="[fiddle_cf53698fc0cb424e8e138619f4116303].[dbo].[table_A].[id]=[fiddle_cf53698fc0cb424e8e138619f4116303].[dbo].[table_B].[i]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_A]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_B]" Column="i"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></ProbeResidual><RelOp NodeId="3" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_B]" Column="i"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_B]" Column="i"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_B]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp><RelOp NodeId="4" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_A]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="Bmk1000"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_A]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_cf53698fc0cb424e8e138619f4116303]" Schema="[dbo]" Table="[table_A]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Hash></RelOp></Top></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |