MEMORY OPTIMIZED TABLE

MEMORY OPTIMIZED TABLE

Today I was work with “MEMORY OPTIMIZED TABLE” SQL Server 2014 as R&D.This is very interesting feature in SQL Server 2014. According to Microsoft,
 

• MEMORY OPTIMIZED TABLES located in Memory above table contains data written to memory and read from memory. But additional copy(second copy) of the table data is maintained on disk(special file group call “MEMORY_OPTIMIZED_DATA”), but only for durability purposes.

• Data Written and Read much more faster.(First time data load from disk bit slow when you have normal table)

• This table types more likely normal table apart from some limitation

• This will support limited data types

• You can not use Truncate command
• Msg 10794, Level 16, State 92, Line 155
  The statement 'TRUNCATE TABLE' is not supported with memory optimized tables.

• If “DURABILITY=SCHEMA_AND_DATA” then maintained additional copy or If “DURABILITY=SCHEMA_ONLY” then it wont keep data in disk.

• Some table functionality wont supports this, like FK(more info you can find above Microsoft link)

First need to create database with file group(MEMORY_OPTIMIZED_DATA)

IF NOT EXISTS(SELECT 1 FROM sys.sysdatabases WHERE (name='DATABASEUSERGROUP')) BEGIN
    CREATE DATABASE [DATABASEUSERGROUP]
     CONTAINMENT = NONE
     ON  PRIMARY
    ( NAME = N'DATABASEUSERGROUP', FILENAME = N'E:\MSSQL\2014\MSSQL12.MSSQL2014\MSSQL\DATA\DATABASEUSERGROUP.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ),
     FILEGROUP [MEM_OPZ_FG] CONTAINS MEMORY_OPTIMIZED_DATA
    ( NAME = N'DATABASEUSERGROUP_MOF', FILENAME = N'E:\MSSQL\2014\MSSQL12.MSSQL2014\MSSQL\DATA\DATABASEUSERGROUP_MOF.ndf' )
     LOG ON
    ( NAME = N'DATABASEUSERGROUP_log', FILENAME = N'E:\MSSQL\2014\MSSQL12.MSSQL2014\MSSQL\DATA\DATABASEUSERGROUP_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%);
END
GO

After create database, next we will create OM tables with normal table.

USE DATABASEUSERGROUP
GO
CREATE TABLE MO_SD_tbl(col1 int not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000),
                    col2 varchar(25),
                    col3 datetime,
                    col4 bit)
WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA);
GO
CREATE TABLE MO_S_tbl(col1 int not null PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000),
                    col2 varchar(25),
                    col3 datetime,
                    col4 bit)
WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_ONLY);
GO
CREATE TABLE Default_tbl(col1 int not null PRIMARY KEY NONCLUSTERED,
                    col2 varchar(25),
                    col3 datetime,
                    col4 bit);
GO
SELECT name,type_desc,is_memory_optimized,[durability],[durability_desc] FROM sys.tables WHERE name IN('MO_SD_tbl','MO_S_tbl','Default_tbl');
GO

OK! Lets start, first i have inserted above three tables 1M records with loop. lets check execution statistics of following table inserts,
 

--Insert data MO_SD_tbl
SET NOCOUNT ON
DECLARE @i int = 1000000

BEGIN TRAN
WHILE @i > 0  BEGIN
    INSERT INTO MO_SD_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
    SET @i -= 1
END
COMMIT TRAN
SET NOCOUNT OFF
--Time 7 Seconds
GO


--Insert data MO_S_tbl
SET NOCOUNT ON
DECLARE @i int = 1000000

BEGIN TRAN
WHILE @i > 0  BEGIN
    INSERT INTO MO_S_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
    SET @i -= 1
END
COMMIT TRAN

SET NOCOUNT OFF
--Time 5 Seconds
GO


--Insert data MO_S_tbl
SET NOCOUNT ON
DECLARE @i int = 1000000

BEGIN TRAN
WHILE @i > 0  BEGIN
    INSERT INTO Default_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
    SET @i -= 1
END
COMMIT TRAN

SET NOCOUNT OFF
--Time 17 Seconds
GO

well... well... now you can get some idea "MEMORY OPTIMIZED TABLES" much more fast than normal tables.

SELECT COUNT(*) FROM Default_tbl;
SELECT COUNT(*) FROM MO_SD_tbl;
SELECT COUNT(*) FROM MO_S_tbl;
GO 

SELECT  * FROM Default_tbl;
SELECT  * FROM MO_SD_tbl;
SELECT  * FROM MO_S_tbl;
GO
DELETE FROM Default_tbl;--11 Seconds
GO 
DELETE FROM MO_SD_tbl;--less than 1 Seconds
GO
DELETE FROM MO_S_tbl;--less than 1 Seconds
GO

For "MEMORY OPTIMIZED TABLES" SQL Server 2014 newly introduce natively compiled stored procedure.which is significant fast method to insert,update or Delete.

-- natively compiled stored procedure for MO_SD_tbl Table
 CREATE PROCEDURE dbo.proc_MO_SD_tbl
 WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
 AS
 BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   
    DECLARE @i int = 1000000
   
    WHILE @i > 0  BEGIN
        INSERT INTO dbo.MO_SD_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
        SET @i -= 1
    END    
 END
 GO

 -- natively compiled stored procedure for MO_S_tbl Table
 CREATE PROCEDURE dbo.proc_MO_S_tbl  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER  AS
 BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
   
    DECLARE @i int = 1000000
   
    WHILE @i > 0  BEGIN
        INSERT INTO dbo.MO_S_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
        SET @i -= 1
    END    
 END
 GO

  -- compiled stored procedure for Default_tbl Table
 CREATE PROCEDURE dbo.proc_Default_tbl
 AS
 BEGIN    
    SET NOCOUNT ON
    DECLARE @i int = 1000000
    BEGIN TRAN
    WHILE @i > 0  BEGIN
        INSERT INTO dbo.Default_tbl VALUES (@i, 'varchar('+CAST(@i AS varchar(25))+')' ,SYSDATETIME() , 1);
        SET @i -= 1
    END    
    COMMIT TRAN
 END
 GO

 EXEC proc_MO_S_tbl --1 Seconds
 GO
 EXEC proc_MO_SD_tbl--1 Seconds
 GO
 EXEC proc_Default_tbl--15 Seconds
 GO

Hope you have get some idea for natively compiled stored procedure(according to Microsoft this is compile as C++)

• After CHECKPOINT above memory optimized tables wont be any issues(Data wont removed) 
• If you restart your SQL Server service then memory optimized tables which is categorizes under "SCHEMA_ONLY" will remove data others remain as same.
• These tables not like "#" or "Declare @T Table" you can access any sessions(two diffrnt SPIDs).
• I haven't tested this, but backups also work as "DURABILITY" option. 

Cheers...
and Most welcome your comments and ideas...