SQL with Manoj

SQL Server 2014 provided you these new features to create [Memory Optimized Tables] and [Native Compiled Stored Procedures] for efficient and quick processing of data and queries which happens all in memory.

It also provided you one more feature to create Memory Optimized Table Variables, in addition to normal Disk Based Table Variables.

This new feature would provide you more efficiency in Storing, Retrieving and Querying temporary data from and in memory.

Normal Table Variables are created in tempdb and utilize it for their entire life. Now with these new Memory Optimized Table Variables they will become free from tempdb usage, relieve tempdb contention and reside in memory only till the scope i.e. batch of a SQL script or a Stored Procedure.

Let’s see how to use these and what performance gain you get out of these tables.
 

–> Enable Database for supporting Memory Optimized tables: To use this feature your Database should be associated with a FileGroup. So, let’s alter the database.

USE [TestManDB]
GO

-- Add the Database to a new FileGroup

ALTER DATABASE [TestManDB]
    ADD FILEGROUP [TestManFG] CONTAINS MEMORY_OPTIMIZED_DATA 
GO

ALTER DATABASE [TestManDB]
ADD FILE ( 
	NAME = TestManDBFG_file1,
    FILENAME = N'E:\MSSQL\DATA\TestManDBFG_file1' -- Put correct path here
)
TO FILEGROUP TestManFG
GO

Otherwise, while creating Memory Optimized objects you will get below error:

Msg 41337, Level 16, State 100, Line 1
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.
 

You cannot create a Memory Optimized Table Variable directly with DECLARE @TableVarName AS TABLE (…) statement. First you will need to create a Table Type, then based upon this you can create Tables Variables.

–> Create a Table TYPE [Person_in_mem]

CREATE TYPE dbo.Person_in_mem AS TABLE(
	 BusinessEntityID INT NOT NULL
	,FirstName NVARCHAR(50) NOT NULL
	,LastName NVARCHAR(50) NOT NULL

	INDEX [IX_BusinessEntityID] HASH (BusinessEntityID) 
		WITH ( BUCKET_COUNT = 2000)
)
WITH ( MEMORY_OPTIMIZED = ON )
GO

The Memory Optimized Table Type should have an Index, otherwise you will see an error as mentioned below:

Msg 41327, Level 16, State 7, Line 27
The memory optimized table ‘Person_in_mem’ must have at least one index or a primary key.
Msg 1750, Level 16, State 0, Line 27
Could not create constraint or index. See previous errors.
 

Ok, now as we’ve created this table type, now we can create as many Table Variables based upon this.

–> Now, create a Table variable @PersonInMem of type [Person_in_mem] that is created above:

DECLARE @PersonInMem AS Person_in_mem

-- insert some rows into this In-Memory Table Variable
INSERT INTO @PersonInMem
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

SELECT * FROM @PersonInMem
GO

Here we successfully created a Table Variable, inserted records into it and retrieved same by the SELECT statement, and this all happened in memory.
 

Now how can we see we that how much benefits we got from this? What we can do is, we can create a separate Disk-Based Table Variable and do similar operation on it and compare the results by checking the Execution Plan.

–> Comparing performance of both In-Memory vs Disk-Based Table-Variables

– Enable the Actual Execution Plan and run below script to Create and Populate both:

1. In-Memory Table Variable

2, Disk-Based Table Variable

-- 1. In-Memory Table Variable
DECLARE @PersonInMem AS Person_in_mem

INSERT INTO @PersonInMem
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

select * from @PersonInMem


-- 2. Disk-Based Table Variable
DECLARE @Person AS TABLE (
	 BusinessEntityID INT NOT NULL
	,FirstName NVARCHAR(50) NOT NULL
	,LastName NVARCHAR(50) NOT NULL
)

INSERT INTO @Person
SELECT TOP 1000
	 [BusinessEntityID]
	,[FirstName]
	,[LastName]
FROM [AdventureWorks2014].[Person].[Person]

select * from @Person
GO

–> Now, check the Actual Execution Plan results below:

1. Check the Cost of INSERT operation with both the tables:

– It took only 8% cost to insert into In-memory Table Variable.

– But it took 89% cost to insert into a Disk-Based Table Variable.

> If You see the individual Operators in both the plans you will see that :
For @PersonInMem Table Variable the cost of INSERT was just 19% compared to the cost of INSERT for @Person Table Variable that was 92%.

2. Check the Cost to SELECT/Retrieve rows both the tables:

– It took only 0% cost to retrieve rows from the In-memory Table Variable

– And it took 3% cost to retrieve rows from a Disk-Based Table Variable

This proves that the INSERT and SELECT operations with Memory Optimized table are way more faster that normal Disk-Based tables.

Thus, using Memory Optimized Table Variables will provide you better performance for storing temporary data within memory and process with in Stored Procedure or your T-SQL Scripts.
 

Update: Know more about In-Memory tables:

Check & Like my FB Page