為什麼具有聚集列儲存索引的表會有許多打開的行組？

昨天我在查詢時遇到了一些性能問題，經過進一步調查，我注意到我認為我正試圖深入了解的聚集列儲存索引的奇怪行為。

該表是

CREATE TABLE [dbo].[NetworkVisits](
   [SiteId] [int] NOT NULL,
   [AccountId] [int] NOT NULL,
   [CreationDate] [date] NOT NULL,
   [UserHistoryId] [int] NOT NULL
)

與索引：

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_NetworkVisits] 
  ON [dbo].[NetworkVisits] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]

該表目前有 13 億行，我們不斷地向其中插入新行。當我說不斷時，我的意思是一直。這是一次向表中插入一行的穩定流。

Insert Into NetworkVisits (SiteId, AccountId, CreationDate, UserHistoryId)
Values (@SiteId, @AccountId, @CreationDate, @UserHistoryId)

執行計劃在這裡

我還有一個計劃作業，每 4 小時執行一次，以從表中刪除重複的行。查詢是：

With NetworkVisitsRows
 As (Select SiteId, UserHistoryId, Row_Number() Over (Partition By SiteId, UserHistoryId
                                   Order By CreationDate Asc) RowNum
       From NetworkVisits
      Where CreationDate > GETUTCDATE() - 30)
DELETE
FROM NetworkVisitsRows
WHERE RowNum > 1
Option (MaxDop 48)

執行計劃已粘貼在這裡。

在深入研究這個問題時，我注意到該NetworkVisits表中有大約 2000 個行組，其中大約 800 個處於打開狀態，並且沒有接近允許的最大值 (1048576)。這是我所看到的一個小樣本：

我對索引進行了重組，壓縮了除 1 個行組之外的所有行組，但今天早上我再次檢查，我們再次有多個打開的行組 - 昨天在重組後創建的行組，然後在刪除時大致創建了 3 個其他行組工作執行：

TableName       IndexName           type_desc               state_desc  total_rows  deleted_rows    created_time
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        36754       0               2019-12-18 18:30:54.217
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        172103      0               2019-12-18 20:02:06.547
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        132628      0               2019-12-19 04:03:10.713
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        397718      0               2019-12-19 08:02:13.063

我正在嘗試確定可能導致創建新行組而不是使用現有行組的原因。

插入和刪除之間是否可能存在記憶體壓力或爭用？這種行為是否記錄在任何地方？

我們在此伺服器上執行 SQL Server 2017 CU 16 企業版。

是INSERTMAXDOP 0，DELETE是 MAXDOP 48。唯一關閉的行組是最初的行組BULKLOAD，然後REORG_FORCED是我昨天做的行組，所以修剪的原因分別sys.dm_db_column_store_row_group_physical_stats是REORG和NO_TRIM。除此之外沒有封閉的行組。沒有針對此表執行的更新。我們在插入語句上平均每分鐘執行約 520 次。表上沒有分區。

我知道涓流插入。我們在其他地方做同樣的事情，並且在多個開放行組中沒有遇到同樣的問題。我們懷疑它與刪除有關。每個新創建的行組都在計劃刪除作業的時間附近。只有兩個增量儲存顯示已刪除的行。我們實際上並沒有從這個表中刪除很多數據，例如在昨天的一次執行中，它刪除了 266 行。

為什麼具有聚集列儲存索引的表會有許多打開的行組？

有許多不同的情況會導致這種情況。我將繼續回答一般性問題，以支持解決您的特定情況，我認為這就是您想要的。

插入和刪除之間是否可能存在記憶體壓力或爭用？

這不是記憶體壓力。將單行插入列儲存表時，SQL Server 不會請求記憶體授予。它知道該行將被插入到增量行組中，因此不需要記憶體授予。當每個語句插入超過 102399 行INSERT並達到固定的 25 秒記憶體授予超時時，可能會獲得比預期更多的增量行組。這種記憶體壓力場景是針對批量載入，而不是涓流載入。

DELETE和之間的不兼容鎖INSERT是對您在表中看到的內容的合理解釋。請記住，我不會在生產中進行涓流插入，但目前用於從增量行組中刪除行的鎖定實現似乎需要 UIX 鎖定。您可以通過一個簡單的展示看到這一點：

在第一個會話中將一些行放入增量儲存中：

DROP TABLE IF EXISTS dbo.LAMAK;

CREATE TABLE dbo.LAMAK (
ID INT NOT NULL,
INDEX C CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.LAMAK
SELECT TOP (64000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

在第二個會話中刪除一行，但不要送出更改：

BEGIN TRANSACTION;

DELETE FROM dbo.LAMAK WHERE ID = 1;

DELETEper的鎖sp_whoisactive：

<Lock resource_type="HOBT" request_mode="UIX" request_status="GRANT" request_count="1" />
<Lock resource_type="KEY" request_mode="X" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="OBJECT.INDEX_OPERATION" request_mode="S" request_status="GRANT" request_count="1" />
<Lock resource_type="PAGE" page_type="*" request_mode="IX" request_status="GRANT" request_count="1" />
<Lock resource_type="ROWGROUP" resource_description="ROWGROUP: 5:100000004080000:0" request_mode="UIX" request_status="GRANT" request_count="1" />

在第一個會話中插入一個新行：

INSERT INTO dbo.LAMAK
VALUES (0);

在第二個會話中送出更改並檢查sys.dm_db_column_store_row_group_physical_stats：

創建了一個新的行組，因為插入請求對它更改的行組進行 IX 鎖定。IX 鎖與 UIX 鎖不兼容。這似乎是目前的內部實現，也許微軟會隨著時間的推移對其進行更改。

至於如何修復它，您應該考慮如何使用這些數據。盡可能壓縮數據是否重要？您需要對[CreationDate]列進行良好的行組消除嗎？新數據在幾個小時內不顯示在表格中是否可以？如果重複項從未出現在表中而不是在表中存在長達四個小時，最終使用者會更喜歡嗎？

所有這些問題的答案決定了解決問題的正確途徑。這裡有幾個選項：

1. 每天對列儲存執行一次REORGANIZE帶有該COMPRESS_ALL_ROW_GROUPS = ON選項的選項。平均而言，這意味著該表在增量儲存中不會超過一百萬行。如果您不需要盡可能最好的壓縮，不需要對[CreationDate]列進行最佳行組消除，並且希望保持每四個小時刪除一次重複行的現狀，這是一個不錯的選擇。
2. 分解DELETE成單獨的INSERT和DELETE語句。第一步將要刪除的行插入到臨時表中，然後TABLOCKX在第二個查詢中刪除它們。根據您的數據載入模式（僅插入）和您用於查找和刪除重複項的方法，這不需要在一個事務中。刪除幾百行應該非常快，並且可以很好地消除[CreationDate]列，您最終將通過這種方法獲得。這種方法的優點是[CreationDate]，假設該列的日期是目前日期，您的壓縮行組將具有緊密的範圍。缺點是您的涓流插入可能會被阻止執行幾秒鐘。
3. 將新數據寫入臨時表並每 X 分鐘將其刷新到列儲存中。作為刷新過程的一部分，您可以跳過插入重複項，因此主表將永遠不會包含重複項。另一個優點是您可以控制數據刷新的頻率，以便獲得所需質量的行組。缺點是新數據會延遲出現在[dbo].[NetworkVisits]表中。您可以嘗試組合表的視圖，但是您必須小心，刷新數據的過程將為最終使用者提供一致的數據視圖（您不希望行在過程）。

最後，我不同意應該考慮重新設計表格的其他答案。您平均每秒只在表中插入 9 行，這並不是很高的速率。單個會話每秒可以對具有六列的列儲存表執行 1500 次單例插入。一旦您開始看到周圍的數字，您可能想要更改表格設計。

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