How to Create In-Memory Database Tables in SQL Server 2014

Getting data off disk drives and into RAM is the biggest game changer for relational databases in decades and SQL Server 2014 brings it to the masses.

RAM is cheap and it’s finally time to reap the benefits of 64-bit computing.

SQL Server In-Memory OLTP, also know at Hekaton is here and it’s ready to transform your business.  Unlike other recent entries to the in-memory database space, SQL Server 2014 integrates this new technology directly into the database engine instead of being a separate add-on. Additionally, existing SQL Server DBAs and developers will feel right at home building memory-optimized databases with same SQL Server Management Studio they’ve used for years.  Not having to retrain your staff is pretty cool.

Benefits to using SQL Server 2014 include:

  • In-memory execution for low-latency data retrieval vs. disk-bound I/O
  • Elimination of contention (locks, latches, spinlocks) from concurrent data Inserts and Updates due to optimistic concurrency control (row versioning without using TempDB)
  • Disk I/O reduction or elimination depending selected data durability (more on this later)
  • 5x – 25x performance improvement and the equivalent throughput of 5 – 10 scaled-out database servers

Create a Memory-Optimized Database

  • Create a normal database in SQL Server Management Studio

Create Database

  •  Add Memory Optimized Data Filegroup to ensure data durability

Memory Optimized Filegroup

  • Add a FILESTREAM Data file type with Unlimited Autogrowth/Maxsize

Filestream Data

Create Memory-Optimized Tables

  • Right-click on the Tables folder of the database you just created and select New | Memory Optimized Table… to get a starter SQL script
  • Create and execute CREATE TABLE scripts to create one or more tables where MEMORY_OPTIMIZED=ON (example in a sec)
  • Set DURABILITY=SCHEMA_ONLY for staging tables to prevent transaction logging and checkpoint I/O (this means only the schema but no actual data will be saved to disk in the event of a server restart)
  • Set DURABILITY=SCHEMA_AND_DATA for standard tables (this saves the schema and in-memory data to disk in the background with the option to delay durability for better performance by not immediately flushing transaction log writes)

Here’s an example of a SQL script to create a memory-optimized Customer table with an Id, FirstName and LastName column:

USE TechEDNZ2014
GO
CREATE TABLE [dbo].[Customer] (
    [Id] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) DEFAULT (NEWID()), 
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50)
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

Create Natively-Compiled Stored Procedures

Just when you thought performance couldn’t get any better, SQL Server 2014 rewrites the book on stored procedures.  Your T-SQL code now compiles to C DLLs which minimizes code execution time to further boost performance and scalability.  Furthermore, they significantly reduce CPU usage on your SQL Server box due to the need for fewer instructions to execute.

Here’s an example of a SQL script to create a natively-compiled stored procedure to retrieve data from the memory-optimized Customer table you just created:

USE TechEDNZ2014
GO
create procedure [dbo].[CustomerSelect]
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(
    transaction isolation level = snapshot, 
    language = N'English'
)
    SELECT [Id], [FirstName], [LastName] FROM [dbo].[Customer];
end
GO

I’m hoping by now you’re feeling the need for speed.

I’ve heard plenty of reports from companies that upgraded from previous versions of SQL Server to SQL Server 2014 that they instantly doubled their performance.  This is before converting disk-based tables to in-memory tables which is pretty incredible and well worth the upgrade on its own.  Just knowing that you can jump from a 2x performance increase to anywhere from 5x to 25x is mind boggling.

Most of you know me as a mobile strategist, architect and developer.  Being a mobile guy doesn’t mean I don’t think about the server.  In fact in all the large-scale enterprise mobile solutions I’ve designed for Fortune 500 companies, I figure I spend more than 70% of my time ensuring that servers are fast and can scale.  With SQL Server 2014 being the heart of most enterprise systems, just imagine how delighted all your mobile users will be when their apps become dramatically more responsive.


One thought on “How to Create In-Memory Database Tables in SQL Server 2014

Add yours

Leave a Reply

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑