Technical: Microsoft – SQL Server – In-Memory Databases ( Heckaton )

Technical: Microsoft – SQL Server – In-Memory Databases (Heckaton)

Introduction

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:

“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.

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/DBI-B204#fbid=R4piOwzFqaH

Why ?

  • 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

Architectural\Design Changes

  • 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

Tooling

  • 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

Commonality – Interface

References

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s