You have migrated some tables to In-Memory OLTP, but instead of getting better performance on table operations, everything seems to be the same or even worse. What is going on? In this tip I will guide you to the answer for that question.
In my previous tips about Hekaton, I wrote about the advantages of In-Memory OLTP, but as happens with any database technology it requires some adjustments to take full advantages of new features. The more robust a product is the more adjustments you can make. I have told you about statistics and the advantages of hash indexes over range indexes for equality searches and now we are ready to go a step further. I am referring to tuning of hash indexes, more specifically, setting the right value for the number of buckets (BUCKET_COUNT).
What does BUCKET_COUNT mean and what is its importance?
The value of BUCKET_COUNT represents the number of buckets a Hash index has. We can think of a bucket as an index position in which a row address is stored. Since I explained what a bucket is in my previous tip Understanding SQL Server Memory-Optimized Tables Hash Indexes, I won't go into further details on this matter.
For a Hash index, ideally distinct row keys will match distinct buckets and equal row key hashes will match to the same bucket.
But this does not always happen and there are cases in which two different keys hash to the same bucket. This is called a "Hash collision" and the way Hekaton solves this is by chaining the existing row with the new or updated row with index pointers. Also row chaining happens when two keys are the same because they will always have the same hash value.
For the purposes of this article I will assume that index keys are unique keys because equal keys will match the same buckets, so with equal keys chaining is unavoidable.
So considering the above, the idea of setting the BUCKET_COUNT value equal to the number of distinct keys is not entirely wrong. But there are other things to take into consideration.
Even though the hash function used in SQL Server 2014 is a very good one, it is not perfect. So hash collisions will happen and that means row chaining will happen.
Also, as Hekaton has an optimistic multi-version concurrency control, in order to keep track of a row's versions, the Engine chains the inserted row (remember that Hekaton handles updates as a delete followed by an insert) at the end of the bucket chain. Of course, there is a Garbage collector within the Engine that removes versions no longer needed to shorten the chain and to free system memory.
So if we set the BUCKET_COUNT to 1,073,741,824 then the index will use 8.589.934.592 bytes which is 8GB. No matter how many rows the table has.
When performing a table or index scan Hekaton needs to look at each bucket of the Hash index.
How to check if BUCKET_COUNT is too low?
The first and easiest method to check if your index is low on buckets is to divide the value specified on BUCKET_COUNT by the number of distinct keys on the index. If the result has a value less than 1 then you have low buckets. You should aim for a value between 1.5 and 2.
The next query will return the next power of two of the number of distinct keys in the table. In most cases it should be enough.
Another way is to use the new Dynamic Management View (DMV) sys.dm_db_xtp_hash_index_stats. This DMV provides some useful statistics for hash indexes.
Here is a description of its columns.
The object ID of table.
The number of empty buckets.
The average length of the row chain.
The maximum length of the row chain.
With the average and maximum chain length you can get a vague idea of the chain length dispersion and gives an idea on how many keys are hashed to the same bucket.
A fairly empty bucket count and a high average chain length means that the Hash index has many duplicate keys.
If the values of average and maximum chain length are high and the empty buckets are low then the index is low on buckets, so consider re-creating the table.
I want to emphasize that you don't have to look only at the value of empty_bucket_count to check if the index is low on buckets.
Remember that the chain length increases if there are transactions updating rows due to Hekaton's multiversion concurrency control until the garbage collector reorganizes the index pointers.
Now I will explain with examples how to determine if your indexes are low on buckets.
Now we create a table whose index has four buckets.
With this script we load our table with 99999 rows for our tests.
To check the statistics of our Hash index I will query the sys.dm_db_xtp_hash_index_stats DMV.
The next query is very simple but effective to show the performance impact of chaining on a hash index.
The query looks innocent, but has some aspects to remark.
Remember from my previous tip that hash indexes aren't optimum for range searches. Also remember that Hekaton handles updates as a delete followed by an insert. And being that the query is within the context of a transaction and the multi-version nature of Hekaton's concurrency control, the outcome is that the updated rows will create new rows on the same chain bucket and the execution loop makes the query more stressful.
As you can see on the image below the query doesn't perform very well.
I decided to include in the script a query to sys.dm_db_xtp_hash_index_stats DMV so you can see the impact of the updates on the chain length. As you can see the maximum chain length increased from 25,003 to 27,522, a difference of 2,519.
We need to create a new table with a proper number of buckets on its Hash index.
The script below returns the value we should use on the BUCKET_COUNT of our new table index which is the next power of two of the number of distinct keys on the index.
It is time to create a new table with the proper number of buckets for the Hash index.
With the next script we copy the data from dbo.Customers table to dbo.Customers_New table.
Now we execute the previous query again on our new table.
As we can see on the image below, the execution time has reduced remarkably.
Also look at the values for max_chain_length. It increased from 4 to 9 which is a difference of 5, the numbers of execution on the loop.
Compare this value with the previous run. Remember that to get to the row to be updated the Engine follows the pointers of the chain until it reaches the row with the transaction timestamp. This is an explanation for the difference in query times.
Read my tip about Understanding SQL Server Memory-Optimized Tables Hash Indexes.
Check this out to learn about Snapshot Isolation in SQL Server 2005.
Need to execute a TSQL batch multiple times as I done in this tip?: Executing a TSQL batch multiple times using GO.
Read about Dynamic Management Views and Functions in SQL Server 2005.
And also Dynamic Management Views and Functions in SQL Server 2008.
Here you will find a deeper insight of Dynamic Management Views Dynamic Management Views and Functions Tips Category.
This ia great article. I have been experimenting with SQL 2014 and memory optimized tables and found it difficult to determine bucket count. This article is very helpful.