非聚集索引的創建時間比聚集索引長得多

我有一個在表上創建聚集索引和非聚集索引以進行分區交換的過程。我遇到的問題是創建聚集索引大約需要 2 分鐘：

CREATE UNIQUE CLUSTERED INDEX UCX_Idx ON myTable (
   IdCol1 ASC
   ,Col2 ASC
   ,IdCol4 ASC
   ,IdCol3 ASC
   ,Col1 ASC
   ,Col6 ASC
   ) ON PS_1 (IdCol1) END

但是非聚集索引的創建大約需要 1.5 小時：

CREATE NONCLUSTERED INDEX IX_Idx1 ON myTable (
   IdCol1 ASC
   ,IdCol2 ASC
   ,IdCol3 ASC
   ,IdCol4 ASC
   ,Col1 ASC
   ) INCLUDE (
   Col2
   ,DateCol1
   ,Col2
   ,Col3
   ,Col4
   ,Col5
   ,Col6
   )
   WITH (SORT_IN_TEMPDB = ON) ON PS_1(IdCol1) END

我在 SQL Server 2014 中沒有看到這種行為，但我在 SQL Server 2016 中看到了。相同數量的 RAM 和 CPU。我已經在沒有 SORT_IN_TEMPDB = ON 的情況下嘗試過，但它也有類似的問題。實際上，我已經在我的環境中的不同地方看到了這一點，並且都安裝了 SQL Server 2016（標準版）。

開放式問題

• 表的 DD 是什麼樣的？
• 列的順序是什麼？

聚集索引創建

當您創建聚集索引時，實際上是按照您正在創建的聚集索引的順序對錶中的數據進行排序。

它們不是表格的~副本~，它們是表格，按您選擇作為鍵的列排序。它可能是一個。可能是幾個。它可能是一個 GUID！但那是另一次了。從現在開始很久了。當我集結軍隊時，按照古老的預言。

參考： 非聚集索引中的聚集索引鍵列(Brentozar.com)

非聚集索引創建

聚群索引的列順序與非聚群索引中的列順序完全不同，直接影響索引的創建。數據庫引擎必須多次搜尋您的聚集索引，直到它以正確的順序將所有資訊組合在一起以創建非聚集索引。這就是為什麼創建非聚集索引需要這麼長時間的原因。

然後是聚集索引始終是非聚集索引的一部分的成本。

SQL Server 2014 和 SQL Server 2016 之間的差異

至於各個版本在索引創建上的差異：您真的將蘋果與蘋果進行比較，而不是與梨進行比較嗎？或者換一種說法：SQL Server 2014 伺服器是否執行在具有相同 CPU 核心頻率、相同數量的 RAM 和相同 MAX_DOP 設置的相同硬體上？

使用標準版或企業版時，索引創建存在一些限制/限制…

企業版與標準版

並非每個 SQL Server 版本都提供並行索引操作。有關詳細資訊，請參閱 SQL Server 2016 版本支持的功能

$$ … $$

• 並行索引執行和 MAXDOP 索引選項適用於以下 Transact-SQL 語句：

+ 創建索引 + 改變索引重建 + DROP INDEX（這僅適用於聚集索引。） + ALTER TABLE ADD（索引）約束 + ALTER TABLE DROP（聚集索引）約束

參考： 配置並行索引操作(Microsoft Docs)

如果您隨後快速瀏覽了 SQL Server 2014 和 2016 的功能，那麼這兩個版本都限制了標準版的 MAXDOP：

SQL Server 2014 - SQL Server 2016 的版本和支持的功能 - RDBMS 可管理性

                            Enterprise    Standard
Parallel indexed operations     Yes          -

參考： SQL Server 2014 版本支持的功能(Microsoft Docs)

SQL Server 2016 - SQL Server 2016 的版本和支持的功能 - RDBMS 可管理性

                            Enterprise    Standard
Parallel indexed operations     Yes          No

參考： SQL Server 2014 版本支持的功能(Microsoft Docs)

此選項對索引創建有直接影響，這可能是您看到索引創建時間差異很大的原因。

想法

您可能需要考慮重新設計索引。我建議閱讀以下一些文章：

• SQL Server 索引體系結構和設計指南(Microsoft Docs)
• 聚集索引鍵敢於使用的地方(Brenozar.com)
• 非聚集索引何時需要 b 樹中的“查找”鍵？(SQLSkills.com)
• SQLskills SQL101：索引基礎知識(SQLSkills.com)

附加閱讀

• 配置並行索引操作(Microsoft Docs)
• 什麼時候應該將主鍵聲明為非聚集的？(DBA.SE)

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