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)