為記憶體最佳化資料表的雜湊索引進行疑難排解

發行項
2025-03-05

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

必要條件

了解這篇文章的重要內容資訊位於︰

實際數字

當為經記憶體最佳化的資料表建立雜湊索引時，值區數目必須在建立時指定。在大部分情況下，值區計數理想情況會介於索引鍵中相異值數目的 1 到 2 倍之間。

不過，即使 BUCKET_COUNT 略低於或高於偏好的範圍，雜湊索引的效能很可能仍然是可容忍或可接受的。請考慮至少為雜湊索引提供 BUCKET_COUNT，大約等於您預測經記憶體最佳化的資料表成長後將擁有的資料列數目。
假設成長中的資料表擁有 2,000,000 個資料列，但預測將成長 10 倍為 20,000,000 個資料列。請從表格中的列數的 10 倍開始設定桶數。這讓您有空間可容納增加的資料列數。

理想情況下，當資料列數量達到初始資料分區數時，您應該增加資料分區數。
在大多數情況下，即使行的數量增長至桶數量的五倍，效能也仍然良好。

假設雜湊索引有 10,000,000 個相異索引鍵值。

桶數計數 2,000,000 大約是您可以接受的最低限度。效能降低的程度是在容忍範圍內。

索引中有太多重複的值？

如果雜湊索引值有高比率的重複項目，則雜湊值區會受到較長鏈結影響。

假設您有來自較早 T-SQL 語法程式碼區塊的相同 SupportEvent 資料表。下列 T-SQL 程式碼示範如何尋找並顯示「所有」值與「唯一」值的比率︰

-- Calculate ratio of:  Rows / Unique_Values.  
DECLARE @allValues float(8) = 0.0, @uniqueVals float(8) = 0.0;  
  
SELECT @allValues = Count(*) FROM SupportEvent;  
  
SELECT @uniqueVals = Count(*) FROM  
  (SELECT DISTINCT SupportEngineerName  
      FROM SupportEvent) as d;  
  
    -- If (All / Unique) >= 10.0, use a nonclustered index, not a hash.   
SELECT Cast((@allValues / @uniqueVals) as float) as [All_divby_Unique];  
go

10.0 或更高的比率表示哈希是一種不好的索引類型。請考慮改用非叢集索引。

疑難排解雜湊索引值區計數

本節將討論如何進行雜湊索引值區計數的疑難排解。

監視連鎖結構和空桶的統計資料

您可以執行下列 T-SQL SELECT，來監視雜湊索引的統計健康狀況。 SELECT 會使用名為 sys.dm_db_xtp_hash_index_stats 的資料管理檢視 (DMV)。

SELECT  
  QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(h.object_id)) as [table],   
  i.name                   as [index],   
  h.total_bucket_count,  
  h.empty_bucket_count,  
    
  FLOOR((  
    CAST(h.empty_bucket_count as float) /  
      h.total_bucket_count) * 100)  
                            as [empty_bucket_percent],  
  h.avg_chain_length,   
  h.max_chain_length  
FROM  
        sys.dm_db_xtp_hash_index_stats  as h   
  JOIN sys.indexes                     as i  
          ON h.object_id = i.object_id  
          AND h.index_id  = i.index_id  
JOIN sys.memory_optimized_tables_internal_attributes ia ON h.xtp_object_id=ia.xtp_object_id
JOIN sys.tables t on h.object_id=t.object_id
WHERE ia.type=1
ORDER BY [table], [index];

將 SELECT 結果與下列統計指導方針進行比較：

空桶
- 33% 是適當的目標值，但較大的百分比 (甚至是 90%) 通常沒什麼問題。
- 當值區計數等於相異索引鍵值的數目時，大約有 33% 的值區是空的。
- 低於 10% 的值就太低。
桶內的鏈結
- 如果沒有任何重複的索引鍵值，則平均鏈結長度 1 會很理想。通常可接受的鏈結長度上限為 10。
- 如果平均鏈結長度大於 10，且空桶的百分比大於 10%，這表明資料有很多重複項目，可能導致雜湊索引不是最適當的類型。

鏈條和空桶的示範

下列 T-SQL 程式碼區塊可讓您輕鬆地測試 SELECT * FROM sys.dm_db_xtp_hash_index_stats;。程式碼區塊會在 1 分鐘內完成。以下是下列程式碼區塊的階段︰

建立含有數個雜湊索引的記憶體最佳化資料表。
在資料表中填入數千個資料列。
a. 模數運算子可用來設定 StatusCode 資料行中重複值的比率。
b. 迴圈大約會在 1 分鐘內插入 262,144 個資料列。
列印訊息要求您執行之前來自 sys.dm_db_xtp_hash_index_stats 的 SELECT。

DROP TABLE IF EXISTS SalesOrder_Mem;  
go  
  
  
CREATE TABLE SalesOrder_Mem  
(  
  SalesOrderId   uniqueidentifier  NOT NULL  DEFAULT newid(),  
  OrderSequence  int               NOT NULL,  
  OrderDate      datetime2(3)      NOT NULL,  
  StatusCode     tinyint           NOT NULL,  
  
  PRIMARY KEY NONCLUSTERED  
      HASH (SalesOrderId)  WITH (BUCKET_COUNT = 262144),  
  
  INDEX ix_OrderSequence  
      HASH (OrderSequence) WITH (BUCKET_COUNT = 20000),  
  
  INDEX ix_StatusCode  
      HASH (StatusCode)    WITH (BUCKET_COUNT = 8),  
  
  INDEX ix_OrderDate       NONCLUSTERED (OrderDate DESC)  
)  
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
go  
  
--------------------  
  
SET NOCOUNT ON;  
  
-- Same as PK bucket_count.  68 seconds to complete.  
DECLARE @i int = 262144;  
  
BEGIN TRANSACTION;  
  
WHILE @i > 0  
BEGIN  
  
  INSERT SalesOrder_Mem  
      (OrderSequence, OrderDate, StatusCode)  
    Values  
      (@i, GetUtcDate(), @i % 8);  -- Modulo technique.  
  
  SET @i -= 1;  
END  
COMMIT TRANSACTION;  
  
PRINT 'Next, you should query:  sys.dm_db_xtp_hash_index_stats .';  
go

先前的 INSERT 迴圈會執行下列動作︰

針對主索引鍵索引和 ix_OrderSequence 插入唯一值。
插入數十萬個資料列，其中只針對 StatusCode 顯示八個相異值。因此，ix_StatusCode 索引中值重複的比例較高。

若要在值區計數不是最佳選項時進行疑難排解，可檢查下列來自 sys.dm_db_xtp_hash_index_stats 之 SELECT 的輸出。我們將 WHERE Object_Name(h.object_id) = 'SalesOrder_Mem' 新增到從 D.1 節複製的 SELECT 中，以產生這些結果。

我們的 SELECT 結果顯示在程式碼之後，以手動方式分割成兩個較窄的結果資料表，以獲得較佳的顯示效果。

以下是「桶數計算」的結果。

IndexName	總桶數量	空桶數	空桶百分比 (EmptyBucketPercent)
訂單序列	32768	13	0
ix_StatusCode	8	4	50
PK_SalesOrd_B14003...	262144	96525	36

接下來是為「鏈結長度」的結果。

索引名稱	平均鏈長度	max_chain_length (最大鏈長)
ix_OrderSequence	8	26
ix_StatusCode	65536	65536
PK_SalesOrd_B14003...	1	8

讓我們解釋上述結果資料表中的三個雜湊索引︰

ix_StatusCode：

有 50% 的桶是空的，這是不錯的狀況。
不過，平均鏈結長度非常高 (65536)。
- 這表示有高比率的重複值。
- 因此，這種情況不適合使用雜湊索引。應該改用非叢集索引。

ix_OrderSequence：

0% 的桶是空的，這個比例過低。
平均鏈結長度為 8，儘管這個索引中的所有值都是唯一。
- 因此應增加桶的數量，以將平均鏈結長度減少到更接近 2 或 3。
由於索引鍵擁有 262144 個唯一值，因此值區計數至少應該為 262144。
- 如果預期未來會有所成長，則桶數應該更高。

主鍵索引 (PK_SalesOrd_...)：

有 36% 的桶是空的，這是好事。
平均鏈結長度為 1，這也很理想。不需進行任何變更。

權衡取捨

OLTP 工作負載專注於個別的資料列。完整資料表掃描通常不會是影響 OLTP 工作負載效能的關鍵因素。因此，您必須在記憶體使用量與等號測試及插入操作的效能表現之間進行取捨平衡。

如果記憶體使用量是更大的考量：

請選擇接近唯一索引鍵值數量的桶數。
貯體計數不應該大幅低於唯一索引鍵值數目，因為這樣會影響大部分 DML 作業，以及影響在伺服器重新啟動後復原資料庫所需的時間。

如果相等性測試的效能是更大的顧慮：

適合使用較高的值區計數 (唯一索引值數目的二到三倍)。較高的計數代表︰
- 尋找特定值時，擷取速度更快。
- 提高記憶體使用量。
- 完整掃描雜湊索引所需的時間增加。

延伸閱讀

記憶體最佳化資料表的雜湊索引
 記憶體最佳化資料表的非叢集索引

共用方式為