SQL - In-Memory OLTP

In-Memory OLTP (Online Transaction Processing) is a standard feature that promises breakthroughs in performance and this feature was introduced with SQL Server 2014 with a big step forward in Microsoft's database capabilities. It provides a way to use In-Memory OLTP tables in a high-scale caching role whereby an in-memory table is used for high-scale, high-speed ingest and acts as a kind of data shock absorber where data can be buffered, transformed and delivered to a variety of applications and analyses.

As we know that data is the most important thing for the business prospective because we need to visualised it into the various formats and comparing the data trends with historical data trends. Based on the historical data trends, Business users/data analysists will be capable to take the right decision for the business growth.

In-Memory OLTP is used to get around disk I/O-throughput bottlenecks and read-write contention without rewriting applications designed to run on SQL Server.  SQL Server 2014 integrates this new technology directly into the database engine instead of being a separate add-on.

Where can we use In-Memory OLTP technique?

To use In-Memory OLTP, you can define a heavily accessed data table as memory optimised because Memory-optimised-tables are fully transnational, durable, and are accessed using Transact-SQL in the same way as disk-based tables. The most interesting facts are that we can easily update data in memory-optimised tables and disk-based tables as well as pull the data from the both data storage.

Native Compilation for Stored procedures

Stored procedures that only reference memory-optimised tables can be natively compiled into machine code for further performance improvements.

Benefits of In-Memory OLTP

1. Logical I/O operations straight from physical memory are much faster than the physical I/O equivalent from storage.
2. It will improve performance best in OLTP with short-running transactions because everything is happening in-memory.
3. Programming patterns that In-Memory OLTP will improve include concurrency scenarios, point lookups, workloads where there are many inserts and updates, and business logic in stored procedures.
4. Integration with SQL Server and Azure SQL Database means you can have both memory-optimised tables and disk-based tables in the same database, and query across both types of tables.

In-Memory OLTP achieves significant performance and scalability gains by using:

1. Algorithms that are optimised for accessing memory-resident data.
2. Optimistic concurrency control that eliminates logical locks.
3. Lock free objects that eliminate all physical locks and latches. Threads that perform transactional work don’t use locks or latches for concurrency control.
4. Natively compiled stored procedures, which have significantly better performance than interpreted stored procedures, when accessing a memory-optimised table.

Restrictions for In-Memory OLTP

1. FOREIGN KEY constraints, DEFAULT constraints, CHECK constraints, IDENTITY columns and Triggers are not allowed on Memory-optimised Tables.
2. If any of the restricted functionality is required, it can usually be coded using natively compiled Stored Procedure code.

Conclusion

The In-Memory OLTP option is an Enterprise-level feature which is designed specifically for typical OLTP traffic balancing read/write operations on the data with concurrent access. It has to be enabled at the database level before proceeding to create Memory-optimised tables.
Memory Management for In-Memory OLTP

Reference: https://msdn.microsoft.com/en-us/library/dn133186.aspx