sql-server – 启用触发器时缓慢删除记录

如果存在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,其中还讨论了对碎片的影响.

（编辑：源码门户网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!