In- Memory OLTP New Feature of SQL Server 2014

In- Memory OLTP New Feature of SQL Server 2014

In-Memory OLTP includes memory-optimized tables and table types as well as native compilation of Transact-SQL stored procedures for efficient access to these tables.

How this feature improves performance:-

  • v Optimized Algorithms help in retrieving memory-resident data.
  • v Optimistic concurrency control which eliminates logical locks.
  • v Stored procedures that are Natively Compiled, significantly improve performance than interpreted stored procedure when accessing a memory-optimized table.

Limitation of In-Memory OLTP

  • v We cannot alter the In-Memory optimized table.
  • v No Locks are allowed in Memory Table.
  • v No LOB types are allowed in this feature.
  • v No constraints support.
  • v No Identity columns supported by this feature.
  • v Clustered Index not supported by Memory-OLTP.

How to Maintain Data

  • v As we can’t alter the In-Memory optimized table, so we have to Drop and recreate the Table.
  • v Only Primary key constraints allowed.
  • v This feature supports two type of Indexes :-
    • Hash Index
    • Non-Clustered Index 

Syntax of Creating a In-Memory Tables :-

— create database  —

CREATE DATABASE  [In-MemoryTest ]
GO
SSMS: To create a database,
1.    In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2.    Right-click Databases, click New Database and then enter the value for the database name.

Step 1: Enable database for in-memory OLTP

ALTER DATABASE In-MemoryTest ADD FILEGROUP In-MemoryTest _mod CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE In-MemoryTest ADD FILE (name=’ In-MemoryTest _mod1′, filename=’c:\data\ In-MemoryTest _mod1′) TO FILEGROUP In-MemoryTest _mod
GO

SSMS: To add a memory_optimized_data filegroup and its container,
1.    In Object Explorer, expand the Databases and then right-click your database and click Properties.
2.    To add a new memory optimized filegroup click the Filegroups page and under MEMORY OPTIMIZED   DATA click Add filegroup and then enter the values for the filegroup.
3.   To add file to the filegroup please click the General page. Under the Database files, click Add and then enter the values for that file. Use file type FILESTREAM Data.

Step 2: Create first memory-optimized table

— create memory optimized tables
USE In-MemoryTest
GO
— durable table – contents of this table will not be lost on a server crash
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId int not null primary key nonclustered hash with (bucket_count=2000000),
UserId int not null index ix_UserId nonclustered hash with (bucket_count=1000000),
CreatedDate datetime2 not null,
TotalPrice money
)
WITH (MEMORY_OPTIMIZED=ON)
GO
— non-durable table – contents of this table are lost on a server restart
CREATE TABLE dbo.UserSession (
SessionId int not null primary key nonclustered hash with (bucket_count=400000),
UserId int not null,
CreatedDate datetime2 not null,
ShoppingCartId int,
index ix_UserId nonclustered hash (UserId) with (bucket_count=400000)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
GO

Step 3: load your data

— Now insert few rows .
INSERT dbo.UserSession VALUES (1,342,GETUTCDATE(),4)
INSERT dbo.UserSession VALUES (2,65,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (3,8798,GETUTCDATE(),1)
INSERT dbo.UserSession VALUES (4,80,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (5,4321,GETUTCDATE(),NULL)
INSERT dbo.UserSession VALUES (6,8578,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (1,8798,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (2,23,GETUTCDATE(),45.4)
INSERT dbo.ShoppingCart VALUES (3,80,GETUTCDATE(),NULL)
INSERT dbo.ShoppingCart VALUES (4,342,GETUTCDATE(),65.4)
GO
— Verify the table contents
SELECT * FROM dbo.UserSession
SELECT * FROM dbo.ShoppingCart
GO
— Natively compiled stored procedure for inserting a large number of rows —
—   this demonstrates the performance of native procs —
CREATE PROCEDURE dbo.usp_InsertSampleCarts @StartId int, @InsertCnt int
WITH NATIVE_COMPILATION,  EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)
DECLARE @ShoppingCartId int = @StartId
WHILE @ShoppingCartId < @StartId + @ InsertCnt
BEGIN
INSERT INTO dbo.ShoppingCart VALUES
(@ShoppingCartId, 1, ‘2014-01-01T0 0:00:00’, NULL)
SET @ShoppingCartId += 1
END
END
GO

— insert 1,000,000 rows
DECLARE @StartId int = (SELECT MAX(ShoppingCartId)+1 FROM dbo.ShoppingCart)
EXEC usp_InsertSampleCarts @StartId, 1000000
GO

— Verify the rows have been inserted
SELECT COUNT(*) FROM dbo.ShoppingCart
GO

CONCLUSION :- So conclusion is that “In-Memory OLTP” is optimized database engine for OLTP application.

Tags:
  • In-Memory OLTP,
  • OLTP database application,
  • OLTP SQL Server 2014,
  • optimized for OLTP,
  • SQL Server engine,
  • Leave a Reply