Starting with SQL Server 2014, Microsoft introduced In Memory Tables also known as Memory Optimized Tables or In Memory OLTP. Memory optimized tables can take advantage of additional Memory (RAM) to provide better performance.
As name suggests, the data in these tables remain in memory when SQL Service is running. This does not mean that data will always vanish away when server is powered off or service interrupts. SQL Server keeps copy of data in Logs (write ahead log), if data has to be retained, the pages from log will be loaded back to memory optimized tables.
While defining the table you can specify whether the durability of the table will be Schema only or Schema and Data both.
Memory Optimized Table is brand new concept for SQL 2014 and the allocation works little differently from regular indices. Unlike regular tables, data in Memory Optimized table is stored in streams also called Data Streams. These Tables don't use IAM (Index Allocation Map) to link extent chain, because of this reason each Memory Optimized Table requires at least one index to link the data.
These are the requirements for Memory Optimized Tables are:
1. File Group designated for Memory Optimized Data,
2. Data Files in Memory Optimized File Group.
3. Disable Broker (or any feature that enables Event Notification) for that database. It is not supported.
4. At least one Index per Memory Optimized Table. Index can be Hash Index or Range Index. Hash Indexes use Buckets to organize and point the underlying data, and Range Indexes are the regular indexes using "BW"-Tree architecture.
You can either create a new Memory Optimized Database or you can add Memory Optimized Data file to existing database also.
Here is the small example to introduce with Memory Optimized OLTP/Table.
CREATE DATABASE [MYDB]
CONTAINMENT = NONE
SIZE = 4096KB,
MAXSIZE = 10240KB,
(NAME = 'LogFile',
IF EXISTS (SELECT * FROM SYS.DATABASES WHERE NAME = 'MYDB' AND IS_BROKER_ENABLED = 1)
ALTER DATABASE MYDB SET DISABLE_BROKER
Add Memory Optimized File Group and Data File
ALTER DATABASE MYDB
ADD FILEGROUP [MYDB_MEMORY_OPTIMIZED_GROUP] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE MYDB
) TO FILEGROUP [MYDB_MEMORY_OPTIMIZED_GROUP]
CREATE TABLE MYDB_INMEMORY_TABLE
COLUMN1 INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
COLUMN2 INT NOT NULL INDEX INDEX1 HASH WITH (BUCKET_COUNT=1000),
COLUMN3 CHAR(100) COLLATE LATIN1_GENERAL_100_BIN2 NOT NULL INDEX INDEX2
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
It is all done and ready to use. Though it is recommended to create resource pooling on memory for the database so that memory utilization can be maintained and monitored.
Create Resource Pool
IF NOT EXISTS(SELECT * FROM SYS.RESOURCE_GOVERNOR_RESOURCE_POOLS WHERE NAME = 'POOL_MYDB')
CREATE RESOURCE POOL POOL_MYDB WITH (MAX_MEMORY_PERCENT=10);
ALTER RESOURCE GOVERNOR RECONFIGURE;
Map Resource Pool and Database
EXEC sp_xtp_bind_db_resource_pool 'MYDB', 'POOL_MYDB';
Take Database Offline and Online again to apply the changes
ALTER DATABASE MYDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE MYDB SET ONLINE;
Please leave your feedback, if you like this article or have any comments, below.