Thursday, October 5, 2017

Memory Optimized Table Variable in SQL Server

This amazing feature (In-Memory Table Variables) was introduced in SQL Server 2014 to provide the better performance for storing temporary data within memory and process with in the stored Procedure or other T-SQL Scripts.
A table variable created using a memory-optimized table type is a memory-optimized table variable because SQL Server does not give us the permission to create an In-memory table variable directly with declare clause.


Memory-optimized table variables advantages or limitations:
  • The variables are only stored in memory. Data access is more efficient because memory-optimized table type use the same memory-optimized algorithm and data structures used for memory-optimized tables, especially when the variables are used in natively compiled stored procedures.
  • Another big advantages with memory-optimized table variables are that there is no tempdb utilization. Means Table variables are not stored in tempdb and do not use any resources in tempdb. 
  • Like memory-optimized tables, memory-optimized table variables do not support parallel plans and must fit in memory and do not use disk resources.


To understand this, we are going to take a dummy customer table having CustNo and CustName columns as given below:  
CustNo
CustName
20541
Lenovo Ltd.
20411
Amity University
20021
CMC Computer Ltd
20281
MRI Inc.
20151
NIIT Technologies
Based on the above table, we have to create a Memory Optimized Table Type called Customer_InMemory which is going to be used by Memory Optimized Table Variable as given below-
----- Create Memory Optimized Table Type
CREATE TYPE dbo.Customer_InMemory
AS TABLE(
     CustNo INT NOT NULL
    ,CustName NVARCHAR(50) NOT NULL
    INDEX [Ind_CustNo] HASH (CustNo)
                WITH ( BUCKET_COUNT = 2000)
)
WITH ( MEMORY_OPTIMIZED = ON )
GO
We have created Memory Optimized Table Type which is going to use by Memory Optimized Table Variable as given below-
---- Declare In-Memory Table Variable
Declare @Customers as Customer_InMemory

---- Insert values into In-Memory Table Variable
INSERT INTO @Customers (CustNo, CustName)
SELECT CustNo, CustName From [dbo].[CustMaster]

---- Pull values from In-Memory Table Variable
SELECT CustNo, CustName From @Customers
---- Output
CustNo
CustName
20541
Lenovo Ltd.
20411
Amity University
20021
CMC Computer Ltd
20281
MRI Inc.
20151
NIIT Technologies



Note: When a memory-optimized table variable is declared based on the table type, an instance of the table and index structures corresponding to the table type is created in the user session. 
With the help of the Memory Optimized Table Variables, we can increase the T-SQL queries performance. There are many typical usage scenarios where memory-optimized table variables could be more usable such as:
  1. If you want to store intermediate results then you can create a single result sets based on multiple queries in natively compiled stored procedures.
  2. Passing table-valued parameters into natively compiled stored procedures and interpreted stored procedures.
  3. Replacing disk-based table variables, and in some cases #temp tables that are local to a stored procedure. This is particularly useful if there is a lot of tempdb contention in the system.
  4. Table variables can be used to simulate cursors in natively compiled stored procedures, which can help us work around surface area limitations in natively compiled stored procedures.


Conclusion
In-memory table variable can be used in the same way as disk-based table variables. We can insert, update, and delete rows in the table variable, and use them in Transact-SQL queries. We can also pass the variables into natively compiled and interpreted stored procedures, as table-valued parameters. They do not interact with TempDb. So, Memory-optimization results in speed increases that are often 10 times faster or more.

No comments:

Post a Comment