SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures into memory and compile the stored procedures into native x86 code. As a result, you can easily query these database objects directly from memory with the best performance and the least possible data access latency. In addition to that, the SQL Server Engine will no longer use the old latching and locking mechanism to control the data access concurrency. Instead, a high performance row versioning mechanism will be used to control the concurrency. This optimistic concurrency mechanism is 5 times to 20 times faster than the normal disk-based processing, due to reading the data from the memory directly.
In order to help us in the In-Memory OLTP migration decision, Microsoft provides the Analysis Migrate Report tool, also known as AMR tool, that analyzes a selected database objects and workload, then choose the suited tables and stored procedures that can be candidates for the In-Memory OLTP migration process.
Like any new feature introduced at the first time, In-Memory OLTP comes with a number of limitations in SQL Server 2014, such as the supported data types and features. In SQL Server 2016, many of these limitations and restrictions are eliminated, making the In-Memory OLTP feature more powerful and useful, allowing more systems to take advantage of this improved technology. In this article, we will go through some of these key improvements to In-Memory OLTP.
Collation Support
To be able to use In-Memory OLTP in SQL Server 2014, you should use the BIN2 collation type for any character column that is used as an index key. Also, in any comparison between string values in the Natively Complied Stored Procedures, you should use the BIN2 collation only.
SQL Server 2016 version eliminates this collation limitation, where you can use any collation type for the character columns used as index key columns or the string values comparison in the Natively Compiled Stored Procedures. You may expect performance degradation when using a non-BIN2 collation for the character columns.
Let us see how it works practically. A new testing database, InMemorySQLShackDemo, will be created, a new non-default filegroup will be added to the database to store the memory optimized data, a data file will be created in the new filegroup and finally the In-Memory OLTP feature will be enabled on that database, by running the below T-SQL script twice, the first time on SQL Server 2014 instance and the second time on SQL Server 2016 instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE master GO CREATE DATABASE [InMemorySQLShackDemo] ON PRIMARY ( NAME = N'InMemorySQLShackDemo', FILENAME = N'D:\Data\InMemorySQLShackDemo.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'InMemorySQLShackDemo_log', FILENAME = N'D:\Data\InMemorySQLShackDemo_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%) GO ALTER DATABASE InMemorySQLShackDemo ADD FILEGROUP InMemorySQLShackDemo_FG CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE InMemorySQLShackDemo ADD FILE (name='InMemorySQLShackDemoDF', filename='D:\Data\InMemorySQLShackDemo') TO FILEGROUP InMemorySQLShackDemo_FG ALTER DATABASE InMemorySQLShackDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON GO |
The database is able to host the In-Memory data now. We will try to create a Memory-Optimized table on that database, with the EmpAddress column using the ARABIC collation type. Applying the below CREATE TABLE T-SQL statement on a SQL Server 2014 instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
USE [InMemorySQLShackDemo] GO CREATE TABLE [dbo].[InMemoryDemo]( [EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000), [EmpName] [varchar](50) NOT NULL, [EmpDEPID] [int] NOT NULL, [EmpBirthDay] [datetime] NULL, [EmpAddress] [varchar](50) COLLATE ARABIC_CI_AS NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
The CREATE TABLE statement will fail, showing that we are using a collation with code page different from the BIN2 collation type as shown in the error message below:
Trying the same CREATE TABLE statement, but this time using SQL Server 2016 instance, the table will be created successfully on that database with a non BIN2 collation type as shown clearly below:
New Standard Reports
SQL Server 2016 provides us with a new built-in standard report, which works at the database level, that shows useful information that helps us identifying database performance bottlenecks and assists in any In-Memory OLTP migration decision. The new standard report, Transaction Performance Analysis Overview, can be viewed by right-clicking on the database name, choose Reports, Standard Reports and then select the report name from the standard reports list as shown below:
The Transaction Performance Analysis Overview report consists of two main parts: The Table Analysis for the database tables and the Stored Procedure Analysis for the database stored procedures as shown below:
To get a meaningful report, you have to run a workload on the selected database. If you choose the Tables Analysis part, a new sub-report will be opened that contains the Recommended Tables Based on Usage, that are suited to be migrated to In-Memory OLTP technology. Choosing a specific table, the new screen will show you the scan and contention statistics for that selected table. For the Stored Procedure Analysis, a list of stored procedures that are suited to be migrated to the In-Memory OLTP technology will be shown in the shape of Recommended Stored Procedures Based on Usage. Selecting a specific stored procedure, a details execution statistics about that stored procedure will be displayed.
ALTER TABLE Operations Support
Schema changes are performed on the table using the ALTER TABLE T-SQL statement. In SQL Server 2014, it is not possible to modify the Memory-Optimized table’s schema using the ALTER TABLE statement after creating that table. In order to modify a Memory-Optimized table’s schema or rebuild an index on it, you have to drop the table and recreate it again. Starting from SQL Server 2016, this restriction is no longer available, you can easily use the ALTER TABLE statement to make schema changes to the already created Memory-Optimized table.
The ALTER TABLE operations on the Memory-Optimized tables are offline. This means that the table that you are changing its schema is not available for the queries during the ALTER TABLE operation. All schema modification operations require more memory temporarily as a part of the ALTER TABLE operation, as a new copy of the table will be created under the hood. Most of the operations performed using the ALTER TABLE T-SQL statements run in parallel and are log-optimized. Which means that only the metadata changes will be written to the transaction log, rather than writing a complete copy of the table to the transaction log.
Recall the previously created InMemorySQLShackDemo testing database with the In-Memory OLTP feature enabled on it, and the created InMemoryDemo table that is considered as Memory-Optimized table. If you try to rebuild the clustered index on that Memory-Optimized table hosted in SQL Server 2014 instance using the ALTER TABLE T-SQL statement below:
|
1 2 3 |
ALTER TABLE [InMemoryDemo] ALTER INDEX [PK_IM_EmpID] REBUILD WITH (BUCKET_COUNT=200000 ) |
The query will fail as the ALTER TABLE statement on the Memory-Optimized table is not supported in the SQL Server 2014 version, as shown in the error message below:
Running the same ALTER TABLE statement but this time on the table hosted in SQL Server 2016 version:
|
1 2 3 |
ALTER TABLE InMemoryDemo ALTER INDEX [PK_IM_EmpID] REBUILD WITH (BUCKET_COUNT=200000 ) |
The index will be rebuilt successfully without any error as shown below:
Again, if we try to change the type of the EmpDEPID column from the Memory-Optimized table to be BIGINT by running the below ALTER TABLE…ALTER COLUMN T-SQL statement on the table hosted in SQL Server 2014 version:
|
1 2 3 |
ALTER TABLE InMemoryDemo ALTER COLUMN [EmpDEPID] [bigint] NOT NULL |
The query will fail, showing that the ALTER TABLE T-SQL statement is not supported with the Memory-Optimized tables as shown in the error message below:
Trying the same ALTER TABLE T-SQL statement on the table hosted in SQL Server 2016 version, the column will be modified successfully with no error as shown below:
The below changes can be also performed on the table hosted in SQL Server 2016 instance successfully using the ALTER TABLE T-SQL statement:
-
Add a new column to the Memory-Optimized table:
-
Add a new index to the Memory-Optimized table:
-
Drop an existing index on the Memory-Optimized table:
-
Drop an existing column on the Memory-Optimized table:
Foreign Key Constraint Support
In SQL Server 2014, it is not allowed to define a foreign key constraint in a Memory-Optimized table that references another table. Assume that we need to create a foreign key constraint in the InMemoryDemo table on the EmpDEPID column that references the ID column in the Department Memory-Optimized table. The Department table is created on both the SQL Server 2014 and SQL Server 2016 instances using the CREATE TABLE T-SQL statement below:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[Departments]( [ID] [int] NOT NULL CONSTRAINT PK_IM_EmpDep PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 100000), [DepName] [varchar](50) NOT NULL, ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
To create the foreign key constraint on the SQL Server 2014 and SQL Server 2016 instances, we will drop the InMemoryDemo table and create it again with the new foreign key constraint, as the ALTER TABLE statement is not supported in SQL Server 2014 as mentioned previously. This can be achieved using the DROP TABLE and CREATE TABLE T-SQL statements shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE [InMemorySQLShackDemo] GO DROP TABLE [dbo].[InMemoryDemo] GO CREATE TABLE [dbo].[InMemoryDemo]( [EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000), [EmpName] [varchar](50) NOT NULL, [EmpDEPID] [int] NOT NULL CONSTRAINT FK_EmpDepID REFERENCES DBO.Departments (ID), [EmpBirthDay] [datetime] NULL, [EmpAddress] [varchar](50) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
Trying to create the table with the foreign key constraint in the SQL Server 2014 instance, the statement will fail showing that the Foreign Key is not supported with the Memory-Optimized tables in that SQL Server version as in the below error message:
Creating the table with the foreign key constraint using the same T-SQL statements but this time in the SQL Server 2016 instance, the table will be created successfully with the foreign key constraint without any error as shown below:
The previous example shows that you can create a foreign key constraint on a Memory-Optimized table that references another Memory-Optimized table in SQL Server 2016. But can we create a foreign key constraint in a Memory-Optimized table that references a disk-based table? To check that, we will create the same previous Department table as a disk-based table using the CREATE TABLE T-SQL statement below:
|
1 2 3 4 5 6 7 8 |
USE [InMemorySQLShackDemo] GO CREATE TABLE [dbo].[Departments]( ID int, DepName varchar(50) ) |
If we try to drop and create the previous InMemoryDemo Memory-Optimized table with the foreign key that references the Department disk-based table, the query will fail in SQL Server 2016 too, showing that you can’t create a foreign key constraint in a Memory-Optimized to reference a disk-based table and vise verse as shown in the error message below:
Check / Unique Constraint Support
Another example for the In-Memory OLTP feature limitation in SQL Server 2014 is the ability to create a check or unique keys in the Memory-Optimized table. Assume that we need to add a constraint on the InMemoryDemo Memory-Optimized table to check that the EmpDEPID is always positive. This can be achieved in the SQL Server 2014 instance using the T-SQL script below that drops the table and create it again with the required check constraint, as the ALTER TABLE T-SQL statement is not supported in SQL Server 2014 version as described previously:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE [InMemorySQLShackDemo] GO DROP TABLE [dbo].[InMemoryDemo] GO CREATE TABLE [dbo].[InMemoryDemo]( [EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000), [EmpName] [varchar](50) NOT NULL, [EmpDEPID] [int] NOT NULL CONSTRAINT CK_EmpDepID CHECK ([EmpDEPID]>0), [EmpBirthDay] [datetime] NULL, [EmpAddress] [varchar](50) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
Running the previous script, the statement will fail, showing that the CHECK constraint is not supported with the Memory-Optimized tables in SQL Server 2014 as below:
The same T-SQL script will run successfully with no error in SQL Server 2016, as the check constraint is supported with the Memory-Optimized tables in that SQL Server version as you can see in the below snapshot:
Indexes on Nullable Columns Support
SQL Server 2014 In-Memory OLTP engine doesn’t support creating an index on a column that allows NULL values. If you try to drop the InMemoryDemo Memory-Optimized table and create it again with the new index on the EmpBirthday Nullable column in SQL Server 2014 instance using the T-SQL statement below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE [InMemorySQLShackDemo] GO DROP TABLE [dbo].[InMemoryDemo] CREATE TABLE [dbo].[InMemoryDemo]( [EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000), [EmpName] [varchar](50) NOT NULL, [EmpDEPID] [int] NOT NULL, [EmpBirthDay] [datetime] NULL, [EmpAddress] [varchar](50), INDEX IX_EmpBirthDa NONCLUSTERED ([EmpBirthDay] ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
The script will fail, showing that it is not allowed to create an index on a nullable column in a Memory-optimized table, as in the error message below:
On the other hand, applying the same script in the SQL Server 2016 instance will run successfully with no error with the new index on the nullable column, which is supported now in SQL Server 2016 version, as you can see below:
Large Objects (LOB) Data Types Support
SQL Server 2016 eliminates another limitation in the SQL Server 2014 In-Memory OLTP Engine that prevents the use of the Large Object (LOB) data types, such as the varchar(max), nvarchar(max) and varbinary(max), while creating the Memory-Optimized tables and the Natively Compiled Stored Procedures. Moreover, you can have rows in the Memory-Optimized tables with size that exceeds the 8060 bytes in SQL Server 2016, although it is recommended not to exceed that size for performance purposes. This is due to the fact that the columns that exceeds the 8060 bytes in-row storage will be stored off-row, with 8-byte in-row reference to the off-row data and separate internal table for each off-row column.
Assume that we need to modify the data type of the EmpName column from the InMemoryDemo Memory-Optimized table to be VARCHAR(MAX) data type by dropping the table and creating it again using the T-SQL script below in the SQL Server 2014 instance:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
USE [InMemorySQLShackDemo] GO DROP TABLE [dbo].[InMemoryDemo] CREATE TABLE [dbo].[InMemoryDemo]( [EmpID] [int] NOT NULL CONSTRAINT PK_IM_EmpID PRIMARY KEY NONCLUSTERED HASH (EmpID) WITH (BUCKET_COUNT = 100000), [EmpName] [varchar](MAX) NOT NULL, [EmpAddress] [varchar](50) NOT NULL, [EmpDEPID] [int] NOT NULL , [EmpBirthDay] [datetime] NULL, INDEX IX_EmpBirthDa NONCLUSTERED ([EmpBirthDay]) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) |
The previous script will fail, telling you that the VARCHAR(MAX) data type in not supported with the Memory-Optimized tables as in the error message below:
While the same T-SQL script will run successfully in SQL Server 2016 instance that supports the VARCHAR(MAX) data type with the Memory-Optimized table as shown below:
More Enhancements
- SQL Server 2016 Natively Compiled Stored Procedures support additional T-SQL statements such as: LEFT OUTER JOIN, RIGHT OUTER JOIN, SELECT DISTINCT, OR, NOT, IN and EXISTS operations, UNION and UNION ALL, built-in math functions, Subqueries, Nested stored procedure calls and the OUTPUT clause.
- SQL Server 2016 Query Optimizer creates parallel plans against the Memory-Optimized tables for specific operations that use a hash index.
- In SQL Server 2016, the Transparent Data Encryption (TDE) feature is compatible with the Memory-Optimized tables. This means that the data stored in the Memory-Optimized table will be encrypted in the disk.
- The Memory-Optimized tables data visibility delay in the Always On Availability Group secondary replica is no longer available in SQL Server 2016, that allows the user to view the data directly in the secondary replica.
- The maximum size of memory recommended by Microsoft to store the durable Memory-Optimized tables, that will be used during the recovery process, is increased in SQL Server 2016 to be 2 TB, instead of the 256 GB recommendation in SQL Server 2014. The recommended memory size is boosted up due to the fact that the number of checkpoint files that are used to store the data from the Memory-Optimized tables are increased.
- You are able to create a Columnstore index on a Memory-Optimized table, providing a very useful enhancement resulting from combining these two great features. You can imagine the combination performance gain and how we will benefit from leveraging these capabilities.
- The Filestream feature is supported now with the In-Memory OLTP feature in SQL Server 2016.
- The ALTER PROCEDURE and sp_recompile T-SQL statements are supported now with the In-Memory Optimized OLTP feature in SQL Server 2016.
- The Memory-Optimized tables’ statistics are updated automatically in SQL Server 2016, without the need to update the statistics manually.
- The Memory-Optimized tables’ statistics sampling is also supported in SQL Server 2016, improving the statistics collection performance.
Conclusion
SQL Server In-Memory OLTP feature was introduced for the first time in SQL Server 2014 version and considered a big step forward in Microsoft SQL Server product performance. It allows us to move all the table data to memory and query it directly from there with the fastest possible speed. The SQL Server 2014 In-Memory OLTP engine comes with number of limitations. In SQL Server 2016, many of these limitations are removed. In this article, we described practically most of these enhancements that are performed on the In-Memory OLTP engine in SQL Server 2016.
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