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
CONTAINS MEMORY_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
CONTAINS MEMORY_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 NOT NULL
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.
Hope you like
it.
Posted by: MR. JOYDEEP DAS
