Technical: Microsoft – SQL Server – In-Memory Databases (Heckaton)
Lately, the noise around in-memory databases seem to be getting a bit louder.
In 2005, Oracle purchased TimesTen.
And, in the last couple of years, SAP, Oracle’s major competitor in the Enterprise Application’s Software space, has being making quite a bit of noise around HANA.
In 2007, IBM purchased Solid and acquired its in-memory DB (SolidDB).
What is Microsoft’s response:
Microsoft seems to be working very hard and making significant head-way with Heakaton.
Timeline of Microsoft’s response:
- 2012-Apr-12 (David Campbell)
The coming -in-memory Database Tipping Point
- 2012-Nov-08 (David Campbell)
Breakthrough performance with in-memory database
- 2012-Dec-20 (Janie Chang)
Hekaton Breaks Through
- 2013-Jun-04 (Sunil Agarwal [MSFT] & Rick Kutshera [BWIN])
“Hekaton” is the in-memory OLTP engine for SQL Server, as a part of XVelocity. In this overview session, we discuss the user scenarios and the hardware trends such as decreasing memory cost, many-core processors, and stalling CPU clock rate that prompted the key architectural pillars of memory optimization, no lock/latch concurrency control, and native compilation. We also cover how “Hekaton” is integrated into SQL Server.
- The current storage engine is susceptible to quite a bit of Log Trashing, Locks and Latches overhead and contention
- No-SQL databases are exposing this complexity and forcing traditionally database vendors and engineers to revisit the entire storage engine and tear everything down
Overview of Changes
- Administratively, you can designate that some tables should completely fit in memory
- With traditional databases, data on disk is the primary truth of data
- As the primary key of data is what it is on disk, there are many things that are done to maintain and that data on disk is safeguarded and that only one session can change that data; other users are kept away through locks and latches
- Heavy use of data compression and reducing need to read data from disk
- Judicious use of Columnar based Storage – Columnar Indexes
- A bit more aggressive tasking of multi-core CPUs –> Queries that target a few columns can be gated to various cores knowing that the data is stored in columns; we can have CPU cores work on distinct columns
- Indexes are not persisted – They are built-on the fly
- New natively compiled Stored Procedure
- Note that natively compiled Stored Procedure can only target memory optimized tables
- Microsoft is expected to provide a tool-set for identifying tables that should be considered for in-memory setting
- That choice is expected to be based on how popular they are, their sizes, and how much they are being affected by latch hits
Constraints & Monitoring
- For a table to be eligible for Memroy Optimnized, it must completely fit into memory
- Note that if the table grows to the extent that it can no longer fit into memory, you can expect your instance to crash
- So, please monitor the size of your memory optimized tables
- I will expect that Microsoft will provide tooling to segregate memory sizing for in-memory tables. This will potentially ensure that traditional memory pools do not encroach on memory that would otherwise be available for in-memory tables
Commonality – Interface
- Most of the in-memory Databases provides JDBC Drivers to allow clients to communicate and issue queries.
- For Oracle TimesTen, Oracle provides a JDBC Driver known as com.timesten.jdbc.TimesTenDriver. Sample JDBC Applications are available @ http://download.oracle.com/otn_hosted_doc/timesten/1121/quickstart/html/developer/jdbc/jdbc.html
- For IBM SolidDB, IBM provides a JDBC Driver known as solid.jdbc.SolidDriver. Sample JDBC Applications are available @ http://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com.ibm.swg.im.soliddb.programmer.doc/doc/s0005092.code.examples.html
- Kalen Delaney – Hekaton whitepaper