In-Memory OLTP Internals - Pro SQL Server Internals - page 640

You can monitor hash index-related statistics with the sys.dm_db_xtp_hash_index_stats data management
view. This view provides information about the total number of buckets, the number of empty buckets, and the
average and maximum row chain lengths.

■ you can read more about the sys.dm_db_xtp_hash_index_stats view at: http://msdn.microsoft.com/en-
us/library/dn296679.aspx .

Note

Hash indexes have different SARGability rules as compared to indexes defined on on-disk tables. They are
efficient only in the case of an equality search , which allows SQL Server to calculate the corresponding hash value and
find a bucket in a hash array.
In the case of composite hash indexes, SQL Server calculates the hash value for the combined value of all key
columns. A hash value calculated on a subset of the key columns would be different and, therefore, to be useful,
a query should have equality predicates on all key columns for the index.
This behavior is different from indexes on on-disk tables. Consider the situation where you want to define an
index on (LastName, FirstName) columns. In the case of on-disk tables, that index can be used for a Seek operation,
regardless of whether the predicate on the FirstName column is specified in the where clause of a query. Alternatively,
a composite hash index on a memory-optimized table requires queries to have equality predicates on both LastName
and FirstName in order to calculate a hash value that allows for choosing the right hash bucket in the array.
Let's look at the example and create on-disk and memory-optimized tables with composite indexes on the
(LastName, FirstName) columns, populating them with the same data as shown in Listing 32-3.

Listing 32-3. Composite hash index: Test tables creation
create table dbo.CustomersOnDisk
(
CustomerId int not null identity(1,1),
FirstName varchar(64) collate Latin1_General_100_BIN2 not null,
LastName varchar(64) collate Latin1_General_100_BIN2 not null,
Placeholder char(100) null,

constraint PK_CustomersOnDisk
primary key clustered(CustomerId)
);

create nonclustered index IDX_CustomersOnDisk_LastName_FirstName
on dbo.CustomersOnDisk(LastName, FirstName)
go

create table dbo.CustomersMemoryOptimized
(
CustomerId int not null identity(1,1)
constraint PK_CustomersMemoryOptimized
primary key nonclustered
hash with (bucket_count = 30000),
FirstName varchar(64) collate Latin1_General_100_BIN2 not null,
LastName varchar(64) collate Latin1_General_100_BIN2 not null,
Placeholder char(100) null,