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.
CREATE TABLE T (
id1 int,
id2 int,
someValue bit not null,
primary key (id1, id2),
index ix (someValue)
);

INSERT T VALUES
(1, 1, 0),
(1, 2, 1),
(2, 1, 0),
(2, 2, 0);

UPDATE STATISTICS T WITH FULLSCAN;
4 rows affected
set statistics xml on;
SELECT *
FROM T
WHERE someValue = 1
ORDER BY id1, id2;
id1 id2 someValue
1 2 True
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x0900EA3E105EE2FA66ECA5172D84BFFE8F960000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="2" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x0A9CA36655584DA5" QueryPlanHash="0x3516E35CEFAEBAAE" CardinalityEstimationModelVersion="160"><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" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="120"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.44" ModificationCount="0" SamplingPercent="100" Statistics="[ix]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[ix]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@1]"><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC
SELECT
SELECT
Cached plan size24 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0032831
Degree of Parallelism0
Estimated Number of Rows1
Statement
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC
Index Seek (NonClustered)
[T].[ix]
Cost: 100%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical OperationIndex Seek
Logical OperationIndex Seek
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read1
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0032831 (100%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size16 B
Actual Rebinds0
Actual Rewinds0
OrderedTrue
Node ID0
Output List
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue
Object
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[ix]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue = Scalar Operator([@1])
SELECT *
FROM T
WHERE someValue = 1
ORDER BY id2, id1;
id1 id2 someValue
1 2 True
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x09003E10D6025AC400D6125C74ADED7F2EC10000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="false" StatementSubTreeCost="0.0146444" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2BE8C280E057EAD6" QueryPlanHash="0xD9DA09624608DB1C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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="0" CompileCPU="0" CompileMemory="144"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="665544" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.44" ModificationCount="0" SamplingPercent="100" Statistics="[ix]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100016" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" 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" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[ix]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Sort></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC
SELECT
SELECT
Cached plan size24 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0146444
Degree of Parallelism0
Memory Grant1024
Estimated Number of Rows1
Statement
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC
Sort
Cost: 78%
Sort
Sort the input.
Physical OperationSort
Logical OperationSort
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0113613 (78%)
Estimated I/O Cost0.0112613
Estimated CPU Cost0.0001
Estimated Subtree Cost0.0146444
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1
Estimated Row Size16 B
Actual Rebinds1
Actual Rewinds0
Node ID0
Output List
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue
Order By
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2 Ascending
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1 Ascending
Index Seek (NonClustered)
[T].[ix]
Cost: 22%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical OperationIndex Seek
Logical OperationIndex Seek
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read1
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0032831 (22%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size16 B
Actual Rebinds0
Actual Rewinds0
OrderedTrue
Node ID1
Output List
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue
Object
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[ix]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue = Scalar Operator((1))
CREATE INDEX IX ON T (someValue, id2, id1) WITH (DROP_EXISTING=ON)
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="insert [dbo].[T] select * from [dbo].[T] option (maxdop 1)" StatementId="1" StatementCompId="1" StatementType="INSERT" StatementSqlHandle="0x09003CC291C1BA32149825D16F29A1E4C56C0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="false" StatementSubTreeCost="0.0246642" StatementEstRows="4" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x67C9CED2F80738A3" QueryPlanHash="0x68E0A6953BB79CE6" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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="MaxDOPSetToOne" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="128"><Warnings><MemoryGrantWarning GrantWarningKind="Excessive Grant" RequestedMemory="1024" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantWarning></Warnings><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="665544" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><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="Index Insert" LogicalOp="Insert" EstimateRows="4" EstimateIO="0.01" EstimateCPU="4e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0246642" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><CreateIndex><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[IX]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="4" EstimateIO="0.0112613" EstimateCPU="0.000112496" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0146602" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="RowRefSrc1007"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" 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="832" OutputMemoryGrant="1024" UsedMemoryGrant="16" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[ix]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></Sort></RelOp></CreateIndex></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
insert [dbo].[T] select * from [dbo].[T] option (maxdop 1)
INSERT
INSERT
Cached plan size24 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0246642
Degree of Parallelism0
Memory Grant1024
Estimated Number of Rows4
Statement
insert [dbo].[T] select * from [dbo].[T] option (maxdop 1)
Warnings
The query memory grant detected "Excessive Grant", which may impact the reliability. Grant size: Initial 1024 KB, Final 1024 KB, Used 16 KB.
Index Insert
(Insert)
Cost: 41%
Index Insert
Physical OperationIndex Insert
Logical OperationInsert
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows0
Actual Number of Batches0
Estimated Operator Cost0.010004 (41%)
Estimated I/O Cost0.01
Estimated CPU Cost0.000004
Estimated Subtree Cost0.0246642
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID0
Object
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[IX]
Sort
Cost: 46%
Sort
Sort the input.
Physical OperationSort
Logical OperationSort
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows4
Actual Number of Batches0
Estimated Operator Cost0.0113738 (46%)
Estimated I/O Cost0.0112613
Estimated CPU Cost0.0001125
Estimated Subtree Cost0.0146602
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows4
Estimated Row Size16 B
Actual Rebinds1
Actual Rewinds0
Node ID1
Output List
RowRefSrc1007
Order By
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue Ascending
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2 Ascending
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1 Ascending
Index Scan (NonClustered)
[T].[ix]
Cost: 13%
Index Scan (NonClustered)
Physical OperationIndex Scan
Logical OperationIndex Scan
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read4
Actual Number of Rows4
Actual Number of Batches0
Estimated Operator Cost0.0032864 (13%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001614
Estimated Subtree Cost0.0032864
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read4
Estimated Number of Rows4
Estimated Row Size16 B
Actual Rebinds0
Actual Rewinds0
OrderedFalse
Node ID2
Output List
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue
Object
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[ix]
SELECT *
FROM T
WHERE someValue = 1
ORDER BY id1, id2;
id1 id2 someValue
1 2 True
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC" StatementId="1" StatementCompId="0" StatementType="SELECT" StatementSqlHandle="0x0900FF71E9F432FEAB29E1295E3D6DF62F350000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="false" StatementSubTreeCost="0.0032864" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0A9CA36655584DA5" QueryPlanHash="0x221E54358B8008D8" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="136"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.46" ModificationCount="0" SamplingPercent="100" Statistics="[IX]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[PK__T__21951B9C9F834CFB]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[someValue]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC
SELECT
SELECT
Cached plan size24 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0032864
Degree of Parallelism0
Estimated Number of Rows1
Statement
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id1] ASC,[id2] ASC
Clustered Index Scan (Clustered)
[T].[PK__T__21951B9C9F834CFB]
Cost: 100%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
Physical OperationClustered Index Scan
Logical OperationClustered Index Scan
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read4
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0032864 (100%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001614
Estimated Subtree Cost0.0032864
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read4
Estimated Number of Rows1
Estimated Row Size16 B
Actual Rebinds0
Actual Rewinds0
OrderedTrue
Node ID0
Output List
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue
Object
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[PK__T__21951B9C9F834CFB]
Predicate
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[someValue]=(1)
SELECT *
FROM T
WHERE someValue = 1
ORDER BY id2, id1;
id1 id2 someValue
1 2 True
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC" StatementId="1" StatementCompId="0" StatementType="SELECT" StatementSqlHandle="0x0900263E1184C192A8940324A9FA43A88B140000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="2" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x2BE8C280E057EAD6" QueryPlanHash="0xE8300CEA8D1E210C" CardinalityEstimationModelVersion="160"><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" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="120"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1353760"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2023-12-02T19:15:21.46" ModificationCount="0" SamplingPercent="100" Statistics="[IX]" Table="[T]" Schema="[dbo]" Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]"></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="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="id2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Index="[IX]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_9d5151a2438b4d9aa966a12215f81324]" Schema="[dbo]" Table="[T]" Column="someValue"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@1]"><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC
SELECT
SELECT
Cached plan size24 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost0.0032831
Degree of Parallelism0
Estimated Number of Rows1
Statement
SELECT * FROM [T] WHERE [someValue]=@1 ORDER BY [id2] ASC,[id1] ASC
Index Seek (NonClustered)
[T].[IX]
Cost: 100%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
Physical OperationIndex Seek
Logical OperationIndex Seek
Actual Execution ModeRow
Estimated Execution ModeRow
StorageRowStore
Number of Rows Read1
Actual Number of Rows1
Actual Number of Batches0
Estimated Operator Cost0.0032831 (100%)
Estimated I/O Cost0.003125
Estimated CPU Cost0.0001581
Estimated Subtree Cost0.0032831
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows to be Read1
Estimated Number of Rows1
Estimated Row Size16 B
Actual Rebinds0
Actual Rewinds0
OrderedTrue
Node ID0
Output List
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id1
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].id2
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue
Object
[fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].[IX]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_9d5151a2438b4d9aa966a12215f81324].[dbo].[T].someValue = Scalar Operator([@1])