认为这是通过以下链接解决的 – 工作周围的工作 – 但补丁没有.使用Microsoft支持解决.
http://support.microsoft.com/kb/2606883
好的,我有一个问题,我想抛弃到StackOverflow,看看是否有人有想法.
请注意,这是sql Server 2008 R2
问题：在启用触发器时,从具有15000条记录的表中删除3000条记录需要3-4分钟,而在禁用触发条件时仅需3-5秒.
表格设置
我们称之为Main和Secondary的两个表.辅助包含我要删除的项目的记录,因此当我执行删除时,我加入辅助表.进程在delete语句之前运行,以使用要删除的记录填充辅助表.
删除声明：
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分钟才能完成
>使用Trigger off – 语句需要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>
解决方法
sql Server 2005中引入的行版本控制框架用于支持许多功能,包括新的事务隔离级别READ_COMMITTED_SNAPSHOT和SNAPSHOT.即使没有启用这些隔离级别,行版本控制仍然用于AFTER触发器(以便于生成插入和删除的伪表),MARS和(在单独的版本存储中)在线索引.
作为documented,引擎可以向表的每一行添加14字节的后缀,该表的版本用于任何这些目的.此行为相对众所周知,因为将14字节数据添加到索引的每一行(rebuilt online,并启用了行版本控制隔离级别).即使未启用隔离级别,在重建ONLINE时也会向non-clustered indexes only添加一个额外字节.
如果存在AFTER触发器,并且版本控制每行将增加14个字节,则引擎内存在优化以避免这种情况,但是不能发生ROW_OVERFLOW或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,'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,'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;记录大小= 57.
将UPDATE替换为单行DELETE的相同脚本生成显示的输出：
DELETE dbo.Example WHERE ID = 1;
总共少了一行(当然！),但最大物理行大小没有增加.行版本控制信息仅添加到触发器伪表所需的行中,并且该行最终被删除.但是,页面拆分仍然存在.此页面拆分活动负责在触发器出现时观察到的缓慢性能.如果Padding2列的定义从varchar(8000)更改为varchar(7999),则页面不再分割.
另请参阅sql Server MVP Dmitri Korotkevitch的blog post,其中还讨论了对碎片的影响.
