使用已排序的叢集數據行存放區索引進行效能微調
適用於：
Microsoft Fabric 中的 SQL Server 2022 （16.x） Azure SQL 資料庫 Azure SQL 受控執行個體 SQL 資料庫
藉由啟用有效率的區段消除，已排序的叢集數據行存放區索引 （CCI） 藉由略過大量不符合查詢述詞的已排序數據來提供更快的效能。 由於資料排序作業，將資料載入已排序 CCI 資料表可能會較載入非排序 CCI 資料表花費更長的時間，但之後查詢可利用已排序 CCI 使執行速度更快。
當使用者查詢數據行存放區數據表時，優化工具會檢查儲存在每個區段中的最小值和最大值。 查詢述詞界限外的區段不會從磁碟讀取到記憶體。 如果要讀取的區段數目及其大小總計很小，查詢可以更快完成。
如需已排序的數據行存放區索引可用性，請參閱 已排序的數據行索引可用性。
已排序與非已排序的叢集數據行存放區索引
根據預設，針對未建立索引選項的每個數據表，內部元件（索引產生器）會在其中建立非排序的叢集數據行存放區索引 （CCI）。 每個數據行中的數據都會壓縮成個別的CCI資料列群組區段。 每個區段的值範圍都有元數據，因此查詢執行期間不會從磁碟讀取超出查詢述詞界限的區段。 CCI 提供最高層級的數據壓縮，並減少要讀取的區段大小，以便查詢執行得更快。 不過，由於索引產生器在將數據壓縮成區段之前不會排序數據，因此可能會發生重疊值範圍的區段，導致查詢從磁碟讀取更多區段，而且需要較長的時間才能完成。
建立已排序的 CCI 時，SQL 資料庫 引擎會在索引產生器將現有數據壓縮成索引區段之前，依順序索引鍵排序記憶體中的現有數據。 透過已排序的數據，區段重迭會減少，讓查詢有更有效率的區段消除，因此效能較快，因為要從磁碟讀取的區段數目較小。 如果所有數據可以一次在記憶體中排序，則可以避免區段重疊。 由於數據倉儲中的大型數據表，此案例不會經常發生。
若要檢查資料列的區段範圍，請使用資料表名稱和資料行名稱執行下列命令：
SELECT
o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
cls.segment_id,
cls.column_id,
cls.min_data_id, cls.max_data_id,
cls.max_data_id-cls.min_data_id as difference
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns as cols ON o.object_id = cols.object_id AND cls.column_id = cols.column_id
WHERE o.name = '<Table Name>' and cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;
注意
在已排序的 CCI 數據表中，來自相同批次 DML 或數據載入作業的新數據會在該批次內排序，數據表中的所有數據都沒有任何全域排序。 用戶可以 REBUILD 已排序的 CCI 來排序數據表中的所有數據。 針對數據分割數據表，REBUILD 會一次完成一個數據分割。 正在重建的數據分割中的數據「離線」，且在重建完成該分割區之前無法使用。
查詢效能
查詢從已排序的CCI獲得效能取決於查詢模式、數據大小、數據排序方式、區段的實體結構，以及為查詢執行選擇的 DWU 和資源類別。 在設計已排序的CCI數據表時，用戶應該先檢閱所有這些因素，再選擇排序數據行。
所有這些模式的查詢通常會以已排序的CCI更快執行。
- 查詢具有相等、不相等或範圍述詞
- 述詞數據行和已排序的CCI資料行相同。
在此範例中，數據表T1具有以、 Col_B和 Col_A序列Col_C排序的叢集數據行存放區索引。
CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);
查詢 1 和查詢 2 的效能可受益於其他查詢以外的已排序 CCI，因為它們參考所有已排序的 CCI 資料行。
-- Query #1:
SELECT * FROM T1 WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';
-- Query #2
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';
-- Query #3
SELECT * FROM T1 WHERE Col_B = 'b' AND Col_A = 'a';
-- Query #4
SELECT * FROM T1 WHERE Col_A = 'a' AND Col_C = 'c';
數據載入效能
載入已排序 CCI 資料表的數據效能類似於資料分割數據表。 由於資料排序作業，將資料載入已排序 CCI 資料表可能會較載入非排序 CCI 資料表花費更長的時間，但之後查詢可利用已排序 CCI 使執行速度更快。
減少區段重迭
重迭區段的數目取決於排序的數據大小、可用的記憶體，以及在排序 CCI 建立期間平行處理原則的最大程度 （MAXDOP） 設定。 下列策略可減少建立已排序 CCI 時的區段重疊。
- 使用
OPTION (MAXDOP = 1)建立已排序的CCI。 用於排序 CCI 建立的每個線程都會在數據子集上運作，並將其排序在本機。 不會跨不同線程排序的數據進行全域排序。 使用平行線程可以縮短建立已排序 CCI 的時間，但會產生比使用單個線程更多的重疊區段。 使用單個線程作業可提供最高的壓縮品質。 您可以使用 或CREATE TABLE命令來指定 MAXDOPCREATE INDEX。 例如：
CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);
- 在將數據載入數據表之前，先依照排序索引鍵預先排序數據。
以下是已排序的CCI數據表散發範例，其中零個區段重疊於上述建議。 已排序的 CCI 會在沒有重複專案的 bigint 資料行上排序。
在大型數據表上建立已排序的CCI
建立已排序的 CCI 是離線作業。 對於沒有數據分割的數據表，在排序的CCI建立程式完成之前，使用者將無法存取數據。 若為數據分割數據表，因為引擎會依分割區建立已排序的CCI分割區，使用者仍然可以存取未處理已排序CCI建立之數據分割中的數據。 您可以使用此選項，將大型資料表上已排序 CCI 建立期間的停機時間降到最低：
- 在目標大型數據表上建立分割區（稱為
Table_A）。 - 使用與 相同的數據表和數據分割架構
Table_A，建立空的已排序 CCI 數據表（稱為Table_B）。 - 將分割區從
Table_A切換為Table_B。 - 執行
ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID>以在 上Table_B重建切換的數據分割。 - 針對中的每個
Table_A分割區重複步驟 3 和 4。 - 將所有分割區從
Table_A切換為Table_B，且已重建、卸Table_A除 ，並將 重新命名Table_B為Table_A。
SQL Server 2022 功能
SQL Server 2022 （16.x） 引進了已排序的叢集數據行存放區索引，類似於 Azure Synapse 專用 SQL 集區中的功能。
- SQL Server 2022 （16.x） 和更新版本和其他 SQL 平台支援字串、二進位和 guid 數據類型的叢集數據行存放區增強 區段消除 功能，以及 大於兩個的 datetimeoffset 數據類型。 先前，此區段消除適用於數值、日期和時間數據類型，以及 小於或等於兩個小數字數的 datetimeoffset 數據類型。
- 目前，只有 SQL Server 2022 （16.x） 和更新版本和其他 SQL 平台支援叢集數據行存放區資料列群組消除前置詞，
LIKE例如column LIKE 'string%'。 非前置詞使用 LIKE 不支援區段消除，例如column LIKE '%string'。
如需已排序的數據行存放區索引可用性，請參閱 已排序的數據行索引可用性。
如需詳細資訊，請參閱 數據行存放區索引的新功能。
如需 Azure Synapse Analytics 中專用 SQL 集區中已排序數據行存放區索引的詳細資訊，請參閱 使用已排序的叢集數據行存放區索引進行效能微調。
範例
A. 若要檢查已排序的資料列和順序序數：
SELECT object_name(c.object_id) table_name, c.name column_name, i.column_store_order_ordinal
FROM sys.index_columns i
JOIN sys.columns c ON i.object_id = c.object_id AND c.column_id = i.column_id
WHERE column_store_order_ordinal <>0;
B. 若要變更數據行序數，請新增或移除訂單清單中的數據行，或從CCI變更為已排序的CCI：
CREATE CLUSTERED COLUMNSTORE INDEX InternetSales ON dbo.InternetSales
ORDER (ProductKey, SalesAmount)
WITH (DROP_EXISTING = ON);