Ahmad Yaseen

Using Memory-Optimized Tables to Replace SQL Server Temp Tables and Table Variables

January 24, 2017 by

TempDB usage can be considered as a performance bottleneck for workloads that use temp tables and table variables intensively resulted in heavy IO usage. A valuable alternatives for the 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 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 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:

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 temp table, where the table schema will be recovered only in the case of restart or crash:

We will create also a normal physical table that will be used to store the data temporarily:

In order to create the Memory-Optimized Variable Table, we should create a Memory-Optimized Table Type using the CREATE TYPE statement as below:

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 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:

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:

It is clear from the following PerfMon window that the normal 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 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:

The result of the previous script will show us that the SP that used the normal temp table is taking the longest execution time compared to the ones using other tables types, and the ones that use the Memory-Optimized 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 the normal temp and variable tables.

See more

To get 3 free licenses to a SQL Server monitoring tool, download ApexSQL Monitor and fill out this simple survey

Useful Links:

Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen
SQL Server performance tuning

About Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

4,951 Views