uncategorized

透過參考數值設定In Memory Table的Index

自從SQL 2014開始，有了In Memory Table感覺在效能上又多了一道曙光，而到了SQL 2016這部分又更強化了，可用的SQL語句又增加，雖然In Memory Table是可以增加效能，但是，如果沒有設定好index反而會造成效益不彰的狀況，這幾天針對這部分進行一些調教，原本以為跟傳統資料表設定index一樣，最後發其實沒有這樣簡單，反而相對複雜，主要是因為在In Memory Table中的Index多了一個BUCKET_COUNT需要考量，設定太大，則消耗記憶體，設定太小速度反而更慢。在In Memory Table中除了叢集索引外，還可以設定非叢集索引，非叢集索引分為兩類

  • 非叢集索索引 : Index…..NONCLUSTERED
  • 雜湊型非叢集索引 : Index…..NONCLUSTERED HASH…..WITH (BUCKET_COUNT = 20)

這兩種索引要去設定，還真的會讓人搞昏，所以，藉由一些參考值讓我們至少有一些些依據可以設定，而非盲目的亂設定，尤其是BUCKET_COUNT的大小

BUCKET_COUNT 值參考

如果你設定的索引值，在資料表內的資料重複性相當高的時候，建議採用非叢集索引，反之，該索引所造成的資料重複性小的時候，建議採用雜湊型非叢集索，造成這兩差異主要在於BUCKET_COUNT的設計，而就官方說法，如果這個索引中你無法很明確設定BUCKET_COUNT值，就要改用非叢集索引。若是，確定要設定NONCLUSTERED HASH index時，不知道怎樣設定BUCKET_COUNT大小時候，可以透過下面語法的值去設定

1
2
3
4
5
SELECT    POWER(2, CEILING(LOG(COUNT(0)) / LOG(2))) AS 'BUCKET_COUNT'
FROM      ( SELECT DISTINCT
                      A,B
            FROM      Table
          ) [T];

其中A&B就是要設定索引的欄位，由這樣方式可以計算出BUCKET_COUNT值，再由這個值去設定或是比這個值大兩倍去設定都是可以

調整非叢集索引類型

索引不是一開始建立後就可以不管它，畢竟，當資料流穩定之後，還是必須回頭看看設定的index是否是有確實被用到或是有遺漏的，而在In Memory Table中，也必須確定倒底是要設定雜湊型還是非雜湊型。所以，要在Review一下

1
2
3
4
5
6
7
8
9
10
11
12
SELECT OBJECT_NAME([hs].[object_id]) AS 'object name'
      ,[i].[name] AS 'index name'
      ,[hs].[total_bucket_count]
      ,[hs].[empty_bucket_count]
      ,FLOOR(( CAST([empty_bucket_count] AS FLOAT) / [total_bucket_count] )
             * 100) AS 'empty_bucket_percent'
      ,[hs].[avg_chain_length]
      ,[hs].[max_chain_length]
FROM   [sys].[dm_db_xtp_hash_index_stats] AS [hs]
       JOIN [sys].[indexes] AS [i]
       ON [hs].[object_id] = [i].[object_id]
          AND [hs].[index_id] = [i].[index_id];

透過上面語法，透過一些指標去判定設定Index是否合宜。

  • empty_bucket_percent : 小於 10%，表示這個值區計數可能太低，理想的應該是33%或更高
  • avg_chain_length : 表示是否有重複值，理想的平均鏈結長度為 1

因此，在這兩個值中，如果 avg_chain_length 大於 10，且 empty_bucket_percent 大於 10%，則可能有許多重複的索引鍵值，那麼非叢集索引會較為理想，而不是設定雜湊的非叢集索。當發現empty_bucket_percent過小或是趨近於0，就必須看是否要把BUCKET_COUNT值調大了

參考資料

https://msdn.microsoft.com/zh-tw/library/dn494956(v=sql.120).aspx.aspx)

Share