ETL 中的聚集索引（刪除 - 插入 - 創建？）
我有一張 1.3 億行的表。每天我從源系統中提取數據並插入到這個表中。提取是增量的，即我只從源獲取新數據。該表是一個具有一個非聚集索引的堆。
所以我正在考慮創建一個聚集索引。我已經讀過在聚集表中載入數據會創建頁面拆分，最好刪除索引，並在插入後重新創建索引。有些人似乎還說最好先禁用然後重建。
我不確定，但我認為上述建議並不可靠。即使當我插入一個堆時，也必須分配新的頁面。是什麼讓聚集索引上的頁面拆分如此糟糕？
我認為也許最好只插入聚集索引？它可能比在堆中插入要慢，但稍後它將受益於連接和計算中的聚集索引。
上述關於刪除和重新創建聚集索引的建議是神話嗎？
我想說總體目標是在新插入之後改進所有內容，即表上的連接和計算，並可能維護表並刪除另一個作業中最舊的行並避免堆碎片。所以我可以忍受插入變慢..
“插入”實際上來自 SSIS 中 ODBC 數據源的“數據流水龍頭”，它們不會大量發生（我認為）。
坦斯克
性能計數器
$$ \SQLServer\Databases(*)\Bulk Copy Rows/sec $$可以幫助確定 SSIS 包是否正在執行批量插入。您需要批量插入，尤其是在基礎表曾經轉換為聚集列儲存的情況下。 在您的 ETL 中，我假設每個 ETL 都涉及 X+Y 行，其中 X 是更新的行數，因此從約 1.3 億行目標表中刪除，Y 是淨新行。對目標表執行刪除，然後插入所有 X+Y 行。
（也有可能這個 ETL 只是在每次迭代中插入 Y 新行，並刪除超過某個截止點的 X 舊行 - 例如刪除超過 30 天的所有行。這使得一些設計/規劃更容易，特別是如果 ~1.3 億行目標表的大小隨著時間的推移保持相當穩定，而不是隨著時間的推移持續增長。）
了解此架構和流程是否要針對 ETL 進行優化、針對非 ETL 工作負載進行優化或在它們之間進行平衡是非常重要的。
對於某些系統和工作負載組合，ETL 是在可接受的時間內完成的。在這種情況下，優化幾乎可以完全集中在非 ETL 工作負載上。格言然後是“寫入數據將如何讀取”和“保持簡單”。無需擔心聚集索引與堆的頁面拆分行為（和事務日誌分支），或者聚集索引插入很可能始終是 ETL 中的序列化點。這一切都變成了：如果有一個鍵（單個或組合）對報告查詢有意義並且具有大量表列，則可以將其視為聚集索引鍵的候選者。
可以根據相同的考慮來管理聚群索引（如果採用）和非聚群索引的碎片。（但對於非 ETL 工作負載，索引維護通常比 ETL 工作負載更需要考慮。）我通常專注於索引重組而不是重建，因為我真正關心的是保持頁面相當滿。我喜歡從這個角度看待它的一個原因是：隨著時間的推移跟踪它的成本要低得多。我可以觀察重組前後平均每百萬行有多少 mb。與使用 sys.dm_db_index_physical_stats 相比，這可以相當便宜地進行測量。重建與重組有兩個固有的優勢：索引重建自動包括全掃描統計更新，
現在，如果 ETL 性能優化優先於其他一切會怎樣？那麼你不希望堆上的非聚集索引，除非它用於通過提供更有效的計劃來加快刪除速度。在 ETL 插入期間維護該非聚集索引的額外成本可能會嚴重拖累 ETL 性能，即使它加快了刪除速度。在這種情況下，請考慮在刪除之前創建非聚集索引。並在刪除後立即刪除。這樣，它就不必使用 REORG 或 REBUILD 進行維護。並且刪除獲得了好處，而插入則不必擔心成本。但這必須衡量才能知道它是否有益。隨著表格增長到 2x、3x 大小等，需要重新訪問測量值。
如果 ETL 性能優先於其他一切，則可能也不希望在該表上使用聚集索引。這是聚集索引中大量插入活動的性能缺陷之一：如果估計超過 100 行，它將在插入之前進行排序。根據插入的大小、最大記憶體授予以及插入查詢收到的授予可能意味著 tempdb 溢出 - 以及插入所用時間的大幅增加。
好的。很有可能，這個系統和工作負載暗示了 ETL 和非 ETL 工作負載所需的性能優化。也許非聚集索引同時用於 ETL 刪除和報告查詢？在這種情況下，請考慮在插入之前刪除它，並在插入之後創建它。
Paul White 的這篇博文是我經常思考的一篇。他在這裡描述了寬泛的按索引計劃和狹窄的按行計劃。
優化更改數據的 T-SQL 查詢 2013 年 1 月 26 日 https://www.sql.kiwi/2013/01/optimizing-t-sql-queries-that-c hange-data.html
也許為刪除和插入保留非聚集索引是滿足您需求的最佳選擇。 也許在刪除之前創建，在刪除之後刪除。 也許在刪除之前刪除，在插入之後創建。這取決於哪些查詢計劃實際使用索引，以及創建索引與為活動保留索引的相對成本。
類似地，聚集索引可能有利於刪除和報告查詢（儘管如果聚集索引對刪除有意義，則非聚集索引不太可能對刪除有意義）。不過，與堆相比，這將是插入的淨成本。
而且…是的，我同意通常創建聚集索引然後刪除聚集索引以管理堆中的碎片/平均填充不是定期執行的操作。我更贊成確保在 delete 語句中根據需要使用 TABLOCK/TABLOCKX 提示，因此可以使用頁鎖，並且可以從堆中釋放由 delete 清空的頁。
如果將創建和刪除聚集索引以將堆的碎片管理為真正需要時偶爾發生的事情，我建議在 NCI(s) 可以執行此操作時執行此操作在創建聚集索引之前刪除，然後在刪除聚集索引後再次創建 NCI。因為在具有 NCI 的堆上創建聚集索引將重建 NCI。刪除聚集索引將再次重建 NCI。預先刪除 NCI(s) 並在最後再次創建 NCI(s) 將 rigamarole 減少了大約一半。
希望不是創建聚集索引只是為了再次刪除它，如果需要對堆進行一次干預，它可以採用 ALTER TABLE…MOVE 的形式。對於該操作，我也建議先刪除 NCI，然後再創建 NCI。我喜歡單獨控制 NCI 重建，而不是因為對底層堆採取的操作而讓它們自動發生。
在評估這些選項時要牢記一個重要變數：相關數據庫的恢復模型。如果事務日誌操作是性能或事務日誌空間的一個問題，並且數據庫處於簡單恢復模式，那麼盡可能使用最少記錄的操作可能是一個很大的收穫。如果數據庫處於完全恢復模式並且 txlog 是一個問題，那麼聚集索引（或在 NCI 中）中的頁面拆分將變得更重要。對於完全恢復模型，sort_in_tempdb 對索引創建或重建操作的好處增加了（除非 tempdb 對實例來說比 txlog 對數據庫來說更痛苦）。