I have a SQL Server table defined as follow
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
This table contains real time data, so records are consistently inserted. I'm trying to delete data that are older than 5 days. But when the delete is running, I have several insert statement that throws deadlock.
Here's the delete query. The #ToPurge
table contains the ID that needs to be deleted. The #ToPurge
table doesn't have any indexes. The number of rows may vary, but for my test case it had over 3 million:
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
Here's the execution plan from the delete query
<?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>
As you can see in the execution plan, the optimizer doesn't use the clustered index of the primary, but instead uses the Unique key which does not contain the Id field. I think this is the reason why I have deadlock since it needs to lock more page that if it was using the right index.
I try to add a non-clustered index with the Id and after that the optimizer is using it.
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20160617-104555]
ON [dbo].[Production_Detail] ([Id] ASC)
GO
I'd like to know why the optimizer does not use the clustered index, but is using the non-clustered one?
Best Answer
This will be a decision of the cost based optimizer. It estimates that it is cheaper to fully scan the narrow index.
It seems that you were expecting a nested loops with seeks on the clustered index?
The execution plan shows that the table
#ToPurge
has 3.9 million rows.This will take 195 batches to clear out. Assuming that all rows in
#ToPurge
are unique and initially exist inProduction_Detail
and that the rows output from#ToPurge
are in a consistent order for each batch (I'm ignoring the parallelism) then each successive batch will end up seeking all rows that have already been deleted in previous batches before finally getting to a patch of rows that have not yet been processed then in total yourdelete
process will do more than a third of a billion index seeks for already deleted rows.This isn't the logic that the cost based optimizer uses (which will involve histograms of the two tables and a rowgoal for the
TOP 20000
) but it shows this is not a good strategy anyway.You could try the following to cap the number of seeks at 20,000 per iteration and see if you now get your desired plan.