SQL Server In-Memory OLTP

In my next few blogs, I would attempt to explain you about SQL Server’s In-Memory OLTP (Hekaton). Let me know how am I doing with it by giving your valuable comments. In-Memory OLTP is the new feature introduced with the latest version of Sql Server i.e SQL Server  2014.

What is In-Memory Computing?

Kirill Sheynkman describes In-Memory Computing as “In-Memory Computing is based on a memory-first principle utilizing high-performance, integrated, distributed main memory systems to compute and transact on large-scale data sets in real-time – orders of magnitude faster than traditional disk-based systems.”

Memory-first principle refers to an optimizations one can take advantage of when data is stored mainly in RAM (Yes in Ram, Not in disks).

What is In-Memory OLTP?

You may have imagined what is In-Memory OLTP after knowing what is In-Memory computing. You are right, In-Memory OLTP is the data processing technique that works with the data stored in memory of the server. In-Memory OLTP is a new feature introduced in Sql Server 2014 that stores, retrieves and performs query with data in memory.

Why data are stored in Memory? What is the Cost? What happens when Server shuts down?

Not only RAM is ~1000 times faster than physical mediums, it completely eliminates the traditional overhead of block-level devices including marshaling, paging, buffering, memory-mapping, possible networking, OS I/O, and I/O controller. So, when data is stored in memory, all the time spent on Lock, paging, I/O is saved allowing you to perform your transactions faster and without locks.

There were the days when computer memory was too expensive and was available in smaller size only. Now, RAM costs less than $10 for GB and we have up to 12TB of Ram as of writing this blog. Now, what happens when the server goes off? Obviously you know, When the server is shut down, everything stored in RAM is flushed. And what about data? When Server is up again, your data is back on Memory. SQL Server uses Data and Delta files in Memory Filegroup and regular log files to store information about your data so that in case of server shutdown or any other failure, data is reloaded into memory. Always remember one thing, data is always stored in Memory and Logging is always done on disk.

How to install In-Memory OLTP?

In-Memory OLTP is available only with 64 bit of Sql Server 2014. Also, it is only available for Enterprise Edition. You should select In-Memory OLTP feature during an installation of SQL Server 2014.

In my Next article, I will post on creating Memory Optimized database and tables.

Leave a Reply

Your email address will not be published. Required fields are marked *