SQL Server In memory tables using tempdb

Question

I'm testing in memory tables on SQL 2014 and the execution plan for a particular query is showing a sort in tempdb.

The query has a join between two in memory tables (on a field that has a nonclustered index in both tables) and a group by with a few sums and counts - the server has plenty of RAM available.

Why is it the query being sorted in tempdb if the tables are"In Memory"?

I'm also wondering why is SQL Suggesting to create an index if the "create index" statement is not allowed on in memory tables.

Answer 1

This query is being run in interop mode. This uses the normal query processor except that it pulls data out of Hekaton tables. Otherwise there are no changes that I can think of.

The speedup that you obtain from Hekaton here is extremely limited if any.

You cannot use the create index statement but you can create indexes at table creation time.

Answer 2

The data may be stored in memory, but the query processor can still only use the memory it's been given in it's memory grant to actually execute the query, if the memory required by the sort operator exceeds this because of a poor estimate by the optimizer, it will spill the same way as a query that uses disk based tables will.