Sql-Server

為嵌套循環設置統計 I/O

  • July 24, 2018

考慮以下查詢：

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 返回：

(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.

Itzik Ben-Gan 在他的書中的解釋如下：

執行查詢計劃所涉及的 I/O 成本由以下各項組成：

  • 尋找索引的葉子：3次讀取（索引分為三個級別）。
  • 25 行的範圍掃描：0-1 次讀取（數百行適合一頁）。
  • 用於優化查找的嵌套循環預取：9 次讀取（通過使用跟踪標誌 8744 禁用預取來測量）
  • 25 鍵查找：75 次讀取

查詢計劃

執行計劃

現在我的問題是，由於嵌套循環對從 seek 返回的每一行執行一次鍵查找，所以 seek 讀取應該是 25*3 :75，與鍵查找相同嗎？

查詢計劃 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 
  @pagesize AS BIGINT = 25 
  AS
SELECT TOP (@pagesize) orderid, orderdate, custid, empid FROM dbo.Orders WHERE orderid > @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>

現在我的問題是，由於嵌套循環對從 seek 返回的每一行執行一次鍵查找，所以 seek 讀取應該是 25*3 :75 與鍵查找相同

如果問題是“搜尋是否也需要 75 次讀取？” 那麼答案是否定的，因為 Itzik 給出的原因並在問題中引用：

> > 尋找索引的葉子：3 次讀取（索引分為三個級別） 25 行的範圍掃描：0-1 次讀取（數百行適合一頁） > > >

查找範圍掃描的起始位置的初始查找（在 Index Seek 運算符中）需要 3 次讀取。從那時起，儲存引擎會記住掃描的目前位置，因此獲取下一個 Index Seek 行需要零次或一次讀取。如果下一行在同一頁上，則讀取零；如果它在下一頁上，請閱讀。

行為上的差異是混淆的常見來源，也是我不喜歡將邏輯讀取作為性能指標的原因之一。

引用自：https://dba.stackexchange.com/questions/134172

相關問答

Sql-Server

where 子句中的 dateadd - 在這種特殊情況下如何避免完全掃描？

  • March 30, 2022
檢視問答
Sql-Server

為什麼 sys.dm_exec_query_stats 中缺少大多數記憶體計劃？

  • March 22, 2022
檢視問答
Sql-Server

mslid 上的非集群索引查找速度很慢

  • March 16, 2022
檢視問答
Sql-Server

執行計劃中的點陣圖創建導致聚集索引掃描的錯誤估計

  • January 28, 2022
檢視問答
Sql-Server

在哪裡表現受到打擊

  • December 10, 2021
檢視問答
Sql-Server

Columnstore：糟糕的執行計劃 - 過濾而不是搜尋

  • December 3, 2021
檢視問答