8

Consider the below query:

CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key @pagesize AS BIGINT = 25 AS SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM dbo.Orders WHERE orderid > @orderid ORDER BY orderid; exec GetPage 25,25

SET STATISTICS IO for the above query returned:

(25 row(s) affected) Table 'Orders'. Scan count 1, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Explanation from Itzik Ben-Gan in his book goes like this for the above reads:

The I/O costs involved in the execution of the query plan are made of the following:

  • Seek to the leaf of index: 3 reads (the index has three levels).
  • Range scan of 25 rows: 0–1 reads (hundreds of rows fit in a page).
  • Nested Loops prefetch used to optimize lookups: 9 reads (measured by disabling prefetch with trace flag 8744)
  • 25 key lookups: 75 reads

Query plan

Execution plan

Now my question is, since nested loops does a key lookup once for each row returned from seek, should seek reads be 25*3 :75, the same as key lookups?

Query plan XML

<?xml version="1.0" encoding="utf-16"?> <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.4" Build="13.0.900.73" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="3" StatementEstRows="25" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0887816" StatementText=" CREATE PROC dbo.GetPage @orderid AS INT = 0, -- anchor sort key &#xD;&#xA; @pagesize AS BIGINT = 25 &#xD;&#xA; AS&#xD;&#xA;SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM dbo.Orders WHERE orderid &gt; @orderid ORDER BY orderid" StatementType="SELECT" QueryHash="0x48DC1D1D4649B914" QueryPlanHash="0x8FDC055F05E0E93C" RetrievedFromCache="true" SecurityPolicyApplied="false"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="32" CompileTime="2" CompileCPU="2" CompileMemory="208"> <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="52428" EstimatedPagesCached="13107" EstimatedAvailableDegreeOfParallelism="2" /> <RelOp AvgRowSize="29" EstimateCPU="2.5E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="25" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0887816"> <OutputList> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" /> </OutputList> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="[@pagesize]"> <Identifier> <ColumnReference Column="@pagesize" /> </Identifier> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="29" EstimateCPU="4.1799" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="25" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0887791"> <OutputList> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" /> </OutputList> <NestedLoops Optimized="false" WithOrderedPrefetch="true"> <OuterReferences> <ColumnReference Column="Uniq1001" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" /> <ColumnReference Column="Expr1003" /> </OuterReferences> <RelOp AvgRowSize="18" EstimateCPU="1.10013" EstimateIO="1.92683" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="25" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00335567" TableCardinality="1000000"> <OutputList> <ColumnReference Column="Uniq1001" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Column="Uniq1001" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" /> </DefinedValue> </DefinedValues> <Object Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Index="[PK_Orders]" IndexKind="NonClustered" Storage="RowStore" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <StartRange ScanType="GT"> <RangeColumns> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderid" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[@orderid]"> <Identifier> <ColumnReference Column="@orderid" /> </Identifier> </ScalarOperator> </RangeExpressions> </StartRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="22" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="25" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0853189" TableCardinality="1000000"> <OutputList> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" /> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" /> </OutputList> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> <DefinedValues> <DefinedValue> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="custid" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="empid" /> </DefinedValue> </DefinedValues> <Object Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Index="[idx_cl_od]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" /> <ColumnReference Column="Uniq1001" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[PerformanceV3].[dbo].[Orders].[orderdate]"> <Identifier> <ColumnReference Database="[PerformanceV3]" Schema="[dbo]" Table="[Orders]" Column="orderdate" /> </Identifier> </ScalarOperator> <ScalarOperator ScalarString="[Uniq1001]"> <Identifier> <ColumnReference Column="Uniq1001" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> <ParameterList> <ColumnReference Column="@pagesize" ParameterCompiledValue="(25)" /> <ColumnReference Column="@orderid" ParameterCompiledValue="(25)" /> </ParameterList> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>
Improve this question

1 Answer 1

Reset to default
9

Now my question is ,since nested loops does a key lookup once for each row returned from seek,should seek reads be 25*3 :75 same as key lookups

If the question is "should the seek also require 75 reads?" then the answer is no, for the reasons Itzik gave, and quoted in the question:

Seek to the leaf of index: 3 reads (the index has three levels) Range scan of 25 rows: 0–1 reads (hundreds of rows fit in a page)

The initial seek to find the starting position of the range scan (in the Index Seek operator) takes 3 reads. From that point on, the storage engine remembers the current position of the scan, so fetching the next Index Seek row requires zero or one read. Zero reads if the next row is on the same page; one read if it is on the next page.

The difference in behaviour is a common source of confusion, and one of the reasons I dislike logical reads as a performance metric.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.