{"id":1933,"date":"2013-10-09T13:00:00","date_gmt":"2013-10-09T20:00:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2013\/10\/09\/troubleshooting-common-performance-problems-with-memory-optimized-hash-indexes\/"},"modified":"2024-01-22T22:49:09","modified_gmt":"2024-01-23T06:49:09","slug":"troubleshooting-common-performance-problems-with-memory-optimized-hash-indexes","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2013\/10\/09\/troubleshooting-common-performance-problems-with-memory-optimized-hash-indexes\/","title":{"rendered":"Troubleshooting Common Performance Problems with Memory-Optimized Hash Indexes"},"content":{"rendered":"

In-Memory OLTP introduces a new type of index for memory-optimized tables in SQL Server 2014: the hash index. This new index type is optimized for index seek operations, i.e. finding the rows corresponding to a given set of equality predicates; for example, \u2018o_id=8 AND od_id=4\u2019. The underlying data structure of a hash index is a hash table, which is quite different from the B-tree data structure underlying the traditional (non)clustered indexes for disk-based tables. <\/span><\/p>\n

In this post we will not describe the hash index structures in details; more information can be found in the Books Online topic on <\/span>In-Memory OLTP<\/span><\/a>. Instead, we focus on troubleshooting two common issues we have seen with customers using hash indexes. For each of the common issues we describe how to troubleshoot and how to work around the issue. Future blog posts will address further common performance problems. <\/span><\/span><\/p>\n

Hash index bucket count too low<\/span><\/span><\/h1>\n

Issue<\/strong>: If the bucket count is significantly lower (think 10X) than the number of unique index keys, there will be many buckets that have multiple index keys. This degrades performance of most DML operations, in particular point lookups, i.e. lookups of individual index keys.<\/span><\/p>\n

Symptom<\/strong>: A performance degradation of queries that rely on lookups or inserts into the hash index. For example, SELECT queries and UPDATE\/DELETE operations with equality predicates matching the index key columns in the WHERE clause.<\/span><\/p>\n

How to troubleshoot<\/strong>: In some cases the problem is obvious from the index definition and the table data. For example, if the PRIMARY KEY has a HASH index with bucket_count 10,000, and the table has 1,000,000 rows, the bucket count is too low and will need to be changed.<\/span><\/p>\n

In addition to inspecting table schema and data, you can use the DMV <\/span>sys.dm_db_xtp_hash_index_stats<\/span><\/a>. You can use the following query to obtain statistics concerning the buckets, and the row chains hanging off the buckets:<\/span><\/span><\/p>\n

SELECT<\/span> hs.object_id<\/span>, object_name<\/span>(hs.object_id<\/span>) AS<\/span> 'object name'<\/span>, i.name as<\/span> 'index name'<\/span>, hs.*
FROM<\/span> sys<\/span>.dm_db_xtp_hash_index_stats AS<\/span> hs
JOIN sys.indexes <\/span>AS<\/span> i ON<\/span> hs.object_id<\/span>=i.object_id<\/span> AND hs.index_id=i.index_id;<\/code><\/p>\n

A large average chain length indicates that many rows are hashed to the same bucket. If, in addition, the number of empty buckets is low or the average and maximum chain lengths are similar, it is likely that the total bucket count is too low. In this case, you need to increase the bucket_count. Typically, you would want the bucket_count to be between 1 and 2 times the number of unique index key values. Note that the bucket_count is automatically rounded up to the nearest power of 2.<\/span><\/p>\n

\n\u00a0<\/span>Search requires a subset of hash index key columns<\/span><\/span>
\n<\/h1>\n

Issue<\/strong>: Hash indexes require values for all index key columns in order to compute the hash value, and locate the corresponding rows in the hash table. Therefore, if a query includes equality predicates for only a subset of the index keys in the WHERE clause, SQL Server cannot use an index seek to locate the rows corresponding to the predicates in the WHERE clause.<\/span><\/p>\n

In contrast, ordered indexes like the traditional disk-based (non)clustered indexes and the new memory-optimized nonclustered indexes (to be introduced in CTP2) support index seek on a subset of the index key columns, as long as they are the leading columns. <\/span><\/p>\n

Symptom<\/strong>: This results in a performance degradation, as SQL Server will need to execute full table scans, rather than an index seek, which is typically a far cheaper operation.\u00a0 <\/span><\/p>\n

How to troubleshoot<\/strong>: Besides the performance degradation, inspection of the query plans will also show a scan instead of an index seek. If the query is fairly simple, inspection of the query text and index definition will also show whether the search requires a subset of the index key columns.<\/span><\/p>\n

Consider the following table and query:<\/span><\/p>\n

create table<\/span> dbo.od
 ( o_id int<\/span> not null,
 od_id int<\/span> not null,
 p_id int<\/span> not null,

constraint<\/span> PK_od primary key<\/span> 
nonclustered hash<\/span> (o_id,od_id) with<\/span> (bucket_count<\/span>=10000)
 ) with<\/span> (memory_optimized<\/span>=on)

select<\/span> p_id
from<\/span> dbo.od
where<\/span> o_id=1<\/code><\/pre>\n
\u00a0<\/span>The table has a hash index on the two columns (o_id, od_id), while the query has an equality predicate on (o_id). As the query has equality predicates on only a subset of the index key columns, SQL Server cannot perform an index seek operation using PK_od; instead, SQL Server has to revert to a full index scan, as shown in the following query plan visualization:<\/span><\/span><\/p>\n
Workarounds<\/strong>: There are a number of possible workarounds. For example:<\/span><\/p>\n
\"query<\/a><\/p>\n