- In memory OLTP Enhancement
Memory optimized tables speed up transnational workloads, high concurrency and too many latches by moving data from disk-based to memory optimized tables.
It helps to improve the performance of DW staging by using non-durable, memory-optimized tables as staging tables.
Features which Memory optimized tables support/not support
Support
|
Not Support
|
Check Constraints
|
Replication
|
Unique Constraints
|
Database Snapshots
|
Foreign Keys : As long as foreign key reference to a “primary key”
|
Remove “ON” clause from “create table statement”
|
Triggers: for insert/update/delete as long as it use “with native Compilation”
|
Datetimeoffset/ Hierachy id
|
Columns with large object types(LOB): Varchar(max), Nvarchar(max), Binary(max)
|
DBCC checkDB / DBCC checktable
|
Noncluster index: specify primary key nonclustered.
|
Clustered index
|
Unique/nullable index key columns
|
Create index: Must be specified with create statement or alter table statement.
|
Scaling Memory Optimized Tables
- Can put more date into memory optimized tables.
- Support bigger workloads.
- Can store upto 2TB of data when you create a durable table by using "Schema_and_data" option in the create table statement.
- Create memory optimized table with row size > 8060 bytes even without a LOB column
- Support parallel scans of all indexes (nonclustered + hash). It is beatifically when you have queries which scan large data sets.
Create Memory Optimized table.
Before creating memory optimized table you should complete below 2 steps.
- Create memory-optimized database file group
- Create filestream database file.
Note:
- You can only create one memory-optimized file group per database.
- You can create the file group when you create the database or you can add it later.
- Using SQL Query statements
- Using DB interface.
Ok then lets start.
Assumption: Lets assum that you have alreay created the Database.
Create in memory optimized file group
ALTER DATABASE DB NAME ADD FILEGROUP FILEGROUP NAME CONTAINS MEMORY_OPTIMIZED_DATA
Ex: ALTER DATABASE SQL2016 ADD FILEGROUP imoltp CONTAINS MEMORY_OPTIMIZED_DATA
Create filestream database file
ALTER DATABASE DB NAME ADD FILE (NAME='NAME OF THE DB FILE', FILENAME='PATH WHERE FILE IS STORED ') TO FILEGROUP FILEGROUP NAME
Ex: ALTER DATABASE SQL2016 ADD FILE (name='imoltp_mod1', filename='C:\Program Files (x86)\SQL2016_IMOLTP') TO FILEGROUP imoltp
CREATE TABLE ProductReviewHistory (
ProductID INT IDENTITY PRIMARY KEY NONCLUSTERED,
ProductEnglishDescription NVARCHAR(4000),
ProductReviewDate DATETIME,
ProductSpanishReviewComments NVARCHAR(4000),
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
2. Using DB Interface
Create in memory optimized file group
Create filestream database file
Now Crete in memory optimized table
CREATE TABLE ProductReviewHistory (
ProductID INT IDENTITY PRIMARY KEY NONCLUSTERED,
ProductEnglishDescription NVARCHAR(4000),
ProductReviewDate DATETIME,
ProductSpanishReviewComments NVARCHAR(4000),
) WITH (MEMORY_OPTIMIZED = ON);
Cheers :)
Thilini
