add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
fn
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="select fn&#xa;from t&#xa;intersect&#xa;select firstname&#xa;from person.person" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.160189" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x5F74C8CD26D8DA49" QueryPlanHash="0x52EAF37609EDFB9E" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><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="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="31" CompileCPU="21" CompileMemory="392"><MissingIndexes><MissingIndexGroup Impact="88.4447"><MissingIndex Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[Person]" Table="[Person]"><ColumnGroup Usage="EQUALITY"><Column Name="[FirstName]" ColumnId="5"></Column></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="959544" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2644200"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2020-07-09T18:11:22.93" ModificationCount="0" SamplingPercent="100" Statistics="[AK_Person_rowguid]" Table="[Person]" Schema="[Person]" Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]"></StatisticsInfo><StatisticsInfo LastUpdate="2020-07-09T18:11:22.89" ModificationCount="0" SamplingPercent="100" Statistics="[IX_Person_LastName_FirstName_MiddleName]" Table="[Person]" Schema="[Person]" Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]"></StatisticsInfo><StatisticsInfo LastUpdate="2021-10-16T23:26:42.92" ModificationCount="0" SamplingPercent="42.5846" Statistics="[_WA_Sys_00000005_7D439ABD]" Table="[Person]" Schema="[Person]" Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0417415" AvgRowSize="61" EstimatedTotalSubtreeCost="0.160189" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[Expr1003]=[fiddle_68ca84a041b94ddaa1d1d8fa2b498079].[Person].[Person].[FirstName]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[Person]" Table="[Person]" Column="FirstName"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100061" AvgRowSize="61" EstimatedTotalSubtreeCost="0.0146445" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></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" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Expr1003"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="61" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(50),[fiddle_68ca84a041b94ddaa1d1d8fa2b498079].[dbo].[t].[fn],0)"><Convert DataType="nvarchar" Length="100" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[dbo]" Table="[t]" Column="fn"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="36" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[dbo]" Table="[t]" Column="fn"></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_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[dbo]" Table="[t]" Column="fn"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[dbo]" Table="[t]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></ComputeScalar></RelOp></Sort></RelOp><RelOp NodeId="4" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="19972" EstimatedRowsRead="19972" EstimateIO="0.0786806" EstimateCPU="0.0221262" AvgRowSize="22" EstimatedTotalSubtreeCost="0.100807" TableCardinality="19972" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[Person]" Table="[Person]" Column="FirstName"></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><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[Person]" Table="[Person]" Column="FirstName"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_68ca84a041b94ddaa1d1d8fa2b498079]" Schema="[Person]" Table="[Person]" Index="[IX_Person_LastName_FirstName_MiddleName]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>