Edit

Requirements for Using Memory-Optimized Tables

  • Article

Applies to: SQL Server

For using In-Memory OLTP in Azure DB see Get started with In-Memory in SQL Database.

In addition to the Hardware and Software Requirements for Installing SQL Server, the following are requirements to use In-Memory OLTP:

  • SQL Server 2016 (13.x) SP1 (or later), any edition. For SQL Server 2014 (12.x) and SQL Server 2016 (13.x) RTM (pre-SP1) you need Enterprise, Developer, or Evaluation edition.

    Note

    In-Memory OLTP requires the 64-bit version of SQL Server.

  • SQL Server needs enough memory to hold the data in memory-optimized tables and indexes, as well as additional memory to support the online workload. See Estimate Memory Requirements for Memory-Optimized Tables for more information.

  • When running SQL Server in a Virtual Machine (VM), ensure there is enough memory allocated to the VM to support the memory needed for memory-optimized tables and indexes. Depending on the VM host application, the configuration option to guarantee memory allocation for the VM could be called Memory Reservation or, when using Dynamic Memory, Minimum RAM. Make sure these settings are sufficient for the needs of the databases in SQL Server.

  • Free disk space that is two times the size of your durable memory-optimized tables.

  • A processor needs to support the instruction cmpxchg16b to use In-Memory OLTP. All modern 64-bit processors support cmpxchg16b.

    If you are using a Virtual Machine and SQL Server displays an error caused by an older processor, see if the VM host application has a configuration option to allow cmpxchg16b. If not, you could use Hyper-V, which supports cmpxchg16b without needing to modify a configuration option.

  • In-Memory OLTP is installed as part of Database Engine Services.

    To install report generation (Determining if a Table or Stored Procedure Should Be Ported to In-Memory OLTP) and SQL Server Management Studio (to manage In-Memory OLTP via SQL Server Management Studio Object Explorer), download SQL Server Management Studio (SSMS).

Important Notes on using In-Memory OLTP

  • Starting SQL Server 2016 (13.x), there is no limit on the size of memory-optimized tables, other than available memory.

  • In SQL Server 2014 (12.x), the total in-memory size of all durable tables in a database should not exceed 250 GB. For more information, see Estimate Memory Requirements for Memory-Optimized Tables.

Note

Starting SQL Server 2016 (13.x) SP1, Standard and Express Editions support In-Memory OLTP, but they impose quotas on the amount of memory you can use for memory-optimized tables in a given database. In Standard edition this is 32GB per database; in Express edition this is 352MB per database.

  • If you create one or more databases with memory-optimized tables, you should enable Instant File Initialization (IFI) by granting the SQL Server service startup account the SE_MANAGE_VOLUME_NAME user right. Without IFI, memory-optimized storage files (data and delta files) will be initialized upon creation, which can have negative impact on the performance of your workload. For more information about IFI, including how to enable it, see Database Instant File Initialization.

See Also

In-Memory OLTP (In-Memory Optimization)
Database Instant File Initialization
Memory Architecture guide