Memory optimized Temporary table in SQL Server

Today, we will quickly see how do we create memory optimized temporary table in SQL Server and how to use it efficiently.

I would recommend to read previous blogs before we continue further:
In-Memory OLTP and Memory Optimized data filegroup in SQL Server 2014
In-Memory OLTP: Compatibility Check for Datatypes in SQL Server
In-Memory OLTP: Compatibility Check for SQL Server and Database

Before we start the technical aspect, let us be clear why do we need memory optimized temp tables in SQL Server. Temp tables are very powerful objects in SQL server which is created in TEMPDB. These tables are, as name mentioned, created for a short live may be a for a batch scope, procedure scope etc. With in the scope, we can use the temp tables multiple times. So, many developers would use these objects to populate the data as required for thier operations and use these objects instead of querying the original physical table. Not to wonder, now days, the usage of temp tables are more and many is thinking its a free tool 🙂 .

But, on flip side, as we create huge number of tables, there is also a performance imapct in the system. There is only one database “TempDB” per instance where SQL Server would need to create these temp objects and do the oprations. So, it is observed there are some serious performance issues with tempDB at times. I am not covering tempdb performance issuesin this blog, but introduce memory optimized temp tables introduced in SQL Server 2014 which we can efficiently replace with normal temp objects to avoid performance issues.

Lets get into Techinical now –

Here is a sample example of creating a physical table and a procedure.


Create Table T1 (PeopleID int Primary key, CourseID int)

Insert into T1 values(1,100),(2,100),(3,200),(4,200),(5,200),(6,300)
GO
Create procedure usp_Normalproc @CourseID int
As
Begin
	
	Create Table #TempTable(PeopleID int, CourseID int)

	Insert into #TempTable (PeopleID,CourseID)
		Select PeopleID,CourseID From T1 where CourseID=@CourseID
	
	Select * From #TempTable
End

Exec usp_Normalproc 100

Now, lets look at how we convert this temp table usage to memory optimized temp object in the procedure.

First, Lets create a memory optimized table as below:


Create Table TempTable(PeopleID int, CourseID int,
SpidFilter  SMALLINT    NOT NULL   DEFAULT (@@spid),  
INDEX ix_SpidFiler NONCLUSTERED (SpidFilter),  
 CONSTRAINT CHK_TempTable_SpidFilter  CHECK ( SpidFilter = @@spid ),)
 WITH  
        (MEMORY_OPTIMIZED = ON,  
            DURABILITY = SCHEMA_ONLY);

SpidFilter – is a new column introduced as part of conversion to identify the rows for the session created. This has been indexed for performance.
CHK_TempTable_SpidFilter – This a constraint to make sure the spid has been populated

As next step, We need to create a function as below which is used to filter data for session.


CREATE FUNCTION dbo.fn_SpidFilter(@SpidFilter smallint)  
    RETURNS TABLE  
    WITH SCHEMABINDING , NATIVE_COMPILATION  
AS  
    RETURN  
        SELECT 1 AS fn_SpidFilter  
            WHERE @SpidFilter = @@spid; 

Finally, we will create a security policy to filter the records from the temptable as per the session id its being used.


CREATE SECURITY POLICY dbo.TempTable_SpidFilter_Policy  
    ADD FILTER PREDICATE dbo.fn_SpidFilter(SpidFilter)  
    ON dbo.TempTable  
    WITH (STATE = ON);  

Once we set up memory optimzed table and filters, its time to change the procedure as below:


Alter procedure usp_MO_proc @CourseID int
As
Begin
	
	--Create Table #TempTable(PeopleID int, CourseID int)

	Insert into TempTable (PeopleID,CourseID)
		Select PeopleID,CourseID From T1 where CourseID=@CourseID
	
	Select * From TempTable

	Delete From TempTable Where SpidFilter = @@spid
End

It is very important to delete the data from the memory optimized table. On failing, this will create multiple records into memory optimized table if we re-run the procedure within the same session(please note that its very likely during testing).

Hope you enjoyed this blog, looking for your valuable feedback on the same.

Advertisements

In-Memory OLTP: Compatibility Check for Datatypes in SQL Server

Hope you enjoyed my earlier posts on
In-Memory OLTP and Memory Optimized data filegroup in SQL Server 2014
In-Memory OLTP: Compatibility Check for SQL Server and Database

Here, as continuation of compatibility check, we are moving further one more step on Datatype compatibility.

The below script is primarily focusing on major limitations like unsupported datatypes,computed columns,sparse columns, unavailability of primary keys and presence of clustered index. The script has been developed for SQL Server 2014 version(if there are any of the above limitations removed in later versions, you may need to change the script accordingly.).

Note: Please change the result view as “Results To Text(Ctrl + T)” for better readability of the result.


PRINT '*******************************************************'
PRINT 'DATATYPE COMPATIBILITY CHECK'
PRINT '*******************************************************'

PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS IS DUE TO UNSUPPORTED DATATYPES. '
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE 
	(TYPE_NAME(USER_TYPE_ID) IN 
            ('IMAGE','TEXT','NTEXT','DATETIMEOFFSET','XML','GEOMETRY',
             'DATETIMEOFFSET','GEOGRAPHY','TIMESTAMP','SQL_VARIANT'))
	OR
	(TYPE_NAME(user_type_id) IN 
            ('VARCHAR','CHAR','NVARCHAR','NCHAR','VARBINARY') AND MAX_LENGTH = -1)
	OR
	/*FILESTREAM can only be enabled with VARBINARY(MAX), however, the defined form of datatype is
		not supported in In-Memory Tables, provided the below for FILESTREAM/FILETABLE for completeness,*/
	(TYPE_NAME(USER_TYPE_ID) IN ('VARBINARY') AND MAX_LENGTH = -1 AND (IS_FILESTREAM =1 OR IS_FILETABLE = 1))
	)

--COMPUTED COLUMNS
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS IS DUE TO UNSUPPORTED COMPUTED COLUMNS'
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE IS_COMPUTED =1)

--SPARSE COLUMNS
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY HAVE SPARSE COLUMNS'
SELECT NAME FROM SYS.TABLES WHERE NAME IN
	(SELECT OBJECT_NAME(OBJECT_ID) FROM SYS.COLUMNS WHERE is_sparse =1) 

-- ABSENCE OF PRIMARY KEY
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY DO NOT HAVE PRIMARY KEYS'
SELECT NAME FROM SYS.TABLES WHERE NAME NOT IN 
	(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  WHERE CONSTRAINT_TYPE ='PRIMARY KEY')

--PRESENCE OF CLUSTERED INDEX
PRINT 'THE BELOW TABLE(S) ARE NOT COMPATIBLE TO IN-MEMORY AS THEY HAVE CLUSTERED INDEX'
SELECT B.NAME FROM SYS.INDEXES A
INNER JOIN SYS.TABLES B ON A.OBJECT_ID =B.OBJECT_ID WHERE A.TYPE_DESC = 'CLUSTERED'

In-Memory OLTP: Compatibility Check for SQL Server and Database

In continuation with my last post, I would like to further move on to the topic by starting with the limitations of In-Memory OLTP feature.

“Entire Data in Memory” looks such a wonderful idea to improve the performance by reducing the locking/latching etc, however, Is it true to adopt for all systems? NO. There are lots of restrictions (at least in SQL Server 2014 and hoping many would be taking off in future versions) with In-Memory OLTP feature.
You can refer those limitation in the below MSDN

Transact-SQL Constructs Not Supported by In-Memory OLTP

Here, I would like to share a mechanism to verify the compatibility level of existing environment to migrate to In-Memory Tables. By now(once you read the above link), as you all know the limitations are more, I would like to split the script in multiple snippets for simplicity.

Please refer the below script to verify the compatibility level of your environment at Server and Data base level.


SET NOCOUNT ON

PRINT '************************************************'
PRINT 'DATABASE SERVER COMPATIBILITY CHECK'
PRINT '************************************************'
IF NOT EXISTS(SELECT 1 FROM (SELECT SUBSTRING(@@VERSION,22,4)) A (VERSIONNUMBER) WHERE VERSIONNUMBER >=2014)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN SQL SERVER 2014 OR LATER VERSIONS.'

IF EXISTS(SELECT 1 FROM (SELECT @@VERSION) A (BITVERSION) WHERE CHARINDEX('(64-BIT)',BITVERSION)=0)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN 64 BIT VERSIONS.'

IF EXISTS(SELECT * FROM MASTER.SYS.SERVER_TRIGGERS)
	PRINT HOST_NAME() + ' IS ENABLED WITH SERVER LEVEL TRIGGER AND IN-MEMORY OLTP WILL NOT SUPPORT SERVER LEVEL TRIGGERS.'


PRINT '************************************************'
PRINT 'DATABASE COMPATIBILITY CHECK'
PRINT '************************************************'
	
IF (DB_NAME() NOT IN (SELECT DB_NAME(DATABASE_ID) FROM SYS.MASTER_FILES WHERE TYPE_DESC = 'FILESTREAM'))
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT WITH MEMORY_OPTIMIZED_DATA FILEGROUP. THE CURRENT DATABASE WILL NOT SUPPORT.'
	
IF (DB_NAME() IN (SELECT NAME FROM SYS.DATABASES WHERE IS_MERGE_PUBLISHED & IS_SUBSCRIBED & IS_DISTRIBUTOR & IS_PUBLISHED = 1))
	PRINT DB_NAME() + 'HAS BEEN REPLICATED AND IN-MEMORY OLTP WILL NOT SUPPORT REPLICATED DATABASE'

IF EXISTS(SELECT * FROM SYS.DATABASES WHERE DB_ID () = DATABASE_ID AND IS_AUTO_CLOSE_ON = 1)
	PRINT DB_NAME() + 'HAS BEEN CONFIGURED WITH AUTO CLOSE OPTION AND IN-MEMORY OLTP WILL NOT SUPPORT AUTO_CLOSE FEATURE'

We will cover more on other aspects like datatypes/constraints etc in future posts, until then, happy learning….

In-Memory OLTP and Memory Optimized data filegroup in SQL Server 2014

Today, Dussehra Or Vijayadasami is considered to be the most auspicious day to start learning something new.Let us take this opportunity to start learning about InMemory OLTP in SQL Server.

In-Memory OLTP is one of the most fascinating features in SQL Server 2014.

First off, if you are going to work with InMemory OLTP, then you need to know about Memory optimized data Filegroup. In short, for any database, where memory optimized table(In Memory tables) needs to be created, there should be provided with separate FileGroup called MEMORY_OPTIMIZED_DATA. This filegroup is extremely important for the feature to store the Checkpoint information and delta files for SQL Server during the disaster(Any restart/crash).Let us look at the syntax to create the database and filegroup.

Syntax:


CREATE DATABASE [TestDB]
 ON  PRIMARY ( NAME = N'TestDB', FILENAME = N'D:\data\TestDB.mdf' )
 LOG ON ( NAME = N'ManTest_log', FILENAME = N'D:\data\TestDB_log.ldf')
    
ALTER DATABASE [TestDB] 
    ADD FILEGROUP TestDB_MO CONTAINS MEMORY_OPTIMIZED_DATA 

ALTER DATABASE TestDB
	ADD FILE ( NAME = TestDB_MO , FILENAME = N'D:\data\Datasample_database_1') 
TO FILEGROUP TestDB_MO 

If you have not created memory optimized data file group and you try to create a memory optimized table, you may be facing the below error message.

Msg 41337, Level 16, State 0, Line 9
Cannot create memory optimized tables in a database that does not have an online and non-empty MEMORY_OPTIMIZED_DATA filegroup.

Another important point is you can only create MEMORY_OPTIMIZED_DATA filegroup only in 64 bit SQL Server. Otherwise, you may end up with the below error message

Msg 41356, Level 15, State 1, Line 1
Filegroups with MEMORY_OPTIMIZED_DATA can only be created in 64-bit installations of SQL Server.

As the error message clearly suggests In-Memory OLTP will only support in 64 bit SQL Server.
You can use @@VERSION global variable to know about your SQL Server bit version.

Once you created the database successfully, you will be able to create Memory optimized table as below:


CREATE TABLE dbo.SampleTable
(
   id INT NOT NULL,
   item_id INT NOT NULL,
   CONSTRAINT PK_SampleTable PRIMARY KEY NONCLUSTERED (id,item_id)
) WITH (MEMORY_OPTIMIZED=ON)

Now, we covered very basic requirements for In Memory OLTP concept in SQL Server.To summarize the above

1. make sure you have installed SQL Server 2014 with 64 bit
2. create a memory optimized data filegroup for the database

Next post, We will cover more on this topic…until then happy Dussehra.