Introduction
In SQL 2014 comes with some amazing feature that we must discuss in my blog post. One of the interesting features is In Memory OLTP (Memory Optimizer Table).
In this article we are trying to learn about it. Hope it will be interesting and informative.
What is it?
Microsoft is releasing SQL Server 2014 with a new set of In-Memory OLTP features to significantly improve the OLTP performance and reduce the processing time for servers with a large amount of memory and multi-core processors.
In Memory OLTP is a new technology introduced by Microsoft to reduce the work load of OLTP and provide the high improvement of processing. The performance gains also depends on the hardware we have and how we writing our query. By this technology we are taking the full advantage of modern hardware trends and modern application requirement.
SQL Server is generally designed for saving data in the disk and loading the data into the memory to serve the request made by the Query. This done for resource management as it is quite expensive.
Nowadays the cost of the hardware is quite low and we can manage a server with huge REM and multi core and the new feature of Microsoft called In Memory OLTP works on it.
The In-memory OLTP features are a new database engine component, which is fully integrated into SQL Server and runs side by side with the traditional database engine. It allows us to declare a table to be stored in main memory so that your OLTP workload can access this memory resident data faster.
In the memory optimized table all the data is stored in the memory not in Disk.
Type of Memory Optimize table
There are two types of memory optimize table
1. SCHEMA_AND_DATA
2. SCHEMA_ONLY
SCHEMA_AND_DATA :
The SCHEMA_AND_DATA Memory-Optimized table is a table that resides in memory where the data is available after a server crash, a shutdown or a restart of SQL Server
Usages: A point of sales transaction data might be a good fit for a SCHEMA_AND_DATA type table. We might want our point of sales transactions to run as fast as possible so the memory-optimize type table would provide this, but we wouldn’t want to lose those transactions should our server be restarted.
SCHEMA_ONLY
SCHEMA_ONLY Memory-Optimized table is a table that does not persist data should SQL Server crash, or the instance is stopped or restarted. The SCHEMA_ONLY Memory-Optimized tables do retain their table structure should the server crash, or be shutdown.
Usages: SCHEMA_ONLY table would be useful for a staging table in a data warehouse application. Typically it is fairly easily to reload a data warehouse staging table from its data source. This is why making these type of tables a SCHEMA_ONLY type table is relatively safe.
Maintaining Version in Rows
Rows in memory-optimized tables are versioned. 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.
.
How we Crate it?
To create Memory-Optimized table we are just following our step by step process to Understand it clearly.
Step-1 [ Create Database to Support Memory Optimize Table ]
First we have to create a database to support Memory-Optimized Table. If needed, we can alter our existing database also.
IF EXISTS (SELECT *
FROM sys.databases
WHERE name = N'InMemoryExample'
)
DROP DATABASE InMemoryExample;
GO
CREATE DATABASE InMemoryExample
ON PRIMARY
(NAME = InMemory_Data,
FILENAME = N'C:\data\InMemoryExample_Data.mdf',
SIZE = 100MB,
FILEGROWTH = 10MB),
FILEGROUP InMemoryExample_InMemory CONTAINSMEMORY_OPTIMIZED_DATA
( NAME = InMemory_InMemory,
FILENAME = N'C:\data\InMemoryExample_InMemory.mdf')
LOG ON
( NAME = InMemoryExample_Log,
FILENAME = N'C:\data\InMemoryExample_Log.ldf',
SIZE = 100MB,
FILEGROWTH = 10MB)
GO
The script look likes same. Please review the script carefully, the only differences that we find is
FILEGROUP InMemoryExample_InMemory CONTAINSMEMORY_OPTIMIZED_DATA
( NAME = InMemory_InMemory,
FILENAME = N'C:\data\InMemoryExample_InMemory.mdf')
We created a FILEGROUP named “InMemoryExample_InMemory” that will be used to support our Memory-Optimized tables. This file group contains a single file. Without this “MEMORY_OPTIMIZED_DATA” file group I wouldn’t be able to create a Memory-Optimized table in our new database.
Step-2 [ Creating Memory Optimized Table ]
Here we are going to create two types of memory optimized table.
SCHEMA_AND_DATA table:
IF OBJECT_ID('MemoryOptimized_Schema_And_Data','U') IS NOTNULL
DROP TABLE MemoryOptimized_Schema_And_Data
GO
CREATE TABLE MemoryOptimized_Schema_And_Data
(
Id INT NOT NULL,
Col1 CHAR(1000) NOT NULL,
CONSTRAINT PK_MemoryOptimized_Schema_And_Data
PRIMARY KEY NONCLUSTERED HASH (Id)
WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA);
SCHEMA_ONLY Table:
IF OBJECT_ID('MemoryOptimized_Schema_Only','U') IS NOT NULL
DROP TABLE MemoryOptimized_Schema_Only
GO
CREATE TABLE MemoryOptimized_Schema_Only
(
Id INT NOT NULL,
Col1 CHAR(1000) NOT NULL,
CONSTRAINT PK_MemoryOptimized_Schema_Only
PRIMARY KEY NONCLUSTERED HASH (Id)
WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_ONLY);
Now we have to understand it.
Please look at the part of the script carefully.
CONSTRAINT PK_MemoryOptimized_Schema_Only
PRIMARY KEY NONCLUSTERED HASH (Id)
WITH (BUCKET_COUNT = 1024)
Here we are creating NON CLAUSTERED HASH Index on columns ID. The memory optimized table needs the HASH index It cannot be more than the 8. With CTP1 only columns with Windows BIN2 collation types can be used for a HASH index. Therefore on our table we are just create a HASH index on the INT column and not the char column.
Second thing is BUCKET_COUNT. The BUCKET_COUNT identifies the number of different buckets SQL Server will create in memory to store my Memory-Optimized table records. Each bucket is identified by the value created when hashing the index column. Each unique index key value that has the same hash value will be stored in the same bucket. Therefore it is recommended that we create a bucket value that is equal to or greater than the number of unique key values we expect for your Memory-Optimized table.
Memory-Optimized tables only support the following data types: bit, tinyint, smallint, int, bigint, money, smallmoney, float, real, datetime, smalldatetime, datetime2, date, time, numberic, decimal, char(n), varchar(n) ,nchar(n), nvarchar(n), sysname, binary(n), varbinary(n), and Uniqueidentifier. Notice that none of the large Binary Object data types are allowed, even the variable character “max” data types. Something worth also mentioning is the combined record length must not exceed 8060. This record length limitation will be enforced at the time we create our table.
Step-3 [ Inserting data into Memory-Optimized Table ]
SET NOCOUNT ON;
USE InMemoryExample;
GO
DELETE FROM MemoryOptimized_Schema_And_Data;
DELETE FROM MemoryOptimized_Schema_Only;
SET STATISTICS IO Off;
SET STATISTICS TIME Off;
DECLARE @s datetime = getdate()
-- Load Normal Table
DECLARE @I int = 0;
WHILE @I < 1000
BEGIN
SET @I+=1;
INSERT INTO Normal(Id,C1)
VALUES (@i,cast(@I as varchar(4)) + 'A');
VALUES (@i,cast(@I as varchar(4)) + 'A');
END;
SELECT DATEDIFF(ms,@s,getdate()) as Normal;
-- Load SchemaAnadData table --
SET @s = getdate();
SET @I = 0;
WHILE @I < 1000
BEGIN
SET @I+=1;
INSERT INTO MemoryOptimized_Schema_And_Data
(Id, Col1)
VALUES (@i,cast(@I as varchar(4)) + 'A');
END;
SELECT DATEDIFF(ms,@s,getdate()) as SchemaAndData;
-- Load SchemaOnly table
SET @s = getdate();
SET @I = 0;
WHILE @I < 1000
BEGIN
SET @I+=1;
INSERT INTO MemoryOptimized_Schema_Only
(Id, Col1)
VALUES (@i,cast(@I as varchar(4)) + 'A');
END;
SELECT DATEDIFF(ms,@s,getdate()) As SchemaOnly;
GO
Some limitation
- NO TRUNCATE TABLE statement against my Memory-Optimized tables.
- NO ALTER TABLE statement against my Memory-Optimized tables.
- NO UPDATE of primary key columns of my Memory-Optimized tables.
- NO FOREIGN KEY or CHECK constraints.
- NO UNIQUE constraints other than the PRIMARY KEY.
