Select Page

Hekaton Helps Build Faster Databases In 3 Ways (Part 1)

Andy McDermid | | December 23, 2013

In SQLhekaton Server 2014 Microsoft introduces In-Memory-OLTP, also known as Hekaton. This technology allows the DBA to configure a specific table to reside in memory full time. Of course, if the data is always in memory we can count on increased throughput since we’ll never have to go to disk for that data.

But wait – if your server has enough RAM your instance may be running with a good part of the database already persistent in-memory, so you might be thinking; ‘Hekaton -what is the big deal’?

In fact, there is a lot more going on than what you’d see in an IMDB. In-Memory-OLTP is an entirely new SQL engine standing side-by-side with, and more-or-less seamlessly integrated with the ‘old’ standard engine. Rather than pinning a table in the buffer pool (no, it is not just a technology recycle of DBCC PINTABLE, see myth#2) and settling for the performance improvements associated with reduced disk IO, In-Memory-OLTP has, broadly, three ways help your database perform faster. To get an overview of the feature, and to see just where it can improve performance, let’s take a look at each of these facets and compare and contrast the new In-Memory to the traditional disk-based functionality.

LOG Writes
In a disk-based system when any changes made to a data page in the buffer pool (update, delete, etc.) SQL Server must carefully ensure the changes are durable in case of a system crash. It does this via Write-Ahead-Logging, which ensures that the changes are hardened to disk in the transaction log before the transaction is committed. Then the changed page is marked as dirty and the next checkpoint flushes it to disk.

There is a bit more to it though, since each change recorded in the log must also include a corresponding undo record in case the transaction rolls-back or the system crashes before the transaction commits. Furthermore, if the changed data has any associated indexes, those too must be updated and will create additional log records.

Depending on the disk sub-system where the transaction log resides we might just expect to see some WRITELOG and LOGBUFFER waits.

To understand how In-Memory-OLTP side-steps these potential issues, first things first, set aside your understanding of the buffer pool as a repository of logical 8KB data pages. There is no concept of data pages in In-Memory-OLTP and there is no buffer pool as a logical intermediary between the transaction and the disk. Rather we have rows addressed to physical memory and associated via a hash-index. Whereas in disk-based systems SQL Server uses the transaction log to ensure durability in the gap between a data page change and that page being persisted to disk, In-Memory-OLTP simply waits until the transaction commits and the row is changed, then records the change to the transaction log. In-Memory-OLTP maintains durability but there is no gap and therefore no need for undo information in the transaction log. As for the indexes, they do still get updated, but since In-Memory-OLTP recreates these on every database start up, it does not bother to record those changes to the transaction log.

Last, but not least, In-Memory-OLTP employs a new method to consolidate log records. Whereas a disk based system may log a record (at least) for each row changed, In-Memory-OLTP can consolidate many changes into a single log record.

Taken together this all results in some serious streamlining for transaction log writes. It’s still best practice to put your log records onto the fastest write disk available, SSD if possible. Especially since there is still only one transaction log per database (In-Memory-OLTP transactions share the log with disk-based transactions, they both write to the same old transaction log). But given a decent write speed, and an appropriate design for in-memory tables, we might not see those Log Wait stats around so much anymore.

Keep an eye out here for posts on two additional ways Hekaton helps build faster databases: by reducing latching, locking and blocking and by optimizing CPU time.
To learn more about In-Memory OLTP you can get a start reading Microsoft’s data platform SQL Blog here as well as MSDN here.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Oracle EPM Cloud Vs. On-Premises: What’s the Difference?

EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.

Bobby Ellis | April 10, 2018

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine there are over one hundred logins in the source server and you need to migrate them all over to the destination server. Wouldn’t it be awesome if we could automate the process by generating the scripts for the required tasks?

JP Chen | October 1, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS