SQL Server In-Memory

Hi to all of you SQL Server fans,

There has been a lot of interest from customers moving to SQL Server 2014, specifically around the use of SQL Server in-memory optimizations as well as identifying possible candidates that could benefit from this technology.

I thought I would write a quick post to cover the implementation of SQL Server in-memory at a very high level, by discussing different DURABILITY modes available to you.

First off, let's make use of AdventureWorks 2014 as our test database.

There are a few steps we need to follow first, in order for SQL to make use of the in-memory capabilities:

Firstly, we need to enable the filestream feature on the SQL Engine

[caption id="attachment_35" align="alignnone" width="879"] SQL Server Configuration Manager[/caption]

On the properties page, enable FILESTREAM via the FILESTREAM TAB:

[caption id="attachment_37" align="alignnone" width="251"] Enable FILESTREAM[/caption]

Now that the SQL Engine has been configured correctly, lets enable a Memory Optimized Filegroup in the AdventureWorks2014 database.

There are two ways to do this, via the SSMS GUI on Database properties:

 

 

 

 

 

 

 

 

Enable the Filegroup in MEMORY OPTIMIZED DATA section of your FILEGROUPS tab:

 

 

 

 

 

 

 

 

Or via T-SQL Script:

USE [master] GO ALTER DATABASE [AdventureWorks2014] ADD FILEGROUP [AdventureWorksInMemoryData] CONTAINS MEMORY_OPTIMIZED_DATA GO

Now that that the filegroup has been configured, you are now able to create tables in memory:

In-Memory tables can be created with one of two durability options, namely:

• SCHEMA AND DATA
  • These tables have logging enabled via the sql server transaction log and data is stored in the filestream filegroup.
  • In the event of SQL server being restarted, data is played back into the table on start-up
• SCHEMA ONLY
  • These tables do not persist the data to the filestream filegroup.
  • If the SQL Engine is rebooted, the data is lost
  • This option is popular for staging type of scenarios of data.

Tables can only be created via T-SQL script:

SCHEMA AND DATA:

CREATE TABLE [dbo].[TestInMemoryTableSCHEMA_AND_DATA] ( [ID] [int] IDENTITY(1,1) NOT NULL, [DATAVALUE] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

 

or the SCHEMA ONLY option:

 

CREATE TABLE [dbo].[TestInMemoryTableSCHEMA_ONLY] ( [ID] [int] IDENTITY(1,1) NOT NULL, [DATAVALUE] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

 

Now that the tables have been created, you can read and write to them just like any other table in the database.

Try the different options and see what works best for you and see what performance improvements you notice with your reads and writes.

NOTE: With SQL Server 2014, you cannot change the definition of a memory optimized table once it has been created, you will need to drop and recreate the table in order to change the durability option. For more info on this refer to https://msdn.microsoft.com/en-us/library/dn269114(v=sql.120).aspx

There are also a few limitations to this technology, which you can read more about over at https://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx

Well, I hope this quick and high level post has helped you understand what is involved in making use of this SQL Server technology.

In my next few posts I will be diving deeper into the topic and helping you identify which databases tables or stored procedures would benefit from this technology as well as indexing (refered to as hash and buckets) these tables by making

Please post comments or questions below in order for my blog posts to suit your needs.

Comments

• Anonymous
  May 06, 2016
  Thanks Justin. Good blog.
• Anonymous
  May 10, 2016
  Justin,Well done on that first post. Looking forward to some great articles from you. Keep it up mate!