Limitations of Memory Optimized Tables in SQL Server 2016

As we know SQL Server is a relational database management system (RDBMS) that evolved by Microsoft.  SQL Server was initially designed and developed to compete with MySQL and Oracle database. SQL Server helps ANSI SQL, which is the standard SQL language. However, SQL Server comes with its own execution of the SQL. In this article, we are going to discuss the Reason, Resolution and the Limitations of Memory-Optimized tables in SQL Server 2016.

 Introduction to Memory-Optimized Tables 

The in-memory OLTP feature was introduced with SQL Server 2014 and contains 2 parts. The main advantage of memory-optimized tables is that rows in the table are read from and written to memory which results in non-blocking transactions at super-fast speed.

The second copy of the data is stored on the disk and during database recovery, data is read from the disk-based table. These tables are for particular types of workloads such as large volume OLTP applications.

As we know that transaction isolation levels in SQL Server apply differently to memory-optimized tables and disk-based tables, and the basic mechanisms are different.

An understanding of the distinctions helps the programmer design a high throughput system. The aim of transaction integrity is shared in all scenarios.  

Limitations of Memory-optimized tables in SQL Server 2016

• It doesn’t support Cross-database transactions. If a transaction accesses an optimized table, the transaction cannot access any other database, apart from the tempDB database and Read-only from the master database.
• It doesn’t support Distributed transactions and when it is used the transaction cannot access a memory-optimized table.
• Doesn’t support LOB Data Types, XML, CLR and MAX Data Types.
• NO ALTER TABLE and CREATE, ALTER, DROP INDEX
• Doesn’t support Foreign Key and Check Constraints.
• Doesn’t  support Identity Columns 
• No Unique indexes other than for the Primary Key Column
•  Maximum Size for Table Row is 8060 bytes, it is enforced during Table Creation as well.
• Doesn’t support DML Triggers for Memory-Optimized Table
• Memory Optimized Table needs to have at least one Index. Optimized Tables doesn’t use 8 KB Data Pages from the extent to store the table data, Instead, it has a Special Row Structure and Indexes which are used to connect the rows to get the complete Data.
• The Supported Index Types are Non-Clustered HASH or RANGE Indexes.
  
• A Table Can Have Maximum 8 Indexes.

There may be chances of error “Access to the Remote Server is Denied“. Don’t worry still you can fix it.

 Transaction Phases and Lifetime

When an optimized table is involved, the lifetime of a transaction progresses through three phases.

• Regular Processing: This phase is composed of the implementation of all queries and DML statements in the query. During this phase, the statements see the version of the optimized tables as of the logical beginning time of the transaction.
• Validation-This phase starts by allocating the end time, thereby marking the transaction as logically complete. This completion makes all changes to the transaction detectable to other transactions.
• Commit Processing: During this phase, the variations to durable tables are written to the log, and the log is written to disk. Then control is returned to the client.

            After commit processing ends, all dependent transactions are pointed out that they can commit.

Error while running a query

 When we have tried to execute a query accessing the table and disk-based table, we have received an error.

In the above script, one table is memory optimized and the second one is a disk-based table. When we ran the script it popped up this error. 

Why did this error occur?

We have received this error because in SQL Server In-Memory OLTP when we have access to any disk-based and memory optimized table in the single transactions, it gives an error as it’s not directly supported unless there is a change in the isolation of the database or query. 

How can we resolve the Limitations of memory-optimized tables in SQL server 2016?

Let’s discuss the solution to the above error.

By changing the isolation level for the database To MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT the error will disappear and the SQL Server will support implicit or explicit transactions cross-container.

 

 

Related Post