Sql-Server

SQL Server 2008r2、2017 和 2019 之間的查詢執行異常

  • November 25, 2019

我有一個非常基本的查詢，在 SQL Server 2008r2 上使用索引搜尋執行得非常好。當我們遷移到 SQL Server 2017 時，它開始表現更差，現在在查詢中對 Remote_Records 進行聚集索引掃描，以前從未遇到過問題，因此導致我們的查詢的其餘部分執行一分鐘或更長時間，這取決於該函式應用於多少條記錄。在我們升級之前，這僅在幾秒鐘內執行。將其切換回使用舊的基數估計器會更改計劃，但這是我們希望避免的選項。

我還針對 SQL Server 2019 對其進行了測試，它返回到與 SQL 2008r2 相同類型的計劃，除了 Local_Record_Additional_Data 上的聚集索引搜尋現在在 RowStore 處理上使用批處理模式來獲取搜尋。

我可以通過在 SQL 2017 中的查詢上使用 TOP 1 來使查詢不使用 Remote_Records 上的聚集索引掃描，但是由於這在 SQL 2008r2 中有效而無需任何額外修改，我有點不知所措為什麼基數估計器在這種情況下關閉。

此查詢是更大的內聯表值函式的一部分，該函式應用於給定的 id 值以收集我們想要顯示的某些數據。這是功能中唯一存在問題的部分。

任何意見，將不勝感激。

以下是執行計劃連結： 原始 SQL 2008r2 計劃：https ://www.brentozar.com/pastetheplan/?id=S1G4pnK2H

原始 SQL 2017 計劃：https ://www.brentozar.com/pastetheplan/?id=B1ALKXQ2S

帶有 TF 9481 的 SQL 2017：https ://www.brentozar.com/pastetheplan/?id=ByguM2Y2S

Microsoft 尚未發布 SQL 2019 的 DTD，我已在下麵包含 SQL 2019 計劃的 XML。

生成表和數據集的程式碼以反映我們在生產中看到的內容：

CREATE TABLE Remote_Records  
(
   Remote_record_id int identity (1,1) PRIMARY KEY CLUSTERED,
   Bit_1 bit,
   Bit_2 bit
)
GO
CREATE TABLE Local_Record_Additional_Data 
(
   Local_record_id int identity(1,1) PRIMARY KEY CLUSTERED,
   Status_data varchar(100),
   Device_data varchar(25)
)
GO
CREATE TABLE Remote_Additional_Data 
(
   Remote_add_data_id int identity(1,1) PRIMARY KEY CLUSTERED,
   Remote_record_id int NOT NULL CONSTRAINT FK_Remote_Additional_Data_Remote_Records_Remote_record_id  FOREIGN KEY (Remote_record_id) REFERENCES Remote_Records(Remote_record_id),
   Local_record_add_id int NULL CONSTRAINT FK_Remote_Additional_Data_Local_Record_Additional_Data FOREIGN KEY (Local_record_add_id) REFERENCES Local_Record_Additional_Data(Local_record_id)
)
GO



DECLARE @count int = 1
WHILE @count <=319756
BEGIN
   INSERT INTO Remote_Records
   SELECT 0,0
   SET @count = @count + 1
END
GO

DECLARE @count int = 1
WHILE @count <=457
BEGIN
   INSERT INTO Local_Record_Additional_Data(Status_data,Device_data)
   SELECT NULL,NULL
   SET @count = @count + 1
END
GO


DECLARE @count int = 2
DECLARE @count_2 int = 1
DECLARE @rr_id int
DECLARE @max_rr_id int 
DECLARE @rad_id int = 1
DECLARE @lrad_id int
SELECT @max_rr_id = max(Remote_record_id) FROM Remote_Records
WHILE @count <= 486004
BEGIN
   IF @count > @max_rr_id
   BEGIN
       SET @count_2 = @count_2 + 1
   END

   IF @count >= 486004-456
   BEGIN
       SET @lrad_id = ISNULL(@lrad_id,0) + 1
   END
   SELECT @rr_id = CASE WHEN @count > @max_rr_id THEN @count_2 ELSE @count END

   INSERT INTO Remote_Additional_Data(Remote_record_id, Local_record_add_id)
   SELECT @rr_id, @lrad_id

   SET @count = @count + 1

END
GO
sp_createstats 'NO','NO','NORECOMPUTE'
GO

UPDATE STATISTICS Remote_Records WITH FULLSCAN
GO
UPDATE STATISTICS Local_Record_Additional_Data WITH FULLSCAN
GO
UPDATE STATISTICS Remote_Additional_Data WITH FULLSCAN
GO
CREATE INDEX IX_Remote_Additional_Data_Remote_record_id ON [Remote_Additional_Data](Remote_record_id) INCLUDE (Local_record_add_id) 
GO
CREATE INDEX IX_Remote_Additional_Data_Local_record_add_id ON [Remote_Additional_Data](Local_record_add_id) INCLUDE (Remote_record_id) 
GO

這是問題查詢：

declare @lra_id int = 267
SELECT  Local_record_add_id,
       Bit_1,
       Bit_2
       FROM Remote_records se 
       INNER JOIN Remote_Additional_Data foe ON se.Remote_record_id = foe.Remote_record_id
       INNER JOIN Local_Record_Additional_Data foh on foe.Local_record_add_id = foh.Local_record_id
       WHERE foe.Local_record_add_id = @lra_id
go

SQL 2019 執行計劃 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.539" Build="15.0.2000.5" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
 <BatchSequence>
   <Batch>
     <Statements>
       <StmtSimple StatementCompId="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.00985766" StatementText="SELECT  Local_record_add_id,
      Bit_1,
        Bit_2
     FROM Remote_records se 
       INNER JOIN Remote_Additional_Data foe ON se.Remote_record_id = foe.Remote_record_id
       INNER JOIN Local_Record_Additional_Data foh on foe.Local_record_add_id = foh.Local_record_id
      WHERE foe.Local_record_add_id = @lra_id" StatementType="SELECT" QueryHash="0x6825DB46BFFA067E" QueryPlanHash="0xE4DD43257F5DD402" RetrievedFromCache="false" SecurityPolicyApplied="false" BatchModeOnRowStoreUsed="true">
         <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
         <QueryPlan DegreeOfParallelism="1" CachedPlanSize="32" CompileTime="8" CompileCPU="8" CompileMemory="376">
           <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
           <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="102400" EstimatedPagesCached="153600" EstimatedAvailableDegreeOfParallelism="12" MaxCompileMemory="18406536" />
           <OptimizerStatsUsage>
             <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[Local_record_add_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:16.28" />
             <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[IX_Remote_Additional_Data_Remote_record_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:16.5" />
             <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[Remote_record_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:15.86" />
             <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Statistics="[IX_Remote_Additional_Data_Local_record_add_id]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:17.78" />
             <StatisticsInfo Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Statistics="[PK__Remote_R__FEF044CF617642DF]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-11-21T13:25:14.75" />
           </OptimizerStatsUsage>
           <QueryTimeStats CpuTime="0" ElapsedTime="0" />
           <RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985766">
             <OutputList>
               <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
               <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
               <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
             </OutputList>
             <RunTimeInformation>
               <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
             </RunTimeInformation>
             <NestedLoops Optimized="false">
               <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="457">
                 <OutputList />
                 <RunTimeInformation>
                   <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                 </RunTimeInformation>
                 <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                   <DefinedValues />
                   <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Local_Record_Additional_Data]" Index="[PK__Local_Re__E377E55CA10BE177]" Alias="[foh]" IndexKind="Clustered" Storage="RowStore" />
                   <SeekPredicates>
                     <SeekPredicateNew>
                       <SeekKeys>
                         <Prefix ScanType="EQ">
                           <RangeColumns>
                             <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Local_Record_Additional_Data]" Alias="[foh]" Column="Local_record_id" />
                           </RangeColumns>
                           <RangeExpressions>
                             <ScalarOperator ScalarString="[@lra_id]">
                               <Identifier>
                                 <ColumnReference Column="@lra_id" />
                               </Identifier>
                             </ScalarOperator>
                           </RangeExpressions>
                         </Prefix>
                       </SeekKeys>
                     </SeekPredicateNew>
                   </SeekPredicates>
                 </IndexScan>
               </RelOp>
               <RelOp AvgRowSize="12" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
                 <OutputList>
                   <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                   <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                   <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                 </OutputList>
                 <RunTimeInformation>
                   <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
                 </RunTimeInformation>
                 <NestedLoops Optimized="false">
                   <OuterReferences>
                     <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                   </OuterReferences>
                   <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Index Seek" NodeId="4" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="486003">
                     <OutputList>
                       <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                       <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                     </OutputList>
                     <RunTimeInformation>
                       <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                     </RunTimeInformation>
                     <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                       <DefinedValues>
                         <DefinedValue>
                           <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                         </DefinedValue>
                         <DefinedValue>
                           <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                         </DefinedValue>
                       </DefinedValues>
                       <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Index="[IX_Remote_Additional_Data_Local_record_add_id]" Alias="[foe]" IndexKind="NonClustered" Storage="RowStore" />
                       <SeekPredicates>
                         <SeekPredicateNew>
                           <SeekKeys>
                             <Prefix ScanType="EQ">
                               <RangeColumns>
                                 <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Local_record_add_id" />
                               </RangeColumns>
                               <RangeExpressions>
                                 <ScalarOperator ScalarString="[@lra_id]">
                                   <Identifier>
                                     <ColumnReference Column="@lra_id" />
                                   </Identifier>
                                 </ScalarOperator>
                               </RangeExpressions>
                             </Prefix>
                           </SeekKeys>
                         </SeekPredicateNew>
                       </SeekPredicates>
                     </IndexScan>
                   </RelOp>
                   <RelOp AvgRowSize="9" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="319756">
                     <OutputList>
                       <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                       <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                     </OutputList>
                     <RunTimeInformation>
                       <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
                     </RunTimeInformation>
                     <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                       <DefinedValues>
                         <DefinedValue>
                           <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_1" />
                         </DefinedValue>
                         <DefinedValue>
                           <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Bit_2" />
                         </DefinedValue>
                       </DefinedValues>
                       <Object Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Index="[PK__Remote_R__FEF044CF617642DF]" Alias="[se]" IndexKind="Clustered" Storage="RowStore" />
                       <SeekPredicates>
                         <SeekPredicateNew>
                           <SeekKeys>
                             <Prefix ScanType="EQ">
                               <RangeColumns>
                                 <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Records]" Alias="[se]" Column="Remote_record_id" />
                               </RangeColumns>
                               <RangeExpressions>
                                 <ScalarOperator ScalarString="[Stats_Testing].[dbo].[Remote_Additional_Data].[Remote_record_id] as [foe].[Remote_record_id]">
                                   <Identifier>
                                     <ColumnReference Database="[Stats_Testing]" Schema="[dbo]" Table="[Remote_Additional_Data]" Alias="[foe]" Column="Remote_record_id" />
                                   </Identifier>
                                 </ScalarOperator>
                               </RangeExpressions>
                             </Prefix>
                           </SeekKeys>
                         </SeekPredicateNew>
                       </SeekPredicates>
                     </IndexScan>
                   </RelOp>
                 </NestedLoops>
               </RelOp>
             </NestedLoops>
           </RelOp>
           <ParameterList>
             <ColumnReference Column="@lra_id" ParameterDataType="int" ParameterRuntimeValue="(267)" />
           </ParameterList>
         </QueryPlan>
       </StmtSimple>
     </Statements>
   </Batch>
 </BatchSequence>
</ShowPlanXML>

看起來這已由 Microsoft 通過 2017 年的修補程序之一修復。啟用 TF 4199 後，執行計劃切換回我們的預期。

據我所知，最大的問題是您的統計數據。在 SQL2008 查詢中，索引 seek into Remote_Additional_DatafromLocal_Additional_Record_Data只期望返回一行，而在 2017 下，它期望返回一千多條記錄。這將之前的嵌套循環連接更改為合併連接（這對於它們之間的大行差異可能是不正確的，但仍然有可能匹配超過 1000 行）。

因此，您的基本問題似乎是統計維護或錯誤數據。它看起來不像查詢本身的實際問題。

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

相關問答

Sql-Server

生產中的查詢慢、執行計劃錯誤或索引錯誤？

  • August 19, 2019
檢視問答
Sql-Server

只讀副本上的長時間執行查詢，在主副本上需要一些時間

  • May 10, 2019
檢視問答
Sql-Server

誰能幫我完成這個糟糕的查詢計劃？

  • April 26, 2019
檢視問答
Sql-Server

相同邏輯的不同表現形式會產生非常不同的執行時間

  • February 16, 2019
檢視問答
Sql-Server

努力為此查詢創建有效的索引

  • February 6, 2019
檢視問答
Sql-Server

為什麼 DELETE 查詢以一種格式執行的時間比另一種格式長得多？

  • February 6, 2019
檢視問答