Index options for Memory-Optimized Tables

        Indexes are crucial on file base SQL Server tables. Specifying the right index improves query performance dramatically. This applies to Memory Optimized Tables too. Memory Optimized tables are fast since their data stays in the SQL Server memory but that does not mean that they don't need indexes, you must have at least one index in memory optimized table. Also you can add more if you need to improve query performances.

      Indexes on memory optimized tables are called memory-optimized indexes. Here is important notes about them.

• You can not use CREATE INDEX statement to create a memory-optimized index. You need to define them when you are creating the table using CREATE TABLE statement.
• Memory-Optimized indexes stay in the memory, SQL Server does not use disk to store them or log them in transaction log. They are created in memory when you create a memory optimized table or when database startup.
• All columns are included in memory-optimized indexes. Indexes contain a memory pointers rather than a reference to Primary key.
• Fragmentation or fillfactor problems do not exist for memory-optimized indexes since SQL Server does not deal with disks.

You have two options when you need to crate memory-optimized index on a memory optimized table.

Nonclustered Hash Indexes

      Nonclustered Hash Indexes are for point lookups like Primary keys. Hash indexes include all columns. A Hash index consists of a collection of buckets organized in an array. This is the same bucket that you define with BUCKET_COUNT when you create a memory optimized table. Defining correct bucket count is important because it can impact workload performance. It is better to overestimate BUCKET_COUNT rather than keeping it too low.  To be safe, I recommend at least 2 times of the number of distinct values of its table.

 

Nonclustered indexes 

     Nonclustered indexes are for range scans or ordered scans. They support everything that hash indexes support, they also support seek operations and sort orders. Nonclustered indexes can help you significantly If your queries use grater than,less than operators or Order by clauses.

 

     Every memory-optimized table must have at least one index just like the disk based tables. Primary key constraints creates an index. If you have a primary key in your table, you have an index. Don't forget both indexes consumes memory, when you are planning to convert a file based table to a memory optimized table, consider the number of indexes and how much space they might take from the SQL Server. Please check out my earlier posts about Memory optimized tables if you like to learn about them more.

•  Introduction to Memory-Optimized Tables (MOT) Part 1
• Memory Optimized Tables (MOT) Part 2