我是否应该避免向高吞吐量的表中添加群集密钥？

Question

我们有一个Server解决方案，它有一个表dsStaging.Audit，它存储由第三方事务数据库创建的审计记录。我们使用这些审核将第三方系统的CRUD操作同步到SQL数据库中。

CREATE TABLE [dsStaging].[Audit](
    [SyncExecutionId] [bigint] NOT NULL,
    [AuditDataGuid] [nvarchar](56) NOT NULL,
    [AuditDate] [datetime] NOT NULL,
    [AuditDateTimeZone] [datetimeoffset](3) NULL,
    [AuditEventGroup] [nvarchar](56) NOT NULL,
    [TransactionId] [bigint] NOT NULL,
    [TransactionSequence] [int] NOT NULL,
    .
    ...
    .
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
    [SyncExecutionId] ASC,
    [TransactionId] ASC,
    [TransactionSequence] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

当审核被处理时，我希望将审计记录移动到一个单独的表Processed.Audit中，以便在x天后删除。

CREATE TABLE [Processed].[Audit](
    [SyncExecutionId] [bigint] NOT NULL,
    [AuditDataGuid] [nvarchar](56) NOT NULL,
    [AuditDate] [datetime] NOT NULL,
    [AuditEventGroup] [nvarchar](56) NOT NULL,
    [TransactionId] [bigint] NOT NULL,
    [TransactionSequence] [int] NOT NULL,
    .
    ...
    .
 CONSTRAINT [PK_Processed_Audit] PRIMARY KEY NONCLUSTERED 
(
    [AuditDate] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

我将审计从阶段转移到处理阶段的主要目标是性能。我需要确保在尽可能短的时间内锁定暂存表，以便能够尽快处理任何未处理的审核(在暂存表中的审计较少=处理速度要快得多)。

我们正在查看大约150万份审计记录，每小时进行一次大约10k的批量审核。

移动审计的过程将大约每20-30秒启动一次。删除Processed.Audit记录的过程将每小时执行一次，并从X天前(通常大约7天)删除1小时的审核。

• 是否应该将Processed.Audit表转换为聚集索引？

支持的最低版本: Server 2012标准版。

Answer 1

在这个场景中，我想要一个聚集索引的主要原因是这一行：

删除process.Audit记录的过程将每小时执行一次，并从x天前(通常大约7天)删除一个小时的审计。

当您从堆中删除行时，数据页可能不会被解除分配，除非delete获得表锁，或者您向delete查询提供了WITH (TABLOCK)提示。不过，您可能可以想象这会对并发性产生什么影响。不太好。

注意，如果使用RCSI或快照隔离，TABLOCK提示将不具有此行为。

下面是一个简单的例子。装上一张小桌子：

USE tempdb;
SET NOCOUNT ON;

CREATE TABLE dbo.heap
(
    id INT PRIMARY KEY NONCLUSTERED,
    junk VARCHAR(1000)
);

INSERT dbo.heap (
    id, junk )
SELECT TOP 1000 x.n, REPLICATE('A', x.n % 1000)
FROM   (
           SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m ) AS x;

运行一个是否正确的检查查询，以确定有多少页分配给堆，以及分配给非集群PK：

SELECT   OBJECT_NAME(i.object_id) AS table_name,
         i.name AS index_name,
         MAX(a.used_pages) AS leaf_me_alone
FROM     sys.indexes AS i
JOIN     sys.partitions AS p
ON p.object_id = i.object_id
   AND p.index_id = i.index_id
JOIN     sys.allocation_units AS a
ON a.container_id = p.partition_id
WHERE OBJECT_NAME(i.object_id) = 'heap'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY OBJECT_NAME(i.object_id), i.index_id;

这方面的结果：

table_name  index_name  leaf_me_alone
heap        NULL        74
heap        PK__heap__  7

所以，堆中有74页，NC中有7页。

做一些单例删除以清除表：

DECLARE @i INT = 1;

WHILE @i < 1000
    BEGIN

        DELETE h
        FROM  dbo.heap AS h
        WHERE h.id = @i;

        SET @i += 1;

        PRINT @i;

    END;

如果您重新运行健全检查查询，您将得到相同的结果。

更糟糕的是，如果现在查询表，SQL将读取所有这些空白页！

SET STATISTICS TIME, IO ON 
SELECT *
FROM   dbo.heap AS h;

桌子‘堆’。扫描计数1，逻辑读取67

所以现在我们的表不仅人为地大了，而且SQL现在在磁盘、内存和备份中以及DBCC和.好吧你明白重点了。

我们每小时都要查看150万份审计记录

嘿嘿嘿！一点也不好玩。

从堆中释放页面的其他选项有：

TRUNCATE TABLE dbo.heap

这不适用于您，因为您需要批处理删除数据。

ALTER TABLE dbo.heap REBUILD;

这对您在这个表大小上将是痛苦的，因为它将同时重建表上的所有非聚集索引。

这张桌子会重复使用页面吗？有时候可能有点。

DECLARE @id_max INT = (SELECT MAX(id) FROM dbo.heap AS h);

INSERT dbo.heap (
    id, junk )
SELECT TOP 5000 x.n + @id_max, REPLICATE('A', x.n % 1000)
FROM   (
           SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
           FROM   sys.messages AS m ) AS x;

健全检查：

table_name  index_name  leaf_me_alone
heap        NULL        400
heap        PK__heap__  20

选择*查询：

桌子‘堆’。扫描计数1，逻辑读取392

希望这能有所帮助！

Answer 2

我会采取不同的做法：

1. 没有任何约束或索引的表dsStaging.Audit。
2. 通过开关分区或sp_rename将记录移动到中间表。
3. 在中间表上创建索引以支持删除过程。
4. 将数据从中间表移动到流程审核表中。(建议将其删除)。
5. 放下中间的桌子。结束了。

我会考虑使用开关分区特性对process.Audit表和中间表进行分区。