Sql-Server

未在刪除語句中使用聚集索引

  • June 23, 2016

我有一個 SQL Server 表定義如下

CREATE TABLE [dbo].[Production_Detail]
(
   [Id] [bigint] NOT NULL DEFAULT (NEXT VALUE FOR [dbo].[Production_Detail_Seq]),
   [Meta_Data_ID] INT NOT NULL , 
   [Production_Detail_Time] DATETIME NOT NULL,
   [Production_Detail_Time_Local] DATETIME NOT NULL,
   [Production_Detail_Value] FLOAT NULL,
   [IntegratedDM] BIT NOT NULL DEFAULT 0,
   [DailyIntegratedDM] BIT NOT NULL DEFAULT 0,
   [InsertedDate] DateTime NOT NULL,
   [ModifiedDate] DateTime NOT NULL,  

   CONSTRAINT [PK_Production_Detail] PRIMARY KEY ([Id]), 

   CONSTRAINT [UK_Production_Detail] UNIQUE ([Meta_Data_Id], [Production_Detail_Time]),

   CONSTRAINT [FK_Production_Detail_To_Meta_Data] FOREIGN KEY ([Meta_Data_ID]) REFERENCES [Meta_Data]([Meta_Data_ID]) 
)
GO

CREATE NONCLUSTERED INDEX [IX_Production_Detail_Production_Detail_Time_Local]
ON [dbo].[Production_Detail] ([Production_Detail_Time_Local])
INCLUDE ([Meta_Data_ID], [Production_Detail_Time], [Production_Detail_Value])
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Production_Detail_Meta_Data_ID_Production_Detail_Time] 
ON [dbo].[Production_Detail] ([Meta_Data_ID] ASC, [Production_Detail_Time] ASC)
GO

CREATE NONCLUSTERED INDEX [IX_Production_Detail_IntegratedDM_MetaDataId]
ON [dbo].[Production_Detail] ([IntegratedDM], [Meta_Data_ID])
INCLUDE ([Production_Detail_Time])
GO

該表包含實時數據，因此記錄是一致插入的。我正在嘗試刪除超過 5 天的數據。但是當刪除執行時，我有幾個插入語句會引發死鎖。

這是刪除查詢。該#ToPurge表包含需要刪除的 ID。該#ToPurge表沒有任何索引。行數可能會有所不同，但對於我的測試案例，它有超過 300 萬行：

Declare @RowCount int;
set @RowCount = @@ROWCOUNT;

while @RowCount > 0
begin
   delete top(20000) pd
   from #ToPurge tp
   inner join [DataWarehouse].[dbo].[Production_Detail] pd on (tp.ID = pd.ID)

   set @RowCount = @@ROWCOUNT;
end

這是刪除查詢的執行計劃

在此處輸入圖像描述

<?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.2" Build="11.0.3128.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
 <BatchSequence>
   <Batch>
     <Statements>
       <StmtSimple StatementCompId="1" StatementEstRows="20000" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="34.7996" StatementText="   delete top(20000) pd
  from #ToPurge tp
  inner join [DataWarehouse].[dbo].[Production_Detail] pd on (tp.ID = pd.ID)" StatementType="DELETE" QueryHash="0xF7A9CF8E140CDB4E" QueryPlanHash="0x2CC61A780737A398" RetrievedFromCache="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="88" CompileTime="65" CompileCPU="65" CompileMemory="592">
           <ThreadStat Branches="2" />
           <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="477448" />
           <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209715" EstimatedPagesCached="419430" EstimatedAvailableDegreeOfParallelism="4" />
           <RelOp AvgRowSize="9" EstimateCPU="0.08" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="34.7996">
             <OutputList />
             <Sequence>
               <RelOp AvgRowSize="9" EstimateCPU="0.02" EstimateIO="0.458155" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Delete" NodeId="1" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="8.56272">
                 <OutputList />
                 <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
                   <Object Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Index="[UK_Production_Detail]" Alias="[pd]" IndexKind="NonClustered" />
                   <RelOp AvgRowSize="36" EstimateCPU="0.00182568" EstimateIO="0.00328238" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Eager Spool" NodeId="3" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="8.08456">
                     <OutputList>
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Meta_Data_ID" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time_Local" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="IntegratedDM" />
                     </OutputList>
                     <Spool>
                       <RelOp AvgRowSize="36" EstimateCPU="0.02" EstimateIO="1.31642" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Delete" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="32.3067">
                         <OutputList>
                           <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                           <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Meta_Data_ID" />
                           <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time" />
                           <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time_Local" />
                           <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="IntegratedDM" />
                         </OutputList>
                         <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
                           <Object Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Index="[PK_Production_Detail]" Alias="[pd]" IndexKind="Clustered" />
                           <RelOp AvgRowSize="15" EstimateCPU="0.002" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Top" NodeId="6" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="30.9703">
                             <OutputList>
                               <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                             </OutputList>
                             <Top RowCount="false" IsPercent="false" WithTies="false">
                               <TopExpression>
                                 <ScalarOperator ScalarString="(20000)">
                                   <Const ConstValue="(20000)" />
                                 </ScalarOperator>
                               </TopExpression>
                               <RelOp AvgRowSize="15" EstimateCPU="0.0392491" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Gather Streams" NodeId="7" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="30.9683">
                                 <OutputList>
                                   <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                 </OutputList>
                                 <Parallelism>
                                   <RelOp AvgRowSize="15" EstimateCPU="276.841" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Right Semi Join" NodeId="8" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="30.929">
                                     <OutputList>
                                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                     </OutputList>
                                     <MemoryFractions Input="1" Output="1" />
                                     <Hash>
                                       <DefinedValues />
                                       <HashKeysBuild>
                                         <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                       </HashKeysBuild>
                                       <HashKeysProbe>
                                         <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                       </HashKeysProbe>
                                       <ProbeResidual>
                                         <ScalarOperator ScalarString="[DataWarehouse].[dbo].[Production_Detail].[Id] as [pd].[Id]=[tempdb].[dbo].[#ToPurge].[Id] as [tp].[Id]">
                                           <Compare CompareOp="EQ">
                                             <ScalarOperator>
                                               <Identifier>
                                                 <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                               </Identifier>
                                             </ScalarOperator>
                                             <ScalarOperator>
                                               <Identifier>
                                                 <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                               </Identifier>
                                             </ScalarOperator>
                                           </Compare>
                                         </ScalarOperator>
                                       </ProbeResidual>
                                       <RelOp AvgRowSize="15" EstimateCPU="2.83429" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3910180" LogicalOp="Bitmap Create" NodeId="9" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="9.99726">
                                         <OutputList>
                                           <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                         </OutputList>
                                         <Bitmap>
                                           <DefinedValues>
                                             <DefinedValue>
                                               <ColumnReference Column="Bitmap1007" />
                                             </DefinedValue>
                                           </DefinedValues>
                                           <HashKeys>
                                             <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                           </HashKeys>
                                           <RelOp AvgRowSize="15" EstimateCPU="2.83429" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3910180" LogicalOp="Repartition Streams" NodeId="10" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="9.99726">
                                             <OutputList>
                                               <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                             </OutputList>
                                             <Parallelism PartitioningType="Hash">
                                               <PartitionColumns>
                                                 <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                               </PartitionColumns>
                                               <RelOp AvgRowSize="15" EstimateCPU="1.07532" EstimateIO="6.08765" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="3910180" LogicalOp="Table Scan" NodeId="11" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="7.16297" TableCardinality="3910180">
                                                 <OutputList>
                                                   <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                                 </OutputList>
                                                 <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false">
                                                   <DefinedValues>
                                                     <DefinedValue>
                                                       <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" Column="Id" />
                                                     </DefinedValue>
                                                   </DefinedValues>
                                                   <Object Database="[tempdb]" Schema="[dbo]" Table="[#ToPurge]" Alias="[tp]" IndexKind="Heap" />
                                                 </TableScan>
                                               </RelOp>
                                             </Parallelism>
                                           </RelOp>
                                         </Bitmap>
                                       </RelOp>
                                       <RelOp AvgRowSize="15" EstimateCPU="0.675882" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="902193" LogicalOp="Repartition Streams" NodeId="12" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="3.17684">
                                         <OutputList>
                                           <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                         </OutputList>
                                         <Parallelism PartitioningType="Hash">
                                           <PartitionColumns>
                                             <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                           </PartitionColumns>
                                           <RelOp AvgRowSize="15" EstimateCPU="43.216" EstimateIO="391.867" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="902193" LogicalOp="Index Scan" NodeId="13" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.50096" TableCardinality="157149000">
                                             <OutputList>
                                               <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                             </OutputList>
                                             <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                                               <DefinedValues>
                                                 <DefinedValue>
                                                   <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                                 </DefinedValue>
                                               </DefinedValues>
                                               <Object Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Index="[UK_Production_Detail]" Alias="[pd]" IndexKind="NonClustered" />
                                               <Predicate>
                                                 <ScalarOperator ScalarString="PROBE([Bitmap1007],[DataWarehouse].[dbo].[Production_Detail].[Id] as [pd].[Id],N'[IN ROW]')">
                                                   <Intrinsic FunctionName="PROBE">
                                                     <ScalarOperator>
                                                       <Identifier>
                                                         <ColumnReference Column="Bitmap1007" />
                                                       </Identifier>
                                                     </ScalarOperator>
                                                     <ScalarOperator>
                                                       <Identifier>
                                                         <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                                                       </Identifier>
                                                     </ScalarOperator>
                                                     <ScalarOperator>
                                                       <Const ConstValue="N'[IN ROW]'" />
                                                     </ScalarOperator>
                                                   </Intrinsic>
                                                 </ScalarOperator>
                                               </Predicate>
                                             </IndexScan>
                                           </RelOp>
                                         </Parallelism>
                                       </RelOp>
                                     </Hash>
                                   </RelOp>
                                 </Parallelism>
                               </RelOp>
                             </Top>
                           </RelOp>
                         </Update>
                       </RelOp>
                     </Spool>
                   </RelOp>
                 </Update>
               </RelOp>
               <RelOp AvgRowSize="9" EstimateCPU="0.02" EstimateIO="0.906841" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Delete" NodeId="16" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="9.0114">
                 <OutputList />
                 <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
                   <Object Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Index="[IX_Production_Detail_Production_Detail_Time_Local]" Alias="[pd]" IndexKind="NonClustered" />
                   <RelOp AvgRowSize="36" EstimateCPU="0.00182568" EstimateIO="0.00328238" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Eager Spool" NodeId="18" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="8.08456">
                     <OutputList>
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Meta_Data_ID" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time_Local" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="IntegratedDM" />
                     </OutputList>
                     <Spool PrimaryNodeId="3" />
                   </RelOp>
                 </Update>
               </RelOp>
               <RelOp AvgRowSize="9" EstimateCPU="0.02" EstimateIO="0.458164" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Delete" NodeId="19" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="8.56272">
                 <OutputList />
                 <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
                   <Object Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Index="[IX_Production_Detail_Meta_Data_ID_Production_Detail_Time]" Alias="[pd]" IndexKind="NonClustered" />
                   <RelOp AvgRowSize="36" EstimateCPU="0.00182568" EstimateIO="0.00328238" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Eager Spool" NodeId="21" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="8.08456">
                     <OutputList>
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Meta_Data_ID" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time_Local" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="IntegratedDM" />
                     </OutputList>
                     <Spool PrimaryNodeId="3" />
                   </RelOp>
                 </Update>
               </RelOp>
               <RelOp AvgRowSize="9" EstimateCPU="0.02" EstimateIO="0.478182" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Delete" NodeId="22" Parallel="false" PhysicalOp="Index Delete" EstimatedTotalSubtreeCost="8.58274">
                 <OutputList />
                 <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
                   <Object Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Index="[IX_Production_Detail_IntegratedDM_MetaDataId]" Alias="[pd]" IndexKind="NonClustered" />
                   <RelOp AvgRowSize="36" EstimateCPU="0.00182568" EstimateIO="0.00328238" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="20000" LogicalOp="Eager Spool" NodeId="24" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="8.08456">
                     <OutputList>
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Id" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Meta_Data_ID" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="Production_Detail_Time_Local" />
                       <ColumnReference Database="[DataWarehouse]" Schema="[dbo]" Table="[Production_Detail]" Alias="[pd]" Column="IntegratedDM" />
                     </OutputList>
                     <Spool PrimaryNodeId="3" />
                   </RelOp>
                 </Update>
               </RelOp>
             </Sequence>
           </RelOp>
         </QueryPlan>
       </StmtSimple>
     </Statements>
   </Batch>
 </BatchSequence>
</ShowPlanXML>

正如您在執行計劃中看到的那樣，優化器不使用主索引的聚集索引，而是使用不包含 Id 欄位的唯一鍵。我認為這就是我出現死鎖的原因，因為如果它使用正確的索引，它需要鎖定更多的頁面。

我嘗試使用 Id 添加一個非聚集索引，然後優化器使用它。

CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20160617-104555] 
ON [dbo].[Production_Detail] ([Id] ASC)    
GO

我想知道為什麼優化器不使用聚集索引，而是使用非聚集索引？

我想知道為什麼優化器不使用聚集索引，而是使用非聚集索引？

這將是基於成本的優化器的決定。它估計完全掃描窄索引更便宜。

您似乎期望在聚集索引上搜尋嵌套循環？

執行計劃顯示該表#ToPurge有 390 萬行。

這將需要 195 批才能清除。假設其中的所有行#ToPurge都是唯一的並且最初存在於Production_Detail其中並且輸出的行#ToPurge對於每個批次的順序一致（我忽略了並行性），那麼每個後續批次最終都會尋找之前已經刪除的所有行在最終到達尚未處理的行更新檔之前進行批處理，那麼您的delete程序總共將執行超過 1/3 的十億索引查找已刪除的行。

0 + 20,000 + 40,000 + ... + 3,880,000 = 378,300,000

這不是基於成本的優化器使用的邏輯（這將涉及兩個表的直方圖和 的行目標）TOP 20000，但它表明這無論如何都不是一個好策略。

您可以嘗試以下方法將每次迭代的搜尋次數限制在 20,000 次，然後看看您現在是否獲得了所需的計劃。

CREATE TABLE #ToPurge
 (
    [BatchId] [INT] NULL,
    [Id]      [BIGINT] NOT NULL,
    UNIQUE CLUSTERED([BatchId], [Id])
 );

/*Your insert statement to #ToPurge here*/

WITH CTE
    AS (SELECT *,
               1 + ( ROW_NUMBER() OVER (ORDER BY Id) / 20000 ) AS _BatchId
        FROM   #ToPurge)
UPDATE CTE
SET    [BatchId] = [_BatchId];

DECLARE @BatchCounter INT = 1;

WHILE @BatchCounter <= (SELECT MAX([BatchId])
                       FROM   #ToPurge)
 BEGIN
     DELETE pd
     FROM   #ToPurge tp
            INNER JOIN [DataWarehouse].[dbo].[Production_Detail] pd
              ON ( tp.ID = pd.ID )
     WHERE  tp.BatchId = @BatchCounter

     SET @BatchCounter+=1;
 END

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

相關問答

Sql-Server

創建表時創建聚集索引失敗

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

SQL Server 聚集索引高碎片

  • May 24, 2013
檢視問答
Sql-Server

非聚集主鍵和聚集索引

  • May 17, 2022
檢視問答
Sql-Server

索引中關鍵列的順序

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

為什麼此列有排序

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

聚集索引和排序

  • October 3, 2021
檢視問答