In-Memory Tables in SQL Server: Benefits, Limitations, and How to Identify Them

by | Jul 27, 2023 | Blog, SQL Server

In-Memory Tables in SQL Server- Benefits_ Limitations_ and How to Identify Them

If you are looking to improve the speed of transactions, especially for high-concurrency workloads that involve a lot of inserts, update, and delete operations, you might benefit from implementing In-Memory OLTP and experience up to 30X performance gain, as some have seen. However, the performance gain depends on the workload.

In this article, we will discuss what this SQL Server In-Memory OLTP technology is, when to use them, what are the benefits, and highlight the potential risks and challenges you might be facing while implementing the In-Memory OLTP Table feature in your SQL Server.

What Are In-Memory Tables in SQL Server?

SQL Server &In-Memory OLTP (Online Transaction Processing) is a premier technology available in SQL Server and SQL Database for optimizing the performance of transaction processing, data ingestion, data load, and transient data scenarios. This feature allows faster performance of certain types of workloads in SQL Server; by storing data and indexes in memory rather than on disk. This can significantly improve the speed of transactions, especially for high-concurrency workloads that involve a lot of inserts, update, and delete operations.

In-Memory OLTP was first introduced in SQL Server 2014, so this is the minimum requirement for In-Memory OLTP. It can be used in all editions, including Express, Standard, and Enterprise. In-Memory engine provides tables and indexes on which no latches or locks are required during concurrent access. It also provides natively compiled stored procedures.

When Should You Consider Migrating to In-Memory Tables in SQL Server?

In-Memory OLTP improves the performance of transaction processing by making data access and transaction execution more efficient and by removing the lock and latch contention between concurrently executing transactions. In-Memory OLTP isn’t fast because it’s in memory; it’s fast because it’s optimized around the data being In-Memory. It should be noted that In-Memory OLTP is not a replacement for traditional disk-based tables. It should only be used for specific workloads that will benefit from increased performance. In other words, if you have transactional tables with high-volume WRITEs that involve many inserts, updates, and deletes, not a data warehouse mostly for reads, In-Memory OLTP could be a very beneficial feature for you.

What Are the Benefits of In-Memory Tables?

In-Memory OLTP is built into SQL Server and SQL Database. Because these objects behave similarly to their traditional counterparts, you can often gain performance benefits while making only minimal changes to the database and the application. Plus, you can have both memory-optimized and traditional disk-based tables in the same database and run queries across the two.

According to Microsoft, SQL Server uses an optimistic approach where it does not place locks or latches on any version of updated rows of data, which is very different from normal tables. It’s this mechanism that reduces contention and allows the transactions to process exponentially faster. Instead of locks, In-Memory uses Row Versions, keeping the original row until after the transaction is committed. This allows other transactions to read the original row while updating the new row version. The In-Memory structured version is pageless and optimized for speed inside active memory, giving a significant performance impact depending on workloads.

What Are the Limitations of In-Memory Tables?

In-Memory OLTP supposedly increases performance; however, some features of Disk-based tables are not available in In-Memory tables. Like all SQL Server options, this is not meant for all environments. Many T-SQL operators and functionalities, such as OR, LIKE, BETWEEN, and OUTER JOIN, are not available, which limits developers and business logic in stored procedures. For a full list of unsupported features and syntax to consider, be sure to check out the documentation. Below are just a few.

Features not supported for In-Memory tables to keep in mind:

  • Replication
  • Mirroring
  • Linked Servers
  • Bulk Logging
  • DDL Triggers
  • Minimal Logging
  • Change Data Capture
  • Data Compression

T-SQL not supported in In-Memory Tables:

  • Foreign Keys (Can only reference other Memory Optimized Table PKs)
  • ALTER TABLE
  • CREATE INDEX
  • TRUNCATE TABLE
  • DBCC CHECKTABLE
  • DBCC CHECKDB

How to Identify if In-Memory Tables Are Being Used?

To identify if a certain database has any tables that are In-Memory, the following query can be used. This will return a list of all tables that are In-Memory within that database.

SELECT *
FROM sys.tables
WHERE is_memory_optimized = 1

You can also use the following query to get a list of the In-Memory Tables that would have more information on the tables, including their durability.

SELECT SCHEMA_NAME(Schema_id) SchemaName,
name TableName,
is_memory_optimized,
durability_desc,
create_date, modify_date
FROM sys.tables
GO

For any questions about our SQL Server Virtual-DBA Services or for more information, please contact us!