In-Memory OLTP in SQL Server 2014

The development name for this new technology is Hekaton. It’s Greek and means 100 (hundred) which was the aim of performance improvement with this improved approach to managing SQL Server 2014 table operations. Actual performance improvements will depend on many factors and vary from client to client. Real world improvements in terms of performance are reported between 5x and 20x.

Microsoft Excel 2013 with PowerPivot pioneered this new and improved way for optimizing database operations with its VertiPak engine. A million row worksheet can be entirely loaded into memory (given sufficient memory is available) and can be manipulated in milliseconds in RAM. This innovation extends processing of very large datasets required by Business Intelligence to a division level solution.

What if I want my SQL Server 2014 data to be loaded into memory like Excel? In-Memory OLTP or Online-Transaction Processing is a database engine optimized specifically for OLTP operations and is an integral part of a SQL Server 2014 installation.

This engine is designed to deliver increased concurrency and has a new mechanism to control and eliminate delays due to locking with an optimistic concurrency control mechanism.

OLTP tables are copied into memory and are made durable by writing transaction logs to disk. The new engine has an improved lock-free mechanism which can process transactions for the In-Memory tables.

The performance of stored procedures is radically improved due to compiling them into DLL native code. Currently, stored procedures are being interpreted, an operation which adds a few clock ticks to their execution, but compiling them to native Win64 code, they are made to be directly executable and thus performance is maximized and their execution time is minimized.

To evaluate how the new In-Memory OLTP engine improves a database’s performance, Microsoft provides a new Analysis, Migrate and Report tool (AMR).

Sounds good, huh? But wait. The In-Memory OLTP engine does have some limitations. First of all, not all data types are supported for the memory-optimized tables.

Some of these data types are:

  • geography
  • hierarchyid
  • text
  • ntext
  • image
  • xml
  • varchar(max)

Several important SQL Server features cannot be combined with the In-Memory OLTP feature. These features include:

  • database mirroring
  • snapshots
  • triggers
  • clustered indexes
  • computed columns
  • identity columns
  • constraints such as: FOREIGN KEY, CHECK and UNIQUE
  • FILESTREAM storage

Operating Systems supporting this feature include Windows Server 2012 or Windows Server 2012 R2 and also Windows Server 2008 R2 SP2. You will also need to use either one of the Enterprise, Developer or Evaluation editions of SQL Server 2014 in order to have access to In-Memory OLTP.

For more Microsoft Technical Training information visit www.directionstraining.com or call 1-855-575-8900.