Sql-Server

啟用觸發器時緩慢刪除記錄

  • October 3, 2021

認為這是通過下面的連結解決的 - 解決方法 - 但更新檔沒有。與 Microsoft 支持合作解決。

http://support.microsoft.com/kb/2606883

好的，所以我有一個問題想扔給 StackOverflow 看看是否有人有想法。

請注意，這是使用 SQL Server 2008 R2

問題：啟用觸發器時，從包含 15000 條記錄的表中刪除 3000 條記錄需要 3-4 分鐘，而禁用觸發器時只需 3-5 秒。

表設置

兩張表，我們將稱為 Main 和 Secondary。次要包含我要刪除的項目的記錄，因此當我執行刪除時，我會加入到次要表中。一個程序在刪除語句之前執行，以使用要刪除的記錄填充輔助表。

刪除語句：

DELETE FROM MAIN 
WHERE ID IN (
  SELECT Secondary.ValueInt1 
  FROM Secondary 
  WHERE SECONDARY.GUID = '9FFD2C8DD3864EA7B78DA22B2ED572D7'
);

該表有很多列和大約 14 個不同的 NC 索引。在確定觸發器是問題之前，我嘗試了很多不同的方法。

  • 開啟頁面鎖定（我們已經預設關閉）
  • 手動收集統計資訊
  • 禁用自動收集統計資訊
  • 已驗證的索引執行狀況和碎片
  • 從表中刪除聚集索引
  • 檢查執行計劃（沒有顯示缺失索引，實際刪除的成本為 70%，記錄的連接/合併成本約為 28%

觸發器

該表有 3 個觸發器（插入、更新和刪除操作各一個）。我將刪除觸發器的程式碼修改為只返回，然後選擇一個以查看它被觸發了多少次。它在整個操作過程中只觸發一次（如預期的那樣）。

ALTER TRIGGER [dbo].[TR_MAIN_RD] ON [dbo].[MAIN]
           AFTER DELETE
           AS  
               SELECT 1
               RETURN

回顧一下

  • 啟用觸發器 - 語句需要 3-4 分鐘才能完成
  • 關閉觸發器 - 語句需要 3-5 秒才能完成

有人對為什麼有任何想法嗎？

另請注意 - 不希望更改此架構、添加刪除索引等作為解決方案。該表是一些主要數據操作的中心部分，我們必須對其進行調整和調整（索引、頁面鎖定等），以允許主要並發操作在沒有死鎖的情況下工作。

這是執行計劃 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.1" Build="10.50.1790.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
 <BatchSequence>
   <Batch>
     <Statements>
       <StmtSimple StatementCompId="1" StatementEstRows="185.624" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.42706" StatementText="DELETE FROM MAIN WHERE ID IN (SELECT Secondary.ValueInt1 FROM Secondary WHERE Secondary.SetTMGUID = '9DDD2C8DD3864EA7B78DA22B2ED572D7')" StatementType="DELETE" QueryHash="0xAEA68D887C4092A1" QueryPlanHash="0x78164F2EEF16B857">
         <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
         <QueryPlan CachedPlanSize="48" CompileTime="20" CompileCPU="20" CompileMemory="520">
           <RelOp AvgRowSize="9" EstimateCPU="0.00259874" EstimateIO="0.296614" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Delete" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="0.42706">
             <OutputList />
             <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_02]" IndexKind="Clustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[PK_MAIN_ID]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[UK_MAIN_01]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_03]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_04]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_05]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_06]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_07]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_08]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_09]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_10]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_11]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[UK_MAIN_12]" IndexKind="NonClustered" />
               <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_13]" IndexKind="NonClustered" />
               <RelOp AvgRowSize="15" EstimateCPU="1.85624E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.127848">
                 <OutputList>
                   <ColumnReference Column="Uniq1002" />
                   <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                 </OutputList>
                 <Top RowCount="true" IsPercent="false" WithTies="false">
                   <TopExpression>
                     <ScalarOperator ScalarString="(0)">
                       <Const ConstValue="(0)" />
                     </ScalarOperator>
                   </TopExpression>
                   <RelOp AvgRowSize="15" EstimateCPU="0.0458347" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.12783">
                     <OutputList>
                       <ColumnReference Column="Uniq1002" />
                       <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                     </OutputList>
                     <Merge ManyToMany="false">
                       <InnerSideJoinColumns>
                         <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                       </InnerSideJoinColumns>
                       <OuterSideJoinColumns>
                         <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                       </OuterSideJoinColumns>
                       <Residual>
                         <ScalarOperator ScalarString="[MyDatabase].[dbo].[MAIN].[ID]=[MyDatabase].[dbo].[Secondary].[ValueInt1]">
                           <Compare CompareOp="EQ">
                             <ScalarOperator>
                               <Identifier>
                                 <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                               </Identifier>
                             </ScalarOperator>
                             <ScalarOperator>
                               <Identifier>
                                 <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                               </Identifier>
                             </ScalarOperator>
                           </Compare>
                         </ScalarOperator>
                       </Residual>
                       <RelOp AvgRowSize="19" EstimateCPU="0.0174567" EstimateIO="0.0305324" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15727" LogicalOp="Index Scan" NodeId="4" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0479891" TableCardinality="15727">
                         <OutputList>
                           <ColumnReference Column="Uniq1002" />
                           <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                           <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                         </OutputList>
                         <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                           <DefinedValues>
                             <DefinedValue>
                               <ColumnReference Column="Uniq1002" />
                             </DefinedValue>
                             <DefinedValue>
                               <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                             </DefinedValue>
                             <DefinedValue>
                               <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                             </DefinedValue>
                           </DefinedValues>
                           <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[PK_MAIN_ID]" IndexKind="NonClustered" />
                         </IndexScan>
                       </RelOp>
                       <RelOp AvgRowSize="11" EstimateCPU="0.00392288" EstimateIO="0.03008" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3423.53" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0340029" TableCardinality="171775">
                         <OutputList>
                           <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                         </OutputList>
                         <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                           <DefinedValues>
                             <DefinedValue>
                               <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                             </DefinedValue>
                           </DefinedValues>
                           <Object Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Index="[IX_Secondary_01]" IndexKind="NonClustered" />
                           <SeekPredicates>
                             <SeekPredicateNew>
                               <SeekKeys>
                                 <Prefix ScanType="EQ">
                                   <RangeColumns>
                                     <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="SetTMGUID" />
                                   </RangeColumns>
                                   <RangeExpressions>
                                     <ScalarOperator ScalarString="'9DDD2C8DD3864EA7B78DA22B2ED572D7'">
                                       <Const ConstValue="'9DDD2C8DD3864EA7B78DA22B2ED572D7'" />
                                     </ScalarOperator>
                                   </RangeExpressions>
                                 </Prefix>
                               </SeekKeys>
                             </SeekPredicateNew>
                           </SeekPredicates>
                         </IndexScan>
                       </RelOp>
                     </Merge>
                   </RelOp>
                 </Top>
               </RelOp>
             </Update>
           </RelOp>
         </QueryPlan>
       </StmtSimple>
     </Statements>
   </Batch>
 </BatchSequence>
</ShowPlanXML>

好吧，這是微軟的官方回應……我認為這是一個主要的設計缺陷。

2011 年 11 月 14 日 - 官方回應已更改。如前所述，他們沒有使用事務日誌。正在使用內部儲存（行級別）將更改的數據複製到其中。他們仍然無法確定為什麼花了這麼長時間。

我們決定使用 Instead Of 觸發器來代替 after delete 觸發器。

觸發器的 AFTER 部分導致我們必須在刪除完成後通讀事務日誌並建構觸發器插入/刪除表。這是我們花費大量時間的地方，並且是為觸發器的 AFTER 部分設計的。INSTEAD OF 觸發器將阻止這種掃描事務日誌和建構插入/刪除表的行為。此外，據觀察，如果我們使用 nvarchar(max) 刪除所有列，事情會變得更快，這是有道理的，因為它被視為 LOB 數據。請查看以下文章以獲取有關行內數據的更多資訊：

http://msdn.microsoft.com/en-us/library/ms189087.aspx

摘要：AFTER 觸發器需要在刪除完成後掃描事務日誌，然後我們必須建構和插入/刪除表，這需要更多使用事務日誌和時間。

因此，作為一項行動計劃，這就是我們目前的建議：

A) Limit the number of rows deleted in each transaction or
B) Increase timeout settings or
C) Don't use AFTER trigger or trigger at all or
D) Limit usage of nvarchar(max) datatypes.

SQL Server 2005 中引入的行版本控制框架用於支持許多功能，包括新的事務隔離級別READ_COMMITTED_SNAPSHOT和SNAPSHOT. 即使沒有啟用這些隔離級別，行版本控制仍用於AFTER觸發器（以促進生成表inserted和deleted偽表）、MARS 和（在單獨的版本儲存中）線上索引。

如文件所述，引擎可以為表的每一行添加一個 14 字節的後綴，以用於任何這些目的。這種行為是相對眾所周知的，將 14 字節數據添加到啟用了行版本控制隔離級別的線上重建的索引的每一行中也是如此。即使沒有啟用隔離級別，只有在重新建構時才會向非聚集索引ONLINE添加一個額外的字節。

如果存在 AFTER 觸發器，並且版本控制會為每行添加 14 個字節，則引擎中存在優化以避免這種情況，但不會發生ROW_OVERFLOWor分配。LOB實際上，這意味著行的最大可能大小必須小於 8060 字節。在計算最大可能行大小時，引擎假設例如 VARCHAR(460) 列可以包含 460 個字元。

使用觸發器最容易看到該行為AFTER UPDATE，儘管相同的原則適用於AFTER DELETE. 以下腳本創建一個最大行內長度為 8060 字節的表。數據適合單個頁面，該頁面上有 13 個字節的可用空間。存在無操作觸發器，因此頁面被拆分並添加了版本資訊：

USE Sandpit;
GO
CREATE TABLE dbo.Example
(
   ID          integer NOT NULL IDENTITY(1,1),
   Value       integer NOT NULL,
   Padding1    char(42) NULL,
   Padding2    varchar(8000) NULL,
   
   CONSTRAINT PK_Example_ID
   PRIMARY KEY CLUSTERED (ID)
);
GO
WITH
   N1 AS (SELECT 1 AS n UNION ALL SELECT 1),
   N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
   N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
   N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R)
INSERT TOP (137) dbo.Example
   (Value)
SELECT
   ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM N4;
GO
ALTER INDEX PK_Example_ID 
ON dbo.Example 
REBUILD WITH (FILLFACTOR = 100);
GO
SELECT
   ddips.index_type_desc,
   ddips.alloc_unit_type_desc,
   ddips.index_level,
   ddips.page_count,
   ddips.record_count,
   ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
   ddips.index_level = 0;
GO
CREATE TRIGGER ExampleTrigger
ON dbo.Example
AFTER DELETE, UPDATE
AS RETURN;
GO
UPDATE dbo.Example
SET Value = -Value
WHERE ID = 1;
GO
SELECT
   ddips.index_type_desc,
   ddips.alloc_unit_type_desc,
   ddips.index_level,
   ddips.page_count,
   ddips.record_count,
   ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
   ddips.index_level = 0;
GO
DROP TABLE dbo.Example;

該腳本產生如下所示的輸出。單頁表分為兩頁，最大物理行長度從 57 字節增加到 71 字節（= 行版本資訊的 +14 字節）。

更新範例

DBCC PAGE顯示單個更新的行有Record Attributes = NULL_BITMAP VERSIONING_INFO Record Size = 71，而表中的所有其他行都有Record Attributes = NULL_BITMAP; record Size = 57。

UPDATE用單行替換的相同腳本DELETE會產生如下所示的輸出：

DELETE dbo.Example
WHERE ID = 1;

刪除範例

總共少了一行（當然！），但最大物理行大小沒有增加。行版本資訊僅添加到觸發器偽表所需的行中，並且該行最終被刪除。但是，頁面拆分仍然存在。此頁面拆分活動是導致存在觸發器時觀察到的緩慢性能的原因。如果Padding2列的定義從 更改varchar(8000)為varchar(7999)，則頁面不再拆分。

另請參閱 SQL Server MVP Dmitri Korotkevitch 的這篇部落格文章，其中還討論了對碎片的影響。

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

相關問答

Sql-Server

使用許多外鍵調整級聯刪除的性能

  • November 22, 2019
檢視問答
Sql-Server

有效刪除表中 90% 的數據

  • January 15, 2018
檢視問答
Sql-Server

軟刪除以提高性能：都錯了嗎？

  • December 30, 2015
檢視問答
Sql-Server

觸發分區表和性能

  • July 7, 2022
檢視問答
Sql-Server

執行 DELETE 儲存過程，但發現 WHILE 循環中的性能隨著迭代次數的增加而下降

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

為什麼添加 TOP 1 會顯著降低性能？

  • December 29, 2021
檢視問答