Source : https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios?redirectedfrom=MSDN&view=sql-server-ver16

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/requirements-for-using-memory-optimized-tables?redirectedfrom=MSDN&view=sql-server-ver16

Use Cases / Environments

  • High velocity, high volume
  • Large amout of data turnover
  • CPU Pressure
  • Disk contention
  • Log activity
  • ETL

msdn - july 2016

Implementation ScenarioCharacteristicsBenefits of In-Memory OLTP
High data insertion rate from multiple concurrent connections, for example sensors in IoT (Internet of Things) scenario.Primarily append-only store.

Unable to keep up with the insert workload.		Eliminate contention.

Reduce logging.
Read performance and scale with periodic batch inserts and updates.High performance read operations, especially when each server request has multiple read operations to perform.

Unable to meet scale-up requirements.		Eliminate contention when new data arrives.

Lower latency data retrieval.

Minimize code execution time.
Intensive business logic processing in the database server.Insert, update, and delete workload.

Intensive computation inside stored procedures.

Read and write contention.		Eliminate contention.

Minimize code execution time for reduced latency and improved throughput.
Low latency.Require low latency business transactions which typical database solutions cannot achieve.Eliminate contention.

Minimize code execution time.

Low latency code execution.

Efficient data retrieval.
Session state management.Frequent insert, update and point lookups.

High scale load from numerous stateless web servers.		Eliminate contention.

Efficient data retrieval.

Optional IO reduction or removal, when using non-durable (SCHEMA_ONLY) tables.
Temp Tables and Table-Valued ParametersFrequent use of temporary tables in application logic.

Frequent use of Table-Valued Parameters (TVPs) to send data from the client to the server.		Eliminate contention in tempdb.

Remove IO by using memory-optimized table variables and non-durable (SCHEMA_ONLY) tables.
ETL (Extract, Transform, Load)Use of staging tables to load data into a data warehousing database.Efficient data access.

Remove IO by using non-durable (SCHEMA_ONLY) tables.

Memory Internals

Memory Optimized Tables

Memory Optimized Indexes

Natively Compiled Stored Procedures

Metrics and Observations