In-depth Discussion of In-Memory Online Transaction Processing in SQL Server

In this article, we take an in-depth discussion into online transaction processing features present in latest iterations of SQL Server

In-Memory OLTP In SQL ServerLatest SQL Server editions come with the much vaunted In-Memory online transaction processing (OLTP) engines. This feature helped users improve SQL Server performance by improving the in-memory capabilities of the system, scaling operations in SQL Server and accelerating applications. To achieve all the following SQL users often opt for redesigning the basic read-write code or improving the existing hardware. These are efficient solutions but also have their own set of limitations, redesigning a code is very time consuming and might also introduce bugs in the system.  And a better hardware is often a very expensive solution. To avoid the limitations that come along with these solutions, we can opt for In- Memory solutions.

A good number of software development professionals have began opting for in-memory solutions to solve the performance issues relating to SQL Server. In-Memory solutions are increasingly gaining prominence, caching is one of those solutions, which will be discussed in detail in this article. Primarily caching prevents cached queries from accessing the active database thus preventing locks and blocks by reducing the load of the CPU and memory. This improves the storing and accessing of data in-memory (RAM) enabling the development and IT teams to achieve the service level agreement.

The two different types of caching techniques through which In-memory OLTP is implemented are:

  1. Data-Grids and Distributed Caching Solutions: Application developers are increasingly opting for this technique. This is best suited for parallel processing and high performance in large scale social networking applications similar to Facebook and will also work well for stock trading in real-time. However this requires building an elaborate code, and maintaining it as well.
  1. In-Memory Dynamic Caching: This enables automatic analysis and processing of queries and stored procedures. In- memory cache is leveraged by read-only queries and procedures, and cache invalidation is applied on write commands, if required, in real- time. This is a more convenient and thus popular solution.

SQL Server 2014 In-Memory OLTPAccording to Microsoft, In-Memory OLTP is one of the biggest features of SQL Server, which can provide cheap and reliable solution for faster data processes. The in-memory OLTP engine has been built using a lock-free along with a latch-free design. Further it also makes use of an optimistic multi-version concurrency based control mechanism. To make use of SQL Server In-Memory OLTP, it is important for a database to have certain tables which are declared as memory-optimized. These tables might either be used like a conventional database table or can also act as a substitute for a temporary table. Keeping a table in- memory provides for additional speed improvements. Although reading and writing to an in-memory table will lock individual rows, there would still be no concurrency issues in rows, due to efficient handling of rows.

Along with all its benefits and usefulness, there are also a few drawbacks to In-Memory OLTP engine, it does not provide support for multiple T-SQL operators and functionalities, migration of existing applications is complicated, it cannot be deployed automatically and is also not plug-and-play friendly. It need to be defined carefully, developed and then tested.

Even the Most Efficient SQL Implementations can be Victims of a SQL Crash

SQL crashes can occur in nearly every SQL implementation irrespective of the SQL Server version you are running. The best way to protect your data involves investing in a

sql server recovery tool like DataNumen SQL Recovery that come to your rescue in the event of a crash. It can assuredly get back all your data without any fuss and can handle nearly every storage format including virtual drives. Further the tool is equipped to deal with humongous files and extensive incidents of SQL corruption.

Author Introduction:

Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit

Comments are closed.