Click here to monitor SSC
  • Av rating:
  • Total votes: 22
  • Total comments: 7
Rob Garrison

Exploring In-memory OLTP Engine (Hekaton) in SQL Server 2014 CTP1

25 June 2013

The continuing drop in the price of memory has made fast in-memory OLTP  increasingly viable. SQL Server 2014 allows you to migrate the most-used tables in an existing database to  memory-optimised 'Hekaton' technology, but how you  balance between disk tables and in-memory tables for optimum performance requires judgement and experiment. What is this technology, and how can you exploit it? Rob Garrison explains.

Microsoft announced SQL Server 2014 at TechEd in early June, and the biggest news for SQL Server OLTP in years is the in-memory OLTP engine. This article is an introduction to the features and limitations of this new technology.

There is much to cover here. I will assume that you understand the current versions of SQL Server and so I will only discuss the differences.

Three important points about this new feature that should be stated up front:

  1. SQL Server 2014 will be a 1.0 version of this feature, and the development focus was on mainstream OLTP processing. Think light-weight transactional records processed with stored procedures. Many of the outlier OLTP features are not yet supported. Some of these gaps may be addressed by RTM, but some are not even planned until a later version.
  2. Hekaton can be leveraged inside of a normal SQL Server database. It does not require special hardware, nor does it require a separate database. The expectation is that you will migrate only a portion of your tables and stored procedures to Hekaton. That means your database will have a mix of standard and memory-optimized objects in the same database.
  3. There are customers who have been already running early releases in production with very good results.

CTP1 of SQL Server 2014 is planned for release at TechEd Europe in late June. Release of SQL Server 2014 is expected “in the second half of calendar year 2013.”

What is SQL Server In-memory OLTP Engine (Code-name: Hekaton)

Before the announcement of SQL Server 2014 and the sessions at TechEd 2013 in June, very little information was available about project Hekaton. We knew it was a new technology that 1) allowed tables to be resident in memory, 2) allowed for machine-compiled stored procedures, and 3) was claimed to be very fast.

Now that we have more details, here is a full picture of the two basic features of what is now officially called SQL Server In-memory OLTP Engine.

Memory-optimized Tables

“Memory-optimized” tables (as opposed to standard tables, now called “disk-based” tables) are completely in memory.

Memory-optimized Storage

Memory-optimized tables do not use pages for storage. The record location mechanism employed is pointers stored in hash tables.

Optimistic Concurrency Control

There are new algorithms that process records without locking and blocking. First, since there are no pages, there are no page latches. Also, writes to memory-optimized tables use versioning similar to RCSI (read committed snapshot isolation) except that tempdb is not used for row versions. Every write has an associated ever-increasing transaction number that allows for straightforward handling of reads. Uncommitted records are stored in-memory but are not visible until the transaction commits, so there are no “dirty reads” of uncommitted data.

Native Stored Procedures

We have talked for years about Standard SQL Server stored procedures being “compiled”, but the end-product of the compilation is code that is then interpreted at run-time. Hekaton has the ability to take high-level code and translate it to C and then build a DLL. When the stored procedure is called at run-time, the compilation steps and associated CPU overhead have already been completed. The goal here is to do the same work with many fewer CPU instructions. Based on reported results, the savings are dramatic. Microsoft is talking about 10X to 25X performance gains overall.

Native stored procedures are “atomic” in the sense that they run in a single CPU until they complete. This reduces context-switching, which is very costly in terms of CPU cycles.

An Early Caveat

Be aware that the only tables that can be involved in a native stored procedure are memory-optimized tables. Disk-based tables are not allowed. Stored procedures that access both memory-optimized and disk-based tables are called “interop” procedures.

Other Features

Block Transaction Log Writes

Whereas disk-based tables write individual records to the transaction log, memory-optimized tables write what we’ll call consolidated records. If you wrote a hundred records to a disk-based table that had a single non-clustered index (and were updating a column in that index), you would write two hundred records to the transaction log. With a memory-optimized table and a best-case scenario, you would write exactly one record to the transaction log with all the required detail. (Each log block is 24KB, so depending on the size of the records being written and the number of records, it could write multiple records.)

In-memory Indexes

Disk-based tables store non-clustered indexes on disk. For many OLTP tables, the size of the indexes is larger than the size of the base table. Creation and updates to indexes are written to the transaction log which takes CPU cycles and often very large amounts of disk space.

With memory-optimized tables, non-clustered indexes are not persisted to disk, so they take up no disk space, and updates do not have to be written to the transaction log. When a SQL Server 2014 database is started up, indexes for memory-optimized tables are built at the time that a table is loaded into memory.

Streaming Data Files

Hekaton leverages filestream capabilities to stream data records to disk. Records are written as a continuous stream; there are no UPDATE records, only INSERT and DELETE records. INSERTs are stored in “data files” and DELETEs are stored in “delta files”.

Non-durable Tables

Memory-optimized tables can have durable schema and non-durable data meaning that on start-up, the table is recreated, but no data is loaded.

How could this be used? Besides the ETL scenario described below, the obvious use is ASP session state. This also could be used anywhere you use a #temp or @temp table and you know that only one process is using the table at a given time. The usage of all the other features in Hekaton is rather straightforward. I expect that the provision of non-durable tables is the feature that will be used the most creatively of all. People will use this in ways that the SQL Server team never imagined. (Initially, non-durable tables are only supported in native stored procedures. By RTM, the plan is to support non-durable tables in interop stored procedures as well.)

No Buffer Pool

In Hekaton, there is no buffer pool. All the data for memory-optimized tables is in memory. Note that there are buffer pool-related changes in SQL Server 2014 that allow you to use SSDs to augment the size of your buffer pool.

What Problems Does Hekaton Address?

Scenario 1: Write-intensive Audit Log

Assume you have a centralized audit logging table for an OLTP system. You’ve designed it with an integer or bigint clustered primary key which means all new records are written to the end of the table. This design allows every page to be filled completely without any page fragmentation.

With a very high number of cores (past 12 to 16) and a high-performance OLTP workload, page latches can cause write delays because each writer has to acquire a page latch before it can write a new record.

With memory-optimized tables, there are no pages, so there are no page latches. Also, the writes are so fast that it is much less likely that multiple writers would try to access the same record at once. In disk-based tables, the waits are often because writers are trying to access the same page, rather than the same record. Without pages, this is no longer an issue.

Partitioning a Large Table

An application’s audit log table is likely to be partitioned by time, say a single partition per week. Since Hekaton does not (initially) support partitioning, we need to find a creative solution. The recommendation here is to have the active partition in-memory and all other partitions in a partitioned disk-based table. A view can be created to abstract away the complexity of the split tables.

Say you have two memory-optimized tables: CurrentAuditA and CurrentAuditB. Initially, the application is writing to A until the time boundary is hit. Then it switches and writes to B. (The view must be updated at this time to include table B instead of table A.) After the switch, a simple INSERT/SELECT statement writes the records from memory-optimized table A to disk-based table Audit2013W26 representing the 26th week. When that table has been fully processed (PK, indexes), it can be switched in to the partitioned audit log table.

Scenario 2: Read-intensive Table

Consider a large reference table; say a list of drugs with details about quantities, prices, etc. In an on-line consumer order application, access to this table would be almost all reads with occasional writes. And reads on popular drugs could be very high. The pages that contain popular drugs would likely be cached in memory even with a disk-based table.

Because memory-optimized tables use hash tables and pointers directly to memory-resident records, simple reads are much faster. For a read on a disk-based table, even if the pages are already loaded into memory, the system has to traverse multiple layers in a B-tree index. If the index is non-clustered and not a covering index, then the actual record is accessed. This results in more operations to return the same data.

Scenario 3: Mixed Read-write Customer Table

In that on-line drug order system, the table that holds orders would receive a large amount of writes along with some updates, deletes, and also many select statements. If one process is trying to update the status of an order to in-process and another process is trying to update the quantity, these two processes could end up locking or blocking each other.

Scenario 4: ETL Staging Table

Traditionally, staging tables for ETL processing have used heaps (tables without clustered indexes) and minimal or bulk logging. This was the way to get the best throughput because it minimized the amount of processing that was required with each write.

Memory-optimized tables allow the option of schema-persistence without data-persistence. Writes to these tables don’t cause any transaction logs writes because data written here is transient. If the server crashed during processing, the data can be simply reloaded from the source.

Caveats and Recommendations

Caveats

Write-write Conflicts

If two processes try to write to the same record at the same time, the first will succeed and the second will fail because there is no locking. This requires changes to stored procedures to handle the failure. Whether that “failure” should be followed by a retry or an error should be evaluated on a case-by-case basis. Think through the implications of two writers updating the same record. Retry basically mimics last-writer-wins logic. That is often the simplest choice but not the best choice.

Statistics Updates

Native stored procedures do not recompile automatically based on statistics updates. This will have to be handled through scripting or manual processing.

Running Out of Memory

As you might expect, running out of memory in a memory-optimized database is a problem. If your tables grow to the point where you run out of memory, write activity stops, but read activity can continue. Even many SELECTs would fail, so your database is fundamentally inoperable. The important thing to remember is that durable in-memory tables are fully ACID-compliant, so you will not have data loss in committed records. The SQL Server will continue to improve memory management before RTM.

Size Limitations

Record size is limited to 8,060 bytes. There are a number of creative solutions that should allow you to store your LOB data and long string or binary data in disk-based tables, but the important thing here is that this will require changes to your application.

Durable memory-optimized tables are limited to 512 GB. (Non-durable tables have no size limit.)

Foreign Keys and Check Constraints

My biggest disappointment with the initial version is that it does not support foreign key constraints or check constraints. For a database designer/developer, “let the application handle data quality” is like fingernails on a chalkboard.

IDENTITY, SEQUENCE

IDENTITY and SEQUENCE are not supported. Do a quick search on the web, and you’ll quickly find solutions like this one.

MERGE

Even interop-mode procedures do not support MERGE. The explanation is that the team concentrated on “mainstream” OLTP workloads, but this will cause grief for many of us that have widespread use of MERGE in our current code.

Other Limitations

More on the not-supported list:

  1. DML triggers
  2. Data types
    1. XML
    2. CLR
    3. LOB
  3. Schema changes are not support in the same way as disk-based tables.
  4. There are no heaps in memory-optimized tables. (This is not a bad thing, just interesting. It makes sense though. A heap is just a bag of records in a particular spot. Records in memory have to have pointers for look-up.)
  5. Compression is not supported.
  6. Range indexes are expected to be available in CTP2.

Recommendations

You likely will not want to put everything in memory-optimized tables. In a normal OLTP database, the 80-20 rule is more like 95-5. If you put effort into moving the dozens of tables that make up just 5% of your database’s processing load, your gain is minimal. Target the tables that are used most heavily. Partition large tables into hot and cold. Keep the hot data in memory-optimized tables and everything else in disk-based tables.

There are reasons to move even the cold part of a large table into memory-optimized tables. Memory-optimized tables do not persist indexes, so depending on the number of indexes on your big tables, you could save significant storage by migrating. Weigh the cost of memory against the fully-realized cost of disk storage. It may tip toward disk-based today, but as memory prices continue to drop, reevaluate.

Transaction Log Performance

Evaluate the implications of pumping maybe ten times as many transactions through your system and consider upgrading the storage for the transaction log.

Instrumentation

Consider how your instrumentation will need to change if transactions complete in microseconds instead of seconds or milliseconds.

References

SQL Server 2014: A Closer Look at SQL Server Blog

Edgenet Gain Real-Time Access to Retail Product Data with In-Memory Technology at SQL Server Blog

Microsoft Announces SQL Server 2014 at Visual Studio Magazine

SQL Server 2014: What’s vNew for vNext by Thomas LaRock

SQL Server 2014! by James Serra

Rob Garrison

Author profile:

Rob Garrison is a Data Architect for Nike in Beaverton, Oregon. He has over twenty years of IT experience, as well as having had a number of articles published and speaking regularly at user groups and other events. His immediate family includes his wife, Karen, and his son Tanner. Outside of work, much of their time is spent in activities and service projects involving their church (http://pfcn.org).

Search for other articles by Rob Garrison

Rate this article:   Avg rating: from a total of 22 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: MySQL Memory Tables
Posted by: Anonymous (not signed in)
Posted on: Monday, July 08, 2013 at 7:09 AM
Message: So Microsoft finally implemented the MySQL Memory storage engine!

Subject: If only the licence cost was less…
Posted by: Ian (not signed in)
Posted on: Tuesday, July 09, 2013 at 3:00 AM
Message: At present a lot of systems use caching like memcache for two reasons:

a) Sql Server is not as fast as it should be on servers with lots of Ram

b) Sql servers costs too much on servers with lots of RAM

Sql server 2014 seems to go a long away to removing the first reason, but as you can’t get much RAM on a server without having a lot of CPUs, the licence costs will still drive people to more complex apps so as to be able to use a 3rd party caching system.

Subject: 100% of the tables
Posted by: Carl (not signed in)
Posted on: Tuesday, July 09, 2013 at 11:53 AM
Message: Interesting article!

I just don't see why you focus so strongly on 4 specific scenarios. If the cost of putting 100% off the tables in memory is very low why not doing it?

Subject: 100% of the tables
Posted by: Carl (not signed in)
Posted on: Tuesday, July 09, 2013 at 12:03 PM
Message: Interesting article!

I just don't see why you focus so strongly on 4 specific scenarios. If the cost of putting 100% off the tables in memory is very low why not doing it?

Subject: @Carl
Posted by: Monte (view profile)
Posted on: Saturday, July 13, 2013 at 1:24 PM
Message: Did you read the article before recommending putting all tables in memory? No identity, no foreign key, no unique constraint, and cannot add index. Don't get me wrong I like the speed. But I don't think you can call it a relational database if it does not support foreign keys.

Subject: @carl
Posted by: ndr (view profile)
Posted on: Thursday, August 22, 2013 at 2:37 PM
Message: You could probably get the memory "cheap", have you notice that for more than 64GB of RAM you must use Enterprise Edition (paying per core) and that will make it FAR from cheap!

Subject: @carl
Posted by: RGarrison (view profile)
Posted on: Thursday, October 17, 2013 at 9:52 AM
Message: Yes, putting all the tables in memory is an option.

The nice thing with Hekaton is that you aren't _forced_ to put them all in memory. Choice is good.

And yes, Monte, there are certainly trade-offs. Again, choice is good. If you have to have all those features that we're used to, then use disk-based tables. But for that table where performance is key, you have an option to make it significantly faster.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.