In-Memory OLTP in SQL Server 2014

Introduction

SQL Server 2014 provides memory-optimized database technologies for optimizing performance with OLTP workloads. Particularly, this presents memory-optimized tables regarding effective, contention-free data accessibility, as well as natively created Stored Procedures for efficient execution of business logic.

For enabling the database for In-Memory OLTP you require a database. Let's create a database. If you have an existing database then skip this step.

Create Database

CREATE DATABASE sharad
GO

If you want to create a database with SSMS then use the following procedure:

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.
   
   
   
   
   

Enable your database for In-Memory OLTP

We're going to provide a filegroup regarding "memory_optimized_data" to the database, along with a container to this filegroup. This kind of filegroup will probably be used to assure durability of memory-resident data in the event of a new server crash as well as restart. During the crash recovery phase in server startup, the data can be restored using this filegroup and loaded back into memory space.

When creating your container inside the memory_optimized_data filegroup you will need to specify your storage location. During this example we have a tendency to pick the folder "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\imoltp_mod1". confirm the folder exists before running the script.

You can enable In-Memory OLTP by executing the following query.

First Query

ALTER DATABASE sharad ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
GO

After executing the First Query, execute the second query given below.

Second Query

ALTER DATABASE sharad ADD FILE (name='imoltp_mod1', filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\imoltp_mod1') TO FILEGROUP imoltp_mod
GO

And if you want to enable your database for In-Memory OLTP with SSMS then
 

add a memory_optimized_data filegroup and its container as in the following:

1. In Object Explorer, expand the "Databases" node, right-click your database and then click "Properties".
   
   
    

2. To add a new memory optimized data filegroup, click the Filegroups page. Under MEMORY OPTIMIZED DATA, click "Add filegroup" and then enter the values for the filegroup.
   
   
    

3. To add a file to the filegroup, click the General page. Under Database files, click "Add" and then enter the values for the file. Use file type FILESTREAM Data.
   
   
   

Create your first memory-optimized table
 

We are currently able to produce our own very first memory-optimized tables. We now have the following two tables, "test", in addition to "UserSession". "test" is a durable table (the default), that implies that its contents are persisted on disk and cannot be lost on a server crash. "UserSession" is a non-durable table (DURABILITY=SCHEMA_ONLY), meaning that the particular contents with the table can be found simply within memory, and therefore are lost on server restart.

Observe that throughout CTP1 memory-optimized tables support solely "nonclustered hash" indexes.


Create memory optimized table using query

For table "test":
 

USE sharad

GO

 

CREATE TABLE test (

   Id int not null primary key nonclustered hash with (bucket_count=2000000),

   Name varchar(100),

   Salary varchar(100)

)

WITH (MEMORY_OPTIMIZED=ON)

GO

 

For table "UserSession":

 

CREATE TABLE UserSession (

   SessionId int not null primary key nonclustered hash with (bucket_count=400000),

   Id int not null,

   index ix_Id nonclustered hash (Id) with (bucket_count=400000)

)

WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)

GO

 

Create memory optimized table using SSMS

Use the following procedure:

1. In Object Explorer, right-click the Tables node of your database, click "New", and then click "Memory Optimized Table". A template for creating a memory-optimized table is displayed.
   
   
    

2. To replace the template parameters, click "Specify Values for Template Parameters" on the Query menu. The shortcut key is "Ctrl-Shift-M".
   
   
   

Insert or load your Data

INSERT dbo.UserSession VALUES (1,100)

INSERT dbo.UserSession VALUES (2,200)

INSERT dbo.UserSession VALUES (3,700)

INSERT dbo.UserSession VALUES (4,900)

INSERT dbo.UserSession VALUES (5,466)

INSERT dbo.UserSession VALUES (6,769)

 

 

INSERT test VALUES (1,'sharad',3000)

INSERT test VALUES (2,'nitin',4500)

INSERT test VALUES (3,'rahul',5000)

INSERT test VALUES (43,'vinod',6544)

GO

Update statistics

Memory-optimized tables don't support auto_update_statistics, therefore studies will likely need to possibly be up-to-date manually. You may use UPDATE STATISTICS to update statistics for individual tables, or sp_updatestats for all tables within the database.

The following is a query to update the statistics of memory optimized tables:

UPDATE STATISTICS dbo.UserSession WITH FULLSCAN, NORECOMPUTE
UPDATE STATISTICS dbo.test WITH FULLSCAN, NORECOMPUTE
GO

Create natively compiled Stored Procedures

To optimize the access to memory-optimized tables, in order to optimize delivery of one's business logic, you'll be able to build natively compiled Stored Procedures. Even though these procedures usually are written making use of Transact-SQL, they don't help the entire Transact-SQL area. Here is a good example of a new natively compiled Stored Procedure that will accesses the tables we created previously.

CREATE PROCEDURE dbo.usp_AssignCart @SessionId int

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC

WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

 

  DECLARE @Id int,

    @Ids int

 

  SELECT @Id=Id, @Ids=Id

  FROM dbo.UserSession WHERE SessionId=@SessionId

 

  IF @Id IS NULL

    THROW 51000, 'The session or shopping cart does not exist.', 1

 

  UPDATE dbo.UserSession SET Id=@Ids WHERE SessionId=@SessionId

END

GO

Ref-http://blogs.technet.com