sql-server - 如何解决主要插入数据的事务之间的死锁？

Question

我们有两个 Web 应用程序在同一个数据库上工作的情况。它们都使用 TransactionScope(在 c# 中)来封装导入例程。

我们从每个事务范围内的两个语句中得到一个死锁。第一个是:

DELETE FROM Period WHERE CompanyId = 72

第二个是:

insert bulk Amount ([AccountId] Int, [PeriodId] Int, [Value] Decimal(18,2)) with (CHECK_CONSTRAINTS)

这是第一个被杀死的语句。谁能建议如何解决这个僵局？

其他几个兴趣点:

事务范围是 IsolationLevel.ReadCommitted。

数据库是ReadCommitted。

我已经用 READ_COMMITTED_SNAPSHOT 尝试过，但没有任何区别。

从第二条语句可以看出，我们在事务过程中使用的是 sqlbulkcopy。

大多数情况下，这些应用程序的读取量很大，但是在导入新数据时，它们会时不时地进行密集的写入。我希望 ReadCommitted 是正确的隔离级别。

我不明白为什么插入到数量表中需要对期间聚集索引/表进行排他锁。当然它只是在读取索引。

数据库结构为:

公司

ID

期间

编号，公司编号

帐号

ID

金额

PeriodId,AccountId,值

公司与期间、期间与金额、公司与账户之间存在级联删除。

死锁 XML

<deadlock-list>
 <deadlock victim="process7373048">
  <process-list>
   <process id="process7373048" taskpriority="0" logused="53608" waitresource="KEY: 5:72057594040811520 (55caf8cfebcf)" waittime="262" ownerId="21707350" transactionname="user_transaction" lasttranstarted="2011-11-11T14:26:47.660" XDES="0x72af950" lockMode="U" schedulerid="1" kpid="1212" status="suspended" spid="57" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-11-11T14:26:49.273" lastbatchcompleted="2011-11-11T14:26:49.273" clientapp=".Net SqlClient Data Provider" hostname="Fathom-1" hostpid="3396" loginname="fathom_WebUser" isolationlevel="read committed (2)" xactid="21707350" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x02000000bbf5661cad04d2a71e72d51fe7835afad57a3259">
DELETE FROM Period WHERE CompanyId = 105 AND Type = 7 AND DateRangeId IN (
                    SELECT Id 
                    FROM DateRange
                    WHERE Start &gt;= &apos;20110101 00:00:00&apos;)     </frame>
    </executionStack>
    <inputbuf>
DELETE FROM Period WHERE CompanyId = 105 AND Type = 7 AND DateRangeId IN (
                    SELECT Id 
                    FROM DateRange
                    WHERE Start &gt;= &apos;20110101 00:00:00&apos;)    </inputbuf>
   </process>
   <process id="process2c37e508" taskpriority="0" logused="570716" waitresource="KEY: 5:72057594040352768 (a559124170e7)" waittime="1330" ownerId="21707151" transactionname="user_transaction" lasttranstarted="2011-11-11T14:26:46.640" XDES="0x7207950" lockMode="S" schedulerid="1" kpid="3068" status="suspended" spid="58" sbid="2" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-11-11T14:26:48.203" lastbatchcompleted="2011-11-11T14:26:48.203" clientapp=".Net SqlClient Data Provider" hostname="Fathom-1" hostpid="3396" loginname="fathom_WebUser" isolationlevel="read committed (2)" xactid="21707151" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="adhoc" line="1" sqlhandle="0x020000001672aa048fceb23bce7f8c54178cbbe497dba1a6">
insert bulk Amount ([AccountId] Int, [PeriodId] Int, [Value] Decimal(18,2)) with (CHECK_CONSTRAINTS)     </frame>
    </executionStack>
    <inputbuf>
insert bulk Amount ([AccountId] Int, [PeriodId] Int, [Value] Decimal(18,2)) with (CHECK_CONSTRAINTS)    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594040811520" dbid="5" objectname="fathom.dbo.Period" indexname="PK_Period" id="lock2ab24500" mode="X" associatedObjectId="72057594040811520">
    <owner-list>
     <owner id="process2c37e508" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process7373048" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594040352768" dbid="5" objectname="fathom.dbo.Account" indexname="PK_Account" id="lock1ce65980" mode="X" associatedObjectId="72057594040352768">
    <owner-list>
     <owner id="process7373048" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process2c37e508" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>
</deadlock-list>

sert中批量的执行计划(右侧)

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2500.0"><BatchSequence><Batch><Statements><StmtSimple><QueryPlan CachedPlanSize="40" CompileTime="4" CompileCPU="4" CompileMemory="288"><RelOp NodeId="1" PhysicalOp="Assert" LogicalOp="Assert" EstimateRows="8100" EstimateIO="0" EstimateCPU="0.005508" AvgRowSize="9" EstimatedTotalSubtreeCost="4.81146" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><Assert StartupExpression="0"><RelOp NodeId="2" PhysicalOp="Merge Join" LogicalOp="Left Semi Join" EstimateRows="8100" EstimateIO="0.025979" EstimateCPU="0.0311493" AvgRowSize="9" EstimatedTotalSubtreeCost="4.80595" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Column="Expr1012"/><ColumnReference Column="Expr1013"/></OutputList><Merge ManyToMany="1"><InnerSideJoinColumns><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fathom].[dbo].[Amount].[PeriodId]=[fathom].[dbo].[Period].[Id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="9000" EstimateIO="0.0112613" EstimateCPU="0.465821" AvgRowSize="12" EstimatedTotalSubtreeCost="4.74537" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/><ColumnReference Column="Expr1012"/></OutputList><MemoryFractions Input="0.42446" Output="1"/><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></OrderByColumn></OrderBy><RelOp NodeId="4" PhysicalOp="Merge Join" LogicalOp="Left Semi Join" EstimateRows="9000" EstimateIO="0.027857" EstimateCPU="0.0353131" AvgRowSize="12" EstimatedTotalSubtreeCost="4.26829" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/><ColumnReference Column="Expr1012"/></OutputList><Merge ManyToMany="1"><InnerSideJoinColumns><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Account]" Column="Id"/></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="AccountId"/></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[fathom].[dbo].[Amount].[AccountId]=[fathom].[dbo].[Account].[Id]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="AccountId"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Account]" Column="Id"/></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="5" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="10000" EstimateIO="0.201195" EstimateCPU="0.01" AvgRowSize="15" EstimatedTotalSubtreeCost="4.19121" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="AccountId"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></OutputList><Update DMLRequestSort="1"><Object Database="[fathom]" Schema="[dbo]" Table="[Amount]" Index="[PK_Amount]" IndexKind="Clustered"/><SetPredicate><ScalarOperator ScalarString="[fathom].[dbo].[Amount].[AccountId] = RaiseIfNullInsert([!BulkInsert].[AccountId]),[fathom].[dbo].[Amount].[PeriodId] = RaiseIfNullInsert([!BulkInsert].[PeriodId]),[fathom].[dbo].[Amount].[Value] = RaiseIfNullInsert([Expr1005])"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="AccountId"/><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Table="[!BulkInsert]" Column="AccountId"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Table="[!BulkInsert]" Column="PeriodId"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="Value"/><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="6" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="10000" EstimateIO="0.0112613" EstimateCPU="0.60342" AvgRowSize="24" EstimatedTotalSubtreeCost="3.98001" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[!BulkInsert]" Column="AccountId"/><ColumnReference Table="[!BulkInsert]" Column="PeriodId"/><ColumnReference Column="Expr1005"/></OutputList><MemoryFractions Input="1" Output="0.57554"/><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Table="[!BulkInsert]" Column="AccountId"/></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Table="[!BulkInsert]" Column="PeriodId"/></OrderByColumn></OrderBy><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10000" EstimateIO="0" EstimateCPU="0.001" AvgRowSize="24" EstimatedTotalSubtreeCost="3.36533" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[!BulkInsert]" Column="AccountId"/><ColumnReference Table="[!BulkInsert]" Column="PeriodId"/><ColumnReference Column="Expr1005"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"/><ScalarOperator ScalarString="CONVERT_IMPLICIT(decimal(18,2),[!BulkInsert].[Value],0)"><Convert DataType="decimal" Precision="18" Scale="2" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Table="[!BulkInsert]" Column="Value"/></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Top" LogicalOp="Top" EstimateRows="10000" EstimateIO="0" EstimateCPU="0.001" AvgRowSize="24" EstimatedTotalSubtreeCost="3.36433" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[!BulkInsert]" Column="AccountId"/><ColumnReference Table="[!BulkInsert]" Column="PeriodId"/><ColumnReference Table="[!BulkInsert]" Column="Value"/></OutputList><Top RowCount="1" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"/></ScalarOperator></TopExpression><RelOp NodeId="9" PhysicalOp="Remote Scan" LogicalOp="Remote Scan" EstimateRows="10000" EstimateIO="0" EstimateCPU="3.36333" AvgRowSize="24" EstimatedTotalSubtreeCost="3.36333" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Table="[!BulkInsert]" Column="AccountId"/><ColumnReference Table="[!BulkInsert]" Column="PeriodId"/><ColumnReference Table="[!BulkInsert]" Column="Value"/></OutputList><RemoteScan RemoteObject="STREAM"/></RelOp></Top></RelOp></ComputeScalar></RelOp></Sort></RelOp></Update></RelOp><RelOp NodeId="14" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="907" EstimateIO="0.0127546" EstimateCPU="0.0011547" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0139093" TableCardinality="907" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Account]" Column="Id"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Account]" Column="Id"/></DefinedValue></DefinedValues><Object Database="[fathom]" Schema="[dbo]" Table="[Account]" Index="[PK_Account]" IndexKind="Clustered"/></IndexScan></RelOp></Merge></RelOp></Sort></RelOp><RelOp NodeId="16" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="150" EstimateIO="0.003125" EstimateCPU="0.000322" AvgRowSize="11" EstimatedTotalSubtreeCost="0.003447" TableCardinality="150" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></DefinedValue></DefinedValues><Object Database="[fathom]" Schema="[dbo]" Table="[Period]" Index="[PK_Period]" IndexKind="Clustered"/></IndexScan></RelOp></Merge></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN [Expr1012] IS NULL THEN (0) ELSE CASE WHEN [Expr1013] IS NULL THEN (1) ELSE NULL END END"><IF><Condition><ScalarOperator><Logical Operation="IS NULL"><ScalarOperator><Identifier><ColumnReference Column="Expr1012"/></Identifier></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"/></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Logical Operation="IS NULL"><ScalarOperator><Identifier><ColumnReference Column="Expr1013"/></Identifier></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Predicate></Assert></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

左侧执行计划(删除)

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2500.0"><BatchSequence><Batch><Statements><StmtSimple><QueryPlan CachedPlanSize="104" CompileTime="9" CompileCPU="9" CompileMemory="544"><MissingIndexes><MissingIndexGroup Impact="34.2748"><MissingIndex Database="[fathom]" Schema="[dbo]" Table="[Amount]"><ColumnGroup Usage="EQUALITY"><Column Name="[PeriodId]" ColumnId="2"/></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[AccountId]" ColumnId="1"/></ColumnGroup></MissingIndex></MissingIndexGroup><MissingIndexGroup Impact="46.6451"><MissingIndex Database="[fathom]" Schema="[dbo]" Table="[Result]"><ColumnGroup Usage="EQUALITY"><Column Name="[PeriodId]" ColumnId="2"/></ColumnGroup><ColumnGroup Usage="INCLUDE"><Column Name="[MetricId]" ColumnId="1"/></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><RelOp NodeId="1" PhysicalOp="Sequence" LogicalOp="Sequence" EstimateRows="119.191" EstimateIO="0" EstimateCPU="0.000233964" AvgRowSize="9" EstimatedTotalSubtreeCost="0.49074" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><Sequence><RelOp NodeId="2" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.54684e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00431261" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><Spool><RelOp NodeId="3" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.01685" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><Update DMLRequestSort="0"><Object Database="[fathom]" Schema="[dbo]" Table="[Period]" Index="[PK_Period]" IndexKind="Clustered"/><RelOp NodeId="4" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00684904" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><Top RowCount="1" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"/></ScalarOperator></TopExpression><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00684894" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="DateRangeId"/></OuterReferences><RelOp NodeId="7" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0003121" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0034371" TableCardinality="141" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="DateRangeId"/></OutputList><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></DefinedValue><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="DateRangeId"/></DefinedValue></DefinedValues><Object Database="[fathom]" Schema="[dbo]" Table="[Period]" Index="[PK_Period]" IndexKind="Clustered"/><Predicate><ScalarOperator ScalarString="[fathom].[dbo].[Period].[CompanyId]=(118) AND [fathom].[dbo].[Period].[Type]=(10)"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="CompanyId"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(118)"/></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Type"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(10)"/></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="10" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="2554" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0"><DefinedValues/><Object Database="[fathom]" Schema="[dbo]" Table="[DateRange]" Index="[PK_DateRange]" IndexKind="Clustered"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[DateRange]" Column="Id"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fathom].[dbo].[Period].[DateRangeId]"><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="DateRangeId"/></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fathom].[dbo].[DateRange].[Start]&gt;=&apos;2011-07-01&apos;"><Compare CompareOp="GE"><ScalarOperator><Identifier><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[DateRange]" Column="Start"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2011-07-01&apos;"/></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Top></RelOp></Update></RelOp></Spool></RelOp><RelOp NodeId="11" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="107.939" EstimateIO="0.0116206" EstimateCPU="0.000107939" AvgRowSize="9" EstimatedTotalSubtreeCost="0.261311" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><Update DMLRequestSort="1"><Object Database="[fathom]" Schema="[dbo]" Table="[Result]" Index="[PK_RatioResult]" IndexKind="Clustered"/><RelOp NodeId="13" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="107.939" EstimateIO="0.0112613" EstimateCPU="0.0012373" AvgRowSize="19" EstimatedTotalSubtreeCost="0.249583" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="MetricId"/></OutputList><MemoryFractions Input="1" Output="1"/><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="MetricId"/></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OrderByColumn></OrderBy><RelOp NodeId="14" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="107.939" EstimateIO="0" EstimateCPU="0.0863485" AvgRowSize="19" EstimatedTotalSubtreeCost="0.237084" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="MetricId"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="PeriodId"/></OutputList><MemoryFractions Input="0" Output="0"/><Hash><DefinedValues/><HashKeysBuild><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></HashKeysBuild><HashKeysProbe><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="PeriodId"/></HashKeysProbe><RelOp NodeId="15" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.54684e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00431261" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><Spool PrimaryNodeId="2"/></RelOp><RelOp NodeId="16" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="14974" EstimateIO="0.129792" EstimateCPU="0.0166284" AvgRowSize="15" EstimatedTotalSubtreeCost="0.14642" TableCardinality="14974" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="MetricId"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="PeriodId"/></OutputList><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="MetricId"/></DefinedValue><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Result]" Column="PeriodId"/></DefinedValue></DefinedValues><Object Database="[fathom]" Schema="[dbo]" Table="[Result]" Index="[PK_RatioResult]" IndexKind="Clustered"/></IndexScan></RelOp></Hash></RelOp></Sort></RelOp></Update></RelOp><RelOp NodeId="21" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="5.83333" EstimateIO="0.01" EstimateCPU="5.83333e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0383844" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><Update DMLRequestSort="0"><Object Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Index="[PK_PeriodAlert]" IndexKind="Clustered"/><RelOp NodeId="22" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="5.83333" EstimateIO="0" EstimateCPU="0.0184047" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0283786" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Column="AlertId"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Column="PeriodId"/></OutputList><MemoryFractions Input="0" Output="0"/><Hash><DefinedValues/><HashKeysBuild><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></HashKeysBuild><HashKeysProbe><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Column="PeriodId"/></HashKeysProbe><RelOp NodeId="23" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.54684e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00431261" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><Spool PrimaryNodeId="2"/></RelOp><RelOp NodeId="24" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="140" EstimateIO="0.00534722" EstimateCPU="0.000311" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00565822" TableCardinality="140" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Column="AlertId"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Column="PeriodId"/></OutputList><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Column="AlertId"/></DefinedValue><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Column="PeriodId"/></DefinedValue></DefinedValues><Object Database="[fathom]" Schema="[dbo]" Table="[PeriodAlert]" Index="[PK_PeriodAlert]" IndexKind="Clustered"/></IndexScan></RelOp></Hash></RelOp></Update></RelOp><RelOp NodeId="26" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="119.191" EstimateIO="0.01" EstimateCPU="0.000119191" AvgRowSize="9" EstimatedTotalSubtreeCost="0.186497" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList/><Update DMLRequestSort="0"><Object Database="[fathom]" Schema="[dbo]" Table="[Amount]" Index="[PK_Amount]" IndexKind="Clustered"/><RelOp NodeId="27" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="119.191" EstimateIO="0" EstimateCPU="0.0947385" AvgRowSize="15" EstimatedTotalSubtreeCost="0.176378" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="AccountId"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></OutputList><MemoryFractions Input="0" Output="0"/><Hash><DefinedValues/><HashKeysBuild><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></HashKeysBuild><HashKeysProbe><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></HashKeysProbe><RelOp NodeId="28" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.54684e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00431261" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Period]" Column="Id"/></OutputList><Spool PrimaryNodeId="2"/></RelOp><RelOp NodeId="29" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="16806" EstimateIO="0.0586806" EstimateCPU="0.0186436" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0773242" TableCardinality="16806" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="AccountId"/><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></OutputList><IndexScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="AccountId"/></DefinedValue><DefinedValue><ColumnReference Database="[fathom]" Schema="[dbo]" Table="[Amount]" Column="PeriodId"/></DefinedValue></DefinedValues><Object Database="[fathom]" Schema="[dbo]" Table="[Amount]" Index="[PK_Amount]" IndexKind="Clustered"/></IndexScan></RelOp></Hash></RelOp></Update></RelOp></Sequence></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Answer 1

在我看来，主要原因是下一条语句的执行计划:

DELETE FROM Period WHERE CompanyId = 105 AND Type = 7 AND DateRangeId IN (
                    SELECT Id 
                    FROM DateRange
                    WHERE Start >= '20110101 00:00:00')

这个 DELETE的执行计划至少有两个问题 SCAN运算符(查看 XML 执行计划的图形 View ，我使用了 SQL Sentry Plan Explorer (link) 的免费版本):


我将创建下一个索引:

CREATE /*UNIQUE*/ INDEX IN_Result_PeriodId_MetricId
ON dbo.Result (PeriodId, MetricId);

CREATE /*UNIQUE*/ INDEX IN_Amount_PeriodId_AccountId
ON dbo.Amount(PeriodId, AccountId);

--Optional
CREATE /*UNIQUE*/ INDEX IN_PeriodAlert_PeriodId_AlertId
ON dbo.PeriodAlert(PeriodId, AlertId);

如果这些索引是唯一的，请取消注释。