Memory Optimized Tables - Slower INSERT than SSD Memory Optimized Tables - Slower INSERT than SSD sql-server sql-server

Memory Optimized Tables - Slower INSERT than SSD


UPDATE

After much testing and research, I believe this comes down to parallelism. At this time, SQL Server 2016, up to and including SP1 CU7, does not support parallel insert to Memory-Optimized Tables. This makes all INSERT statements to Memory-Optimized tables single-threaded.

Here is an insightful article from Niko Neugebauer regarding this issue:Niko Neugebauer - Parallelism in Hekaton (In-Memory OLTP)

This makes it much less useful for ETL/ELT ingestion. However, it is pretty amazing for OLTP DML (especially through natively compiled stored procedures), and stellar for aggregating data in BI queries. For ingestion, it is nearly impossible to beat SSD-based heaps without indexes, so long as you take the right steps to ensure that your INSERT will run in parallel.

Even if the database is in Full Recovery mode, the parallel INSERT to disk-based heap outperforms the INSERT to a Memory-Optimized table. This continues to remain true if comparable indexes are added to the disk-based table after the INSERT.