In-Memory OLTP is a technology used in SQL Server to improve the performance of transactional systems, including two core parts: memory-optimized tables and native compiled stored procedures. It reduces I/O and lock competition by retaining data in memory, and is suitable for high-frequency read and write scenarios such as financial transactions. The steps to enable include: 1. Confirm SQL Server version support; 2. Add the MEMORY_OPTIMIZED_DATA file group; 3. Optionally configure the resource pool to limit memory usage; 4. Use MEMORY_OPTIMIZED=ON to create a table and select an appropriate isolation level. Notes include reasonably estimating memory, trying to use native stored procedures, avoiding hotspot contention, monitoring operation status, and paying attention to types and constraints. This technology can significantly improve performance in suitable scenarios, but requires comprehensive evaluation of compatibility and resource requirements.

If you are dealing with high concurrency, low latency transactional database systems, SQL Server's In-Memory OLTP is a technical option worthy of serious consideration. It is not simply "putting tables into memory", but optimizing transaction processing performance through a complete set of mechanisms.

What is In-Memory OLTP?

In-Memory OLTP (Online Transaction Processing) is a functional component provided by SQL Server to retain frequently accessed data and business-critical logic in memory to reduce performance bottlenecks caused by disk I/O and lock competition.
It consists mainly of two parts:

• Memory-Optimized Tables : Data resides in memory, using non-persistent structural design to improve access speed.
• Natively Compiled Stored Procedures : Compile T-SQL into C-level DLLs to reduce the interpretation overhead during execution.

This feature is suitable for high frequency read and write and response time sensitive application scenarios, such as financial transactions, inventory management or real-time order processing.

How to enable and configure In-Memory OLTP?

To use In-Memory OLTP in SQL Server, you first need to confirm that your version supports this feature (such as SQL Server 2014 and above Enterprise or Standard Edition). Then follow the steps below:

• Add memory optimization filegroups :
  Add a MEMORY_OPTIMIZED_DATA file group at the database level, which is used to store transaction logs and checkpoint files for memory optimization tables.

  

• Configure resource pool (optional but recommended) :
  Use Resource Governor to limit the amount of memory used by memory-optimized workloads to avoid affecting other database tasks.

• Create a memory optimization table :
  Create a table with MEMORY_OPTIMIZED = ON parameter and select the appropriate transaction isolation level, such as SCHEMA_ONLY or SCHEMA_AND_DATA.

For example:

 CREATE TABLE dbo.SalesOrder (
    OrderID INT PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
    CustomerID INT NOT NULL,
    OrderDate DATETIME2
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Here are a few key points to pay attention to:

• The primary key must be a non-clustered hash index (unless you are using a non-memory optimized primary key)
• BUCKET_COUNT It is recommended to set to 1 to 2 times the expected number of unique values
• Persistence settings determine whether to log transaction logs

Performance optimization suggestions and precautions

While In-Memory OLTP can bring significant performance improvements, there are some limitations and best practices to note:

• Reasonably estimate memory requirements : Each row of data occupies much larger memory than traditional tables, especially with multiple indexes. Make sufficient memory space available, otherwise it may cause OOM errors.
• Try to use native stored procedures : If the frequently called business logic is still using ordinary T-SQL, the performance advantages will be reduced. Modifying stored procedures to native compile can further reduce CPU overhead.
• Avoid hotspot contention : Memory optimization tables use optimistic concurrency control, and large numbers of updates to the same row may cause retry failures. It can be mitigated by splitting hotspot data or adjusting application logic.
• Monitor the running status : Check DMVs such as sys.dm_db_xtp_table_memory_stats and sys.dm_db_xtp_sessions regularly to understand memory usage and active transactions.

In addition, In-Memory OLTP has limitations on certain T-SQL functions, such as not supporting TEXT, NTEXT, XML types, and the inability to use foreign key constraints. Therefore, compatibility evaluation is required when migrating existing tables.

Basically that's all. In-Memory OLTP is not a universal accelerator, but it can indeed significantly improve the throughput and response speed of trading systems in the right scenario.

The above is the detailed content of SQL In-Memory OLTP for High-Performance Transactions. For more information, please follow other related articles on the PHP Chinese website!