Esat Erkec
Add Batch Requests/Sec to the performance monitor

Memory-Optimized Table Variables in SQL Server

May 6, 2020 by

This article will cover the usage details and performance advantages of the memory-optimized table variables.

A table variable is a sort of variable that is used to store data temporarily. Disk-based table variables are created in the TempDB database by SQL Server and their life cycle begins and ends on this database. In this context, database performance issues in TempDB directly influence the performance of the disk-based table variables.

With the SQL Server 2014 version, the memory-optimized tables were introduced and they are impacting performance positively due to low-latency and high throughput benefits. There are two durability options available for the memory-optimized tables.

SCHEMA_AND_DATA option enables us to preserve schema and data at the same time so we don’t carry any losing data risk.

SCHEMA_ONLY option preserves only the schema of the table. For this reason, we lose the data when the server is restarted. Particularly, non-durable memory-optimized tables produce zero disks I/O because they solely use the memory resources to store the data. On the other hand, data does not preserve on disk, it means that if the SQL Server engine is restarted, we will lose the data but table schema will be re-created. The memory-optimized table variable offers advanced performance with the minimum latency by using the memory-optimized infrastructure. Also, if we decide to use them we don’t require any application code changes.

What is the memory-optimized table type?

The memory-optimized table type is used to specify a predefined table definition. The following query will create a memory-optimized table type.

In this syntax, some keywords may draw our attention to their differences;

MEMORY_OPTIMIZED = ON syntax identifies that this table type will be on the memory-optimized structure. Memory-optimized tables need at least one index to connect rows. This index can be a hash index or a nonclustered index. Individually for this table type, we chose the hash index type so we must set the bucket number. This number specifies the bucket number of the hash index.

Tip: For memory-optimized tables we can use two types of indexes. Hash Index offers advanced performance to seek equality values. The nonclustered index will be a good option to seek the range value conditions.

When we create any memory-optimized table type, it will be shown under the Types folder in SQL Server Management Studio.

Find table types in SSMS

When we right-click on the CustomerType and select Properties menu, we can find out the details of the memory-optimized table type. On this screen, the Memory Optimized option shows the type of table type.

User-defined table type screen in the SSMS

Memory-optimized table variable

Memory-optimized table variables do not consume the TempDB resources so they are not affected by any contention and latency issues of the TempDB database. At the same time, they give an outstanding performance relative to disk-based table variables to access the data. We will apply the following steps to create a memory-optimized table variable.

  • Type a DECLARE statement to initialize a variable
  • Assign a name to the variable and this name must start with @ sign
  • Assign it to a memory-optimized table type after the AS keyword
  • CRUD operations can be performed

Creating Memory-optimized table variable

Now we will prove zero I/O concept of this table variable types. After enabling the STATISTICS IO option, the query editor displays the statistical values of the disk activity generated by the query. We will enable this option for the previous query and will execute it.

Test disk IO of the memory-optimized table variable

The result is not surprising, we have not seen any I/O activity because it uses the memory-optimized algorithm.

The memory-optimized table variables allow us to create the following constraints and we can define these constraints with the declaration of the table type.

  • Unique
  • Null
  • Check

How to use constraints with memory-optimized table variable

Memory-optimized table variables do not allow to create a clustered index, for this reason, we have to define primary key constraint as a nonclustered.

  • Tip: Columnstore indexes are used to store large data in the columnar format, unlike the row-based storage technology. Memory-optimized table variables do not allow creating this type of indexes on it. When we try to create a columnstore index, we will get the following error

The statement failed because columnstore indexes are not allowed on table types and table variables

  • The statement failed because columnstore indexes are not allowed on table types and table variables. Remove the column store index specification from the table type or table variable declaration.

Performance benchmarking: Memory-optimized table variable vs disk-based table variables

From the beginning of this article, we have been focusing on the performance advantages of memory-optimized table variables. In this section, we will make a performance test and compare the results of the memory-optimized and disk-based table variables. To perform this test we will use SQLQueryStress because it is very simple and handy to measure the performance result of the queries. At the same time, we will perform the following scenario in the test query.

  • 1001 rows will be inserted to the table variables
  • 100 rows will be deleted from the table variables
  • 200 rows of the table variable will be updated
  • The remaining rows of the table variables will be fetched by the table variables

At first, we will create the memory-optimized table type.

Through the following query, we will create the disk-based table type.

Before we start our performance test, we will use Performance Monitor, also known as Perfmon in order to capture performance statistics of the queries. At first, we will launch the Perfmon and right-click on the performance capturing screen and select the Remove All Counters.

Remove all counters from the performance monitor

As the second step, we will click the (+) button to add new counters and add the Temp Tables Creation Rate under the SQL Server: General Statistics menu. This counter presents how many temp tables or table variables are created per second.

Add Temp Tables Creation Rate to the performance monitor

Our second counter will be Total Latch Wait Time (ms) under the SQL Server: General Statistics and it indicates total latch wait time for the last second.

Add Total Latch Wait Time (ms) to the performance monitor

Our last counter will be Batch Requests/Sec under SQL Server: SQL Statistics. This counter represents how many SQL statements received by the SQL engine. This counter solely does not indicate any problem but we will use this counter to track the activity of the server during the execution of the queries.

Add Batch Requests/Sec to the performance monitor

Now we will start the performance test for the memory-optimized table variable using the SQLQueryStress. In the Number of Iterations box, we can define how many times the query will be executed and we will set this number as 40. The number of Threads determines how many concurrent virtual sessions will execute this query and we will set this number as 20.

Use SQLStressTest for the performance test

We will click the Database button to determine the database connection and credential settings.

Database settings of the SQLStressTest

As a final step, we will click the GO button and starts the execution of the query 40 times by the 20 virtual users.

Use SQLStressTest for the memory-optimized table variable  performance test

The query took 8.643 seconds to complete. Now we will execute the same query for the disk-based table variable.

Analyze SQLStressTest results

The query using the disk-based table variable took 13.053 seconds to complete. Also, you can see the performance monitor result in the below illustration.

Analyzing the results of the Performance Monitor

This line graphic represents, SQL Server can handle more requests for the memory-optimized table variable in a shorter time than disk-based table variables. At the same time, there isn’t any latch issue occurred for it. This performance test has clearly shown us that the memory-optimized table variable performs better performance than the disk-based ones.


In this article, we discussed the memory-optimized table variables usage details and performance benefits. This type of the variable stores data in the memory so that they don’t produce any disk I/O thus they aren’t affected by any contention or latency which may occur on the TempDB database. This working logic is the biggest advantage against disk-based table variables.

Esat Erkec
Latest posts by Esat Erkec (see all)