主页 / dba / 问题 / 314094
Accepted
Roman Pekar
Asked: 2022-07-06 09:20:13 +0800 CST

触发分区表和性能

  • 772

我不确定我发现的是否是一个错误，但它确实看起来像。我找不到太多关于它的信息，所以我决定把它放在这里。

因此，简而言之，在分区表上定义的触发器中访问内部表（inserted和）时，我面临着糟糕的性能。deleted

为了测试这个问题，我创建了一些简单的表，完全相同，但一个是分区的，另一个不是：

create table [dbo].[Test1](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc) on ps_part(part_id)
);

create table [dbo].[Test2](
    [part_id] [int] not null,
    [id] [int] not null,
    [cost] [float] null,
    constraint [pk__Test1] primary key clustered ([part_id] asc, [id] asc)
);

然后我用一些数据填充了表格。我现在没有数据生成脚本，我只是使用了一些本地数据，但是这些表中有大约 473 个不同的分区和大约 383M 行。

然后我刚刚测试了这些表的更新速度有多快，使用非常简单的查询，例如

update dbo.Test1 set cost = cost + 0.1 where part_id = ??;
update dbo.Test1 set cost = cost - 0.1 where part_id = ??;

update dbo.Test2 set cost = cost + 0.1 where part_id = ??;
update dbo.Test2 set cost = cost - 0.1 where part_id = ??;

结果是分区表的逻辑平均更新时间约为2 秒，非分区表的平均更新时间约为4 秒。

然后我在两个表上创建了简单的触发器

alter trigger [dbo].[Test1__changed] on [dbo].[Test1]
after insert,update,delete
as 
begin
    set nocount on;

    select a.part_id
    into #temp11111111
    from (
        select r.part_id from inserted as r
        union
        select r.part_id from deleted as r
    ) as a;
end

之后我尝试了相同的测试查询，结果非常奇怪 - 在分区表上，查询平均需要3 分钟才能完成，而在非分区表上，时间与没有触发器的情况相似 -大约4 秒。

你知道为什么会发生这种情况吗？有什么办法可以解决这个问题？

sql-server performance

2 个回答

  1. Best Answer

    此问题记录在Alex发现的KB 2606883中的 SQL Server 2012 中。

    我在 SQL Server 2019 中重现了该问题，并验证设置跟踪标志 2470 解决了它。

    我不清楚修复的确切性质是什么，以及为什么这不是更高版本中产品的默认行为。

    对于我的示例数据（3000 万行均匀分布在 700 个分区和一个空分区中），这使插入扫描和删除扫描所需的时间从每个 > 4 秒减少到每个 0.021 秒。

    开启和关闭 TF 的执行计划（包括运行时统计信息）在此处。在这两个计划中，删除/插入的扫描仍然说他们访问所有 701 分区。

    • 8

  2. 为了补充马丁关于修复性质的回答：

    如果没有修复，为支持触发器而生成的行版本将通过与事务关联的单个存储引擎行集句柄进行访问。

    插入和删除的伪表的扫描被标记为分区。每个分区都是通过单个行集句柄扫描版本列表来处理的。在示例中，版本列表被扫描了 700 次。

    当导致触发器触发的语句仅影响几个分区（或示例中的单个分区）时，这会导致大量不必要的版本扫描。在示例中，除了一个版本扫描之外，所有版本扫描都找不到匹配的记录。

    触发器本身是一个单独的执行。它对受触发语句影响的分区集一无所知。

    TF 2470 下的修复为每个分区创建一个存储引擎行集句柄，但仅当底层分区对象具有 100 个或更多分区时。句柄数按比例放大到最接近的 2 次幂（例如，为 100 个分区提供 128 个句柄）。

    触发器内的插入和删除扫描仍然访问所有分区。每个分区的行集句柄通过仅访问与特定分区关联的版本而不是每次访问整个集来提高效率。

    此更改发生在sqlmin!RowsetVersionScan::Init存储引擎内部非常低的级别 ( )，并且通过任何 DMV 均不可见。鉴于触发器可以与其他引擎功能交互的所有方式，这是一个相当冒险的更改，因此它受到跟踪标志的保护，而不是默认情况下。受此问题影响的用户可以启用该标志，并在必要时快速恢复。

    • 5

相关问题