Using indexes on memory-optimized tables

Question

Trying formulate an approach to SQL Server 2014 memory-optimized tables

One very simple table is the most active table in the application

Data is never mutated
Loads are all via a bulk process
Approx 200 million records
Currently all reads are with (nolock)

Table

int PK1  
int PK2  
composite clustered PK of PK1, PK2  
non-clustered index on PK2

The PK was chosen in that order as that is the order of the loads

During a load the non-clustered index is disabled and then rebuilt at the end of the load
That index killed load speed and was so fragmented at the end of a load that it needed to be rebuilt anyway

All searches are equality (never <, >, <>)
Most searches are on PK2
Some simple searches on PK1 and is used in joins.

Finally to the question(s).

It is my understanding that memory-optimized indexes do not fragment.
As an in-memory table would I reverse the PK (PK2, PK1) and have a second index on PK1?
Is there no reason to drop and recreate the index on PK1?
Does index fragmentation truly go away in a memory-optimized table?

I think the answer is yes but it just seems to good to be true.

Guidelines for Using Indexes on Memory-Optimized Tables

On further examination there are limitations:

ALTER TABLE, sp_rename, alter bucket_count, and adding and removing an index outside of the CREATE TABLE statement is not supported for memory-optimized tables.
UNIQUE, CHECK, and FOREIGN KEY constraints are not supported.

Transact-SQL Support for In-Memory OLTP
Did not open the question to criticize the product and it is a cool feature. But if a table does not support declarative referential integrity (DRI) can you call it a relational database?

score 1 · Accepted Answer · answered May 21 '14 at 23:22

From your questions.

It is my understanding that memory-optimized indexes do not fragment.
As an in-memory table would I reverse the PK (PK2, PK1) and have a second index on PK1?
Is there no reason to drop and recreate the index on PK1?
Does index fragmentation truly go away in a memory-optimized table?

Question 1, yes memory optimized indexes don't fragment.

Question 2, no. what you want is a hash index on PK2 and a hash index on PK1. if you want to preserve key uniqueness on PK1, then you'd need a non clustered key on PK1 and PK2. Be careful that PK2 doesn't have a lot of repetition.

Question 3, dropping and re-creating an index can't be done in memory optimized tables.

Question 4, yes fragmentation goes away with memory optimized tables.

Thanks Guy

SQL 2014 was released and I was able to test this a couple weeks ago and came to the same conclusions as you. My problem is that on this very large table it took up too much memory so I was not able to put in into production. — paparazzo, May 22 '14 at 16:42

score 0 · Answer 2 · edited May 23 '17 at 12:20

Since most searches will be on PK2, I would unload the data from the source table in the desired order it will be queried on in the target table. Example:

UNLOAD TO "loadfile" SELECT * FROM sourcetable ORDER BY pk2, pk1;

DROP INDEXES ON targettable;

LOAD FROM "loadfile" INSERT INTO targettable;

CREATE NONCLUSTERED INDEX idxpk2 ON targettable(pk2);

CREATE NONCLUSTERED INDEX idxpk1 ON targettable(pk1);

Unloading the data in the sorted order it will most be queried on in the target table is essentially the same as CLUSTERING on pk2, except you wouldn't have to physically reorder the data in the target table. The loading speed will also improve by dropping the indexes on the target tablew before you load new data into it, and recreating the indexes will optimize access.

See my related question and answers.

Thanks, I get that revering the index will make queries faster. My question is more about load. In a memory optimized table can I load into that revered index with no penalty? In a memory optimized table does index fragmentation go away? In a memory optimized table do I even need to disable the index? That is not the syntax used for creating an index on a memory optimized table. — paparazzo, Aug 08 '13 at 14:56

Using indexes on memory-optimized tables

2 Answers2