TempDB usage can be considered as a performance bottleneck for workloads that use SQL temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the SQL temp table and table variable are SCHEMA_ONLY Memory-Optimized tables and the Memory-optimized Table Variable, where the data will be completely stored in the memory without the need to touch the TempDB database, providing the best data access performance. SCHEMA_ONLY Memory-Optimized table and the Memory-optimized Table Variable are stored only in the memory with no related component in the disk. It involves no IO activity or TempDB utilization. It can also participate in the transactions without the need to log the transactions.
In this article, we will prove practically that the SCHEMA_ONLY Memory-Optimized Table and the Memory- Optimized Variable Tables are the best replacements for the SQL temp tables and variable tables with better CPU, IO and execution time performance.
SQL Server In-Memory OLTP, also known as ‘Hekaton’, is a new in-memory processing technology that was introduced in SQL Server 2014, integrated into the SQL Server Database Engine and optimized for an Online Transaction Processing (OLTP) workload.
In-Memory OLTP introduces three new data structures;
- Memory-Optimized Tables that store its data into memory in multiple versions without causing any locks or latches on the data, as it reads from the underlying disk only during the database recovery, achieving the best performance and the most efficient data access.
- The second data structure is Natively-Compiled Stored Procedures that are compiled to native code (DLL) when they is created and interact only with Memory-Optimized Tables, where any call to that Natively-Compiled Stored Procedure is a call to its related DLL, providing the most efficient query processing and business logic execution.
- The third kind of Memory-Optimized objects are Memory-Optimized Table Variable that is created using the Memory Optimized Table Type. SQL Server generates a DLL for each created Memory-Optimized Table Type that includes the functions required for accessing the indexes and retrieving data from the related Memory-Optimized Table Variable. When that table variable is created using that table type, an instance of the table type components will be created on that table.
Configuration
Memory-Optimized tables can be configured as Durable to recover data in the case of any failure from the transaction logs, which is the default setting, or configured as Non-Durable where the data will not be recovered in the case of failure as the memory buffers will be flushed. The durable setting is used for tables with many updates on daily basis, where any change is required. And the Non-Durable setting is useful in the case of the staging tables that are used for temporary calculations and truncated on daily basis. Setting the Durability option to SCHEMA_ONLY value will recover the table schema only in the case of any failure, where the SCHEMA_AND_DATA Durability value will recover both the schema and the data in the case of any failure.
Another option that should be set when defining non-clustered indexes, while creating the Memory-Optimized Tables, taking into consideration that the clustered index is not supported in the Memory-Optimized Tabled, is the BUCKET_COUNT. The Microsoft recommended value for that option is between 1.5 to 2 times the estimated number of unique values for the column involved in the non-clustered index.
Limitations
There are some Memory-Optimized OLTP features limitations in SQL Server 2014 that are resolved in SQL Server 2016, such as the supported collation for the string columns involved in the non-clustered index, where the only supported collation was the *_BIN2 collation. Now SQL Server 2016 supports all collation types for that kind of columns.
Another limitation in SQL Server 2014 is that you can’t ALTER any Memory-Optimized Table after creating it, but it is allowed now in SQL Server 2016.
Also, many other features are supported now in SQL Server 2016 such as
- ALTER the Natively-Compiled Stored procedures,
- Transparent Data Encryption,
- DML triggers in the Memory-Optimized Tables,
- Natively-compiled scalar user-defined functions,
- Indexes on null-able columns,
- Parallel plan execution for the operations that access the Memory Optimized tables,
- the use of LEFT and RIGHT OUTER JOIN, UNION and UNION ALL, SELECT DISTINCT,
- OR and NOT statements,
- Nested SELECT statements and stored procedures calling and Large Objects (LOB) usage such as VARCHAR(MAX), NVARCHAR(MAX) or VARBINARY(MAX).
The Memory-Optimized OLTP feature is available in SQL Server 2014 Enterprise Edition only, but in SQL Server 2016 SP1, it is available now in the Standard, Web and Express Editions too.
In this article’s demo we will compare the performance of using five tables types; the SCHEMA_ONLY Memory-Optimized Table, the normal SQL temp table, the physical table, the normal variable table and the Memory- Optimized Variable Table.
We will start our demo by preparing the SQLShackDemo test database to host Memory-Optimized tables. This can be achieved by adding a new filegroup that will contain the Memory_Optimized_Data and create a data file under that filegroup. Once the filegroup and database data file created, we will enable the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT option that is used to lessen the need to have table hints on the Memory-Optimized Tables:
|
1 2 3 4 5 6 7 8 9 |
USE SQLShackDemo GO ALTER DATABASE SQLShackDemo ADD FILEGROUP MemoryOpt_FG CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE SQLShackDemo ADD FILE (name='MemoryOptDataF', filename='D:\Data\MemoryOptDataF') TO FILEGROUP MemoryOpt_FG ALTER DATABASE SQLShackDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON GO |
You can verify that the Memory-Optimized filegroup is added or add it from the Filegroups page of the Database Properties window as follows:
After that, we will create the Memory-Optimized Table by turning on the MEMORY_OPTIMIZED option on that table and set the Durability to SCHEMA_ONLY as mentioned previously to simulate the case of SQL temp table, where the table schema will be recovered only in the case of restart or crash:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE SQLShackDemo GO CREATE TABLE [MemoryOptTempTable] ( [ID] INT IDENTITY(1,1) NOT NULL , [First_Name] nvarchar(10) NULL, [FLast_Name] nvarchar(10) NULL, CONSTRAINT [PK_MemoryOptTempTable] PRIMARY KEY NONCLUSTERED ( ID ASC ) )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY ) GO |
We will create also a normal physical table that will be used to store the data temporarily:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE SQLShackDemo GO CREATE TABLE [PhysicalTempTable] ( [ID] INT IDENTITY(1,1) NOT NULL , [First_Name] nvarchar(10) NULL, [Last_Name] nvarchar(10) NULL, CONSTRAINT [PK_PhysicalTempTable] PRIMARY KEY NONCLUSTERED ( ID ASC ) ) GO |
In order to create the Memory-Optimized Variable Table, we should create a Memory-Optimized Table Type using the CREATE TYPE statement as below:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE SQLShackDemo GO CREATE TYPE MemoryOptTabVar as TABLE ( [ID] INT IDENTITY(1,1) NOT NULL , [First_Name] nvarchar(10) NULL, [Last_Name] nvarchar(10) NULL, INDEX IX_MemoryOptTabVar_ID NONCLUSTERED (ID) ) WITH (MEMORY_OPTIMIZED = ON) GO |
And to complete the full scenario, we will create five stored procedures, with simple INSERT statements to fill the five tables types mentioned previously. In the case of the normal SQL temp table, variable table and Memory-Optimized Variable Table, the table definition will be included within the stored procedure itself as in the below script:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
USE SQLShackDemo GO CREATE PROCEDURE dbo.TempDB_TempTableTest --- Normal SQL temp table AS BEGIN SET NOCOUNT ON; CREATE TABLE #NorTempTable (ID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED, [First_Name] nvarchar(10) NULL, [Last_Name] nvarchar(10) NULL) INSERT INTO #NorTempTable (First_Name,Last_Name) VALUES ('Sanya','J') DROP TABLE #NorTempTable END GO CREATE PROCEDURE dbo.MemoryOptTableTest --- Memory Optimized SQL temp table AS BEGIN SET NOCOUNT ON; INSERT INTO MemoryOptTempTable (First_Name,Last_Name) VALUES ('Sanya','J') END GO CREATE PROCEDURE dbo.PhysicalTableTest --- Physical SQL temp table AS BEGIN SET NOCOUNT ON; INSERT INTO [PhysicalTempTable] (First_Name,Last_Name) VALUES ('Sanya','J') END GO CREATE PROCEDURE dbo.VariableTable --- normal variable Table AS BEGIN SET NOCOUNT ON; DECLARE @variableTab TABLE (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, [First_Name] nvarchar(10) NULL, [Last_Name] nvarchar(10) NULL) ; INSERT @variableTab (First_Name,Last_Name) VALUES ('Sanya','J') END GO CREATE PROCEDURE dbo.MemoryOptVarTable --- Memory Optimized variable Table AS BEGIN SET NOCOUNT ON; DECLARE @MemOptVarTab MemoryOptTabVar ; INSERT @MemOptVarTab (First_Name,Last_Name) VALUES ('Sanya','J') END GO |
To perform our testing, we will execute the five stored procedures and use it to insert 20000 records each time, leaving 5 seconds delay between each execution to make it easy for us to get the performance difference between it. We will use the PerfMon performance counters monitoring tool to check the performance of these tables, where we will monitor the Processor and Log Bytes Flushed/Sec counters to get the CPU and I/O of each execution as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
USE SQLShackDemo GO EXEC dbo.TempDB_TempTableTest GO 20000 WAITFOR DELAY '00:00:05' GO EXEC dbo.MemoryOptTableTest GO 20000 WAITFOR DELAY '00:00:05' GO EXEC dbo.PhysicalTableTest GO 20000 WAITFOR DELAY '00:00:05' GO EXEC dbo.MemoryOptVarTable GO 20000 |
It is clear from the following PerfMon window that the normal SQL temp table consumes the CPU and I/O resources and took long time to finish compared to the other tables types. You can also derive from the same image that the Memory-Optimized SQL temp table and the Memory-Optimized Variable Table consumed the minimum I/O and CPU resources compared with the rest types of tables:
The previous script can be modified to show the duration of each stored procedure’s execution as below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
USE SQLShackDemo GO CREATE TABLE #StartEndTime(TableType nvarchar(100),StartTime datetime, EndTime Datetime) INSERT INTO #StartEndTime VALUES('TempTable', GETDATE(),NULL) GO EXEC dbo.TempDB_TempTableTest GO 20000 UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='TempTable' WAITFOR DELAY '00:00:05' GO INSERT INTO #StartEndTime VALUES('MemoryOptTable', GETDATE(),NULL) GO EXEC dbo.MemoryOptTableTest GO 20000 UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='MemoryOptTable' WAITFOR DELAY '00:00:05' GO INSERT INTO #StartEndTime VALUES('PhysicalTable', GETDATE(),NULL) GO EXEC dbo.PhysicalTableTest GO 20000 UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='PhysicalTable' WAITFOR DELAY '00:00:05' GO INSERT INTO #StartEndTime VALUES('VariableTable', GETDATE(),NULL) GO EXEC dbo.MemoryOptVarTable GO 20000 UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='VariableTable' WAITFOR DELAY '00:00:05' GO INSERT INTO #StartEndTime VALUES('Memory Opt VariableTable', GETDATE(),NULL) GO EXEC dbo.MemoryOptVarTable GO 20000 UPDATE #StartEndTime SET EndTime =GETDATE() WHERE TableType ='Memory Opt VariableTable' SELECT TableType , DATEDIFF (SS ,StartTime,EndTime) AS Duration FROM #StartEndTime |
The result of the previous script will show us that the SP that used the normal SQL temp table is taking the longest execution time compared to the ones using other tables types, and the ones that use the Memory-Optimized SQL temp table and the Memory-Optimized required small period of time to insert 20000 records. Although the SP that uses the Variable table required the minimum time, it consumes the I\O and CPU resources:
Conclusion:
In-Memory OLTP is a new feature, introduced in SQL Server 2014 and enhanced in SQL Server 2016 by addressing most of the limitations, that stores the data directly to the memory, eliminating the locks and latches occurred during the reading process and providing the best data access performance. From the testing scenario we performed, it is clear that using the SCHEMA_ONLY Memory-Optimized Table and the Memory-Optimized Variable Table excelled on the rest of tables types in having the best performance that can be seen by the minimum CPU and I/O resources consumption and the minimum execution time required to fill these two tables types compared with the other tables, making it the best alternatives to normal SQL temp tables and variable tables.
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021