In Memory Optimized Table in SQL Server 2014

To increase performance of highly accessible tables, a new feature incorporated in Sql Server 2014 “In-Memory OLTP”.You can define a table that is accessed by large no of users at a time as “memory optimized”. Memory-optimized-tables are fully transactional, durable, and are accessed using T-SQL in the same way as disk-based tables.

In memory optimization is designed for extremely high session concurrency for OLTP type of transactions using latch-free data structures and optimistic, multi-version concurrency control.

Example:

-- Creating disk-based table.

CREATE TABLE [dbo].[PersonSimple] (

  ID INT NOT NULL PRIMARY KEY,

  Name NCHAR(48) NOT NULL

)

GO

-- Creating memory-optimized table durable.

CREATE TABLE [dbo].[PersonMemOptimized] (

  ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

  Name NCHAR(48) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

GO

BUCKET_COUNT:

BUCKET_COUNT parameter is mandatory when you create the memory-optimized table. If you cannot determine the correct bucket count, use a nonclustered index instead. An incorrect BUCKET_COUNT value, especially one that is too low, can significantly impact workload performance, as well as recovery time of the database. It is better to overestimate the bucket count.

DURABILITY parameter defines table schema or table data with schema to be persisted when database is restarted.

-- Creating memory-optimized table non durable.

CREATE TABLE [NonDurablePersonMemOptimized]

(

ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

Name NCHAR(48) NOT NULL

)WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)

GO

If our database does not contain MEMORY_OPTIMIZED_DATA filegroup ,below error occurred on IMO table creation

Msg 41337, Level 16, State 100, Line 7

Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup.

You can only create one memory-optimized file group per database. You need to explicitly mark the filegroup as containing memory_optimized_data.

ALTER DATABASE [ss2014Test] ADD FILEGROUP ss2014TestIMO CONTAINS MEMORY_OPTIMIZED_DATA

You need to add one or more containers to the MEMORY_OPTIMIZED_DATA filegroup. As

ALTER DATABASE [ss2014Test] ADD FILE (name='ss2014TestIMO', filename='c:\IMOfg\ss2014TestIMO') TO FILEGROUP ss2014TestIMO

CREATE TABLE [dbo].[PersonMemOptimized] (

  ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),

  Name NCHAR(48) NOT NULL

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA);

GO

Now query works.

Important Limitations of IMO Filegroup:

·         Once you create a memory-optimized filegroup, you can only remove it by dropping the database. In a production environment, it is unlikely that you will need to remove the memory-optimized filegroup.

·         You cannot drop a non-empty container or move data and delta file pairs to another container in the memory-optimized filegroup.

·         You cannot specify MAXSIZE for the container.