Memory-Optimized tables

Problem: How to create a memory optimized table.

Solution: Memory-Optimized Tables

This Feature is introduced in SQL Server 2014 and above versions.

Memory-optimized tables are tables that read and write to memory, a copy of the table is saved on the disk when the file groups containers are located.

Memory-optimized tables also are fully available by default also can be set to live for a while, these tables can be called inside query’s, stored procedures.

Data inside the memory-optimized tables are stored as multiple versions for each row. This means that each row in the table potentially has multiple versions. All row versions are maintained in the same table data structure. Row versioning is used to allow concurrent reads and writes on the same row. 

These tables are also called In-Memory tables, so they have fast read and write access and also performance gains in following areas:

·         Latch Contention.

·         Spinlock Contention

·         Locking Related Contention

·         Memory-Optimized Hash Index

For creating a memory optimized table we have few requisites.

1.  Create memory optimized file group on the database
2.   Mark the file group explicitly to contain MEMORY_OPTIMIZED_DATA
3.  You need to add one or more containers to the MEMORY_OPTIMIZED_DATA filegroup.
4.  Similar to Create Table, extra syntax to specify

a.       WITH keyword

b.       MEMORY_OPTIMIZED=ON

c.       DURABILTY=SCHEMA_ONLY|SCHEMA_AND_DATA(non-durable|durable)

Example:

In this article I am going to show you how we can create a memory optimized file group, container and a memory optimized table.

Example 1:

Let’s create a memory optimized table and verify the tables exist.

/*

step 1:

--You can only create one memory-optimized filegroup per database.

Step 2:

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

*/

ALTER DATABASE DB ADD FILEGROUP DB_mod CONTAINS MEMORY_OPTIMIZED_DATA 

ALTER DATABASE DB ADD FILE (name='DB_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP DB_mod

-- creating a NON-DURABLE memory optimized table.

CREATE TABLE dbo.durablememtemp

(ColA INT NOT NULL PRIMARY KEY NONCLUSTERED ,

 ColB NVARCHAR(4000))

WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA);

GO

-- creating a DURABLE memory optimized table.

CREATE TABLE dbo.nondurablememtemp

(ColA INT NOT NULL  INDEX ix_1 ,

 ColB NVARCHAR(4000))

WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY);

-- viewing  all memory optimized tables created on the database.

select object_name(object_id) as objname,* from sys.dm_db_xtp_table_memory_stats

objname	object_id	memory_allocated_for_table_kb	memory_used_by_table_kb	memory_allocated_for_indexes_kb	memory_used_by_indexes_kb
memtemp	2007678200	0	0	128	0
durablememtemp	2039678314	0	0	128	0