我是否應該避免將聚集鍵添加到具有高吞吐量的表中

我們有一個 SQL 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 萬條審核記錄，每批大約 1 萬條。

移動審計的過程大約每 20-30 秒觸發一次。刪除Processed.Audit記錄的過程將每小時執行一次，並從 X 天前（通常大約 7 天）刪除 1 小時的審計。

• 我應該把Processed.Audit表變成聚集索引嗎？

要支持的最低版本：SQL Server 2012 標準版。

在這種情況下我想要一個聚集索引的主要原因是這一行：

刪除 process.Audit 記錄的過程將每小時執行一次，並從 x 天前（通常大約 7 天）刪除一個小時的審計

當您從 HEAP 中刪除行時，數據頁可能不會被釋放，除非刪除獲得表鎖，或者您為WITH (TABLOCK)刪除查詢提供提示。不過，您可能可以想像這對並發有什麼影響。不好。

請注意，TABLOCK如果您使用 RCSI 或 Snapshot Isolation，則提示不會出現此行為。

這是一個簡單的例子。載入一個小表：

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 PK 中有 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 CHECKDB 中都有一堆空白頁，而且……嗯，你明白了。

我們每小時查看大約 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

希望這可以幫助！

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