SQL Server In Memory OLTP - Memory Optimized Table

The memory optimized tables were first introduced with SQL 2014. The basic concept behind the introduction of memory optimized tables was to equip the sql server with 100x processing capabilities for some specific requirements (tables).
Before recommending memory optimized table you must understand about the memory optimized tables and how the data is stored, what type of indexes can be created on IM (In Memory) tables. There are basically two types of IM tables i.e schema durable and schema and data durable.

To learn more about In Memory OLTP you can go to the below MSDN Link.

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/memory-optimized-tables

I have done few test with the processing capabilities of the IM tables and compared the same with disk based tables which are mentioned below.

I had loaded around 18000000 rows and tried to query data against that. to return 10000 rows based on some parameter condition the disk based table took approximately 5520 millisecond and IM table took 853 millisecond.

Now lets come to pros and cons (SCHEMA_DATA durable table)

• We see 10-100x performance improvement while retrieving data using natively compiled procedures.
• Disk based table occupied around 8GB data on drive and for same amount of data around 27GB of RMA was used.
• You can see the retrieval performance through natively optimized code only.
• DDL operation is very time and memory intensive once data is added to the table, so carefully use it.
• You might need to create resource pool in case if you face any out of memory exception while creating/loading a large table.
• If you restart the SQL Server service then for sometime your DB might go to recovery mode till all the IM table data loaded back to RAM.

Please read through the query processing guideline

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/a-guide-to-query-processing-for-memory-optimized-tables

Also read about indexes and buckets

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/indexes-for-memory-optimized-tables