透過已排序的叢集資料行存放區索引進行效能微調

  • 發行項

適用於：Azure Synapse Analytics 專用 SQL 集區、SQL Server 2022 (16.x) 和更新版本

當使用者查詢專用 SQL 集區中的資料行存放區資料表時，最佳化工具會檢查每個區段中所儲存的最小值和最大值。 在查詢述詞範圍外的區段不會從磁碟讀取到記憶體。 如果要讀取的區段數目和總計大小很小，查詢可能更快完成。

已排序與未排序的叢集資料行存放區索引

依預設，針對每個建立的資料表 (不含索引選項)，內部元件 (索引產生器) 會在其中建立非排序的叢集資料行存放區索引 (CCI)。 每個資料行中的資料會壓縮成個別的 CCI 資料列群組區段。 每個區段的值範圍都有中繼資料，因此在查詢執行期間，不會從磁碟讀取查詢述詞範圍外的區段。 CCI 提供最高層級的資料壓縮，並減少要讀取的區段大小，讓查詢的執行速度更快。 不過，由於索引產生器不會在將資料壓縮成區段之前排序資料，因此可能會發生具有重疊值範圍的區段，而導致查詢從磁碟讀取更多區段，並需要較長的時間才能完成。

藉由啟用有效率的區段消除來排序叢集資料行存放區索引，藉由略過大量不符合查詢述詞的已排序資料，以加快效能。 建立已排序的 CCI 時，專用 SQL 集區引擎會先依排序索引鍵來排序記憶體中的現有資料，然後索引產生器才會將這些資料壓縮成索引區段。 使用已排序的資料時，會降低區段重疊的情況，讓查詢具有更有效率的區段刪除，從而提高效能，因為從磁碟讀取的區段數目較小。 如果可一次在記憶體中排序所有資料，則可以避免區段重疊。 由於資料倉儲中的大型資料表，這種情況通常不會發生。

若要檢查資料行的區段範圍，請以您的資料表名稱和資料行名稱執行下列命令：

SELECT o.name, pnp.index_id,
cls.row_count, pnp.data_compression_desc,
pnp.pdw_node_id, pnp.distribution_id, 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.pdw_nodes_partitions AS pnp
   JOIN sys.pdw_nodes_tables AS Ntables ON pnp.object_id = NTables.object_id AND pnp.pdw_node_id = NTables.pdw_node_id
   JOIN sys.pdw_table_mappings AS Tmap  ON NTables.name = TMap.physical_name AND substring(TMap.physical_name,40, 10) = pnp.distribution_id
   JOIN sys.objects AS o ON TMap.object_id = o.object_id
   JOIN sys.pdw_nodes_column_store_segments AS cls ON pnp.partition_id = cls.partition_id AND pnp.distribution_id  = cls.distribution_id
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>'  and TMap.physical_name  not like '%HdTable%'
ORDER BY o.name, pnp.distribution_id, cls.min_data_id;

注意

在已排序的 CCI 資料表中，從相同批次 DML 或資料載入作業產生的新資料會在該批次中排序，在資料表中的所有資料之間則沒有全域排序。 使用者可以 REBUILD 已排序的 CCI 來排序資料表中的所有資料。 在專用 SQL 集區中，資料行存放區索引 REBUILD 是離線作業。 如果是資料分割資料表，一次會 REBUILD 一個分割區。 正在重建分割區中的資料為「離線」且無法使用，直到該分割區的 REBUILD 完成為止。

查詢效能

從已排序 CCI 取得的查詢效能取決於查詢模式、資料大小、資料的排序程度、區段的實體結構，以及為查詢執行選擇的 DWU 和資源類別。 在設計已排序的 CCI 資料表時，使用者應該先檢閱這全部的因素，然後再選擇排序資料行。

所有這些模式的查詢通常會以排序的 CCI 更快速執行。

  1. 查詢具有相等、不相等或範圍述詞
  2. 述詞資料行和已排序的 CCI 資料行相同。

在此範例中，資料表 T1 具有叢集資料行存放區索引，並以 Col_C、Col_B 和 Col_A 的順序排序。

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 與已排序 CCI 之間的查詢效能比較。

比較 data_loading 期間效能的長條圖。已排序的叢集資料行存放區索引的持續時間較低。

減少區段重疊

重疊的區段數目取決於要排序的資料大小、可用的記憶體，以及在已排序 CCI 建立期間的最大程度平行處理原則 (MAXDOP) 設定。 下列策略可減少建立已排序 CCI 時的區段重迭。

  • 在索引建立器壓縮資料至區段前，在較高的 DWU 上使用 xlargerc 資源類別，可提供資料排序更多的記憶體。 一旦在索引區段中，就無法變更資料的實體位置。 區段內或跨區段之間沒有資料排序。

  • 使用 OPTION (MAXDOP = 1) 建立排序的 CCI。 用於建立已排序 CCI 的每個執行緒都可在資料子集上運作，並在本機進行排序。 不同執行緒排序的資料之間沒有全域排序。 使用平行執行緒可以減少建立已排序 CCI 的時間，但會產生比使用單一執行緒更多重疊的區段。 使用單一執行緒作業可提供最高的壓縮品質。 例如：

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

注意

目前，在 Azure Synapse Analytics 的專用 SQL 集區中，只有在使用 CREATE TABLE AS SELECT 命令建立已排序的 CCI 資料表時，才支援 MAXDOP 選項。 透過 CREATE INDEX 或 CREATE TABLE 命令建立已排序的 CCI 不支援 MAXDOP 選項。 這項限制不適用於 SQL Server 2022 和更新版本，您可在其中使用 CREATE INDEX 或 CREATE TABLE 命令指定 MAXDOP。

  • 在將資料載入資料表之前，依排序索引鍵預先排序資料。

以下是已排序 CCI 資料表分佈範例，其在上述建議之下為零區段重疊。 已排序的 CCI 資料表是透過使用 MAXDOP 1 和 xlargerc 的 20 GB 堆積資料表，透過 CTAS在 DWU1000c 資料庫中建立。 CCI 是在沒有重複項目的 BIGINT 資料行上排序。

文字資料的螢幕擷取畫面，其中顯示沒有區段重疊。

在大型資料表上建立已排序 CCI

建立已排序 CCI 是一項離線作業。 針對沒有分割區的資料表，使用者必須等到已排序 CCI 建立程式完成之後，才能存取資料。 針對資料分割資料表，因為引擎會依分割區建立已排序 CCI 分割區，所以使用者仍可在未進行已排序 CCI 建立的分割區中存取資料。 您可以使用此選項，將在大型資料表上已排序 CCI 建立期間的停機時間降到最低：

  1. 在目標大型資料表 (稱為 Table_A) 上建立分割區。
  2. 使用與 Table_A 相同的資料表和分割區結構描述，建立空的排序 CCI 資料表 (稱為 Table_B)。
  3. 將一個分割區從 Table_A 切換至 Table_B。
  4. 執行 ALTER INDEX <Ordered_CCI_Index> ON <Table_B> REBUILD PARTITION = <Partition_ID> 並在 Table_B 上重建切換的分割區。
  5. 針對 Table_A 中的每個分割區重複步驟 3 和 4。
  6. 所有分割區從 Table_A 切換並重建至 Table_B 後，請卸除 Table_A 並重新命名 Table_B 為 Table_A。

秘訣

如果是排序 CCI 專用的 SQL 集區資料表，ALTER INDEX REBUILD 會使用 tempdb 重新排序資料。 在重建作業期間監視 tempdb。 如果您需要更多 tempdb 空間，請擴大集區。 當索引重建完成之後，請相應減少回來。

若為具有已排序 CCI 的專用 SQL 集區資料表，ALTER INDEX REORGANIZE 不會重新排序資料。 若要重新排序資料，請使用 ALTER INDEX REBUILD。

如需已排序 CCI 維護的詳細資訊，請參閱最佳化叢集資料行存放區索引。

SQL Server 2022 功能差異

SQL Server 2022 (16.x) 採用的排序叢集資料行存放區索引，類似於 Azure Synapse 專用 SQL 集區的功能。

  • 目前只有 SQL Server 2022 (16.x) 和更新版本支援字串、二進位和 guid 資料類型的叢集資料行存放區增強型區段刪除功能，及規模二以上的 datetimeoffset 資料類型。 先前，此區段刪除適用於數值、日期和時間資料類型，以及小數位數小於或等於 2 的 datetimeoffset 資料類型。
  • 目前只有 SQL Server 2022 (16.x) 和更新版本支援 LIKE 述詞前置詞的叢集資料行存放區資料列群組刪除功能，例如 column LIKE 'string%'。 非前置使用 LIKE 不支援區段刪除，例如 column LIKE '%string'。

如需詳細資訊，請參閱資料行存放區索引的新功能。

範例

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);

後續步驟