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.
|
1 2 3 4 5 6 7 8 9 |
CREATE TYPE CustomerType AS TABLE (CustomerID INT NOT NULL, CustomerName NVARCHAR(100) NOT NULL, CustomerCreateDate DATETIME NOT NULL, INDEX IDX HASH(CustomerID) WITH(BUCKET_COUNT = 100) ) WITH (MEMORY_OPTIMIZED = ON ) |
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.
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.
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
|
1 2 3 |
DECLARE @TestTableVar AS CustomerType INSERT INTO @TestTableVar VALUES(1,'Henry Lawrence', '20200305') SELECT * FROM @TestTableVar |
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.
|
1 2 3 4 5 |
SET STATISTICS IO ON GO DECLARE @TestTableVar AS CustomerType INSERT INTO @TestTableVar VALUES(1,'Henry Lawrence', '20200305') SELECT * FROM @TestTableVar |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TYPE MemoryTypeCons AS TABLE (ID INT PRIMARY KEY NONCLUSTERED, Col1 VARCHAR(40) UNIQUE, Col2 VARCHAR(40) NOT NULL, Col3 int CHECK (Col3>=20) ) WITH (MEMORY_OPTIMIZED = ON ) GO DECLARE @ConsTable AS MemoryTypeCons INSERT INTO @ConsTable VALUES(1,'Value1',12 , 20) SELECT * FROM @ConsTable |
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
|
1 2 3 4 5 6 7 8 9 |
CREATE TYPE MemoryTypeColumnStore AS TABLE (ID INT PRIMARY KEY NONCLUSTERED, Col1 VARCHAR(40) UNIQUE, Col2 VARCHAR(40) NOT NULL, Col3 int CHECK (Col3>=20), INDEX IX01_ColumnStore CLUSTERED COLUMNSTORE ) WITH (MEMORY_OPTIMIZED = ON ) |
- 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.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TYPE TestMemTyp AS TABLE (Id INT PRIMARY KEY NONCLUSTERED, ODate DATETIME, St TINYINT, SNumber NVARCHAR(25), ANumber NVARCHAR(15) ) WITH (MEMORY_OPTIMIZED = ON ); |
Through the following query, we will create the disk-based table type.
|
1 2 3 4 5 6 7 8 |
CREATE TYPE TestDiskTyp AS TABLE (Id INT PRIMARY KEY NONCLUSTERED, ODate DATETIME, St TINYINT, SNumber NVARCHAR(25), ANumber NVARCHAR(15) ) |
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.
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.
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.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @TestMemOptVar AS TestMemTyp ; INSERT INTO @TestMemOptVar SELECT SalesOrderId, OrderDate, STATUS, SalesOrderNumber, AccountNumber FROM Sales.SalesOrderHeader S WHERE DueDate <= '2011-10-26 00:00:00.000'; DELETE FROM @TestMemOptVar WHERE Id <= 43758; UPDATE @TestMemOptVar SET St = 0 WHERE Id <= 43958; SELECT * FROM @TestMemOptVar; |
We will click the Database button to determine the database connection and credential settings.
As a final step, we will click the GO button and starts the execution of the query 40 times by the 20 virtual users.
The query took 8.643 seconds to complete. Now we will execute the same query for the disk-based table variable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
DECLARE @TestDiskOptVar AS TestDiskTyp ; INSERT INTO @TestDiskOptVar SELECT SalesOrderId, OrderDate, STATUS, SalesOrderNumber, AccountNumber FROM Sales.SalesOrderHeader S WHERE DueDate <= '2011-10-26 00:00:00.000'; DELETE FROM @TestDiskOptVar WHERE Id <= 43758; UPDATE @TestDiskOptVar SET St = 0 WHERE Id <= 43958; SELECT * FROM @TestDiskOptVar; |
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.
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.
Conclusion
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023