SQL Server In-Memory OLTP Overview – 1

In-Memory OLTP can provide great performance gains, for the right workloads. One customer, BWIN, managed to achieve 1.2 Million requests per second with a single machine running SQL Server 2016 (13.x), leveraging In-Memory OLTP. Another customer, Quorum, managed to double their workload while reducing their resource utilization by 70%, by leveraging In-Memory OLTP in SQL Database. While customers have seen up to 30X performance gain in some cases, how much gain you see depends on the workload.

Now, where does this performance gain come from? In essence, In-Memory OLTP improves performance of transaction processing by making data access and transaction execution more efficient, and by removing lock and latch contention between concurrently executing transactions: it is not fast because it is in-memory; it is fast because it is optimized around the data being in-memory. Data storage, access, and processing algorithms were redesigned from the ground up to take advantage of the latest enhancements in in-memory and high concurrency computing.

Now, just because data lives in-memory does not mean you lose it when there is a failure. By default, all transactions are fully durable, meaning that you have the same durability guarantees you get for any other table in SQL Server: as part of transaction commit, all changes are written to the transaction log on disk. If there is a failure at any time after the transaction commits, your data is there when the database comes back online. In addition, In-Memory OLTP works with all high availability and disaster recovery capabilities of SQL Server, like Always On, backup/restore, etc.

To leverage In-Memory OLTP in your database, you use one or more of the following types of objects:

  • Memory-optimized tables are used for storing user data. You declare a table to be memory-optimized at create time.
  • Non-durable tables are used for transient data, either for caching or for intermediate result set (replacing traditional temp tables). A non-durable table is a memory-optimized table that is declared with DURABILITY=SCHEMA_ONLY, meaning that changes to these tables do not incur any IO. This avoids consuming log IO resources for cases where durability is not a concern.
  • Memory-optimized table types are used for table-valued parameters (TVPs), as well as intermediate result sets in stored procedures. These can be used instead of traditional table types. Table variables and TVPs that are declared using a memory-optimized table type inherit the benefits of non-durable memory-optimized tables: efficient data access, and no IO.
  • Natively compiled T-SQL modules are used to further reduce the time taken for an individual transaction by reducing CPU cycles required to process the operations. You declare a Transact-SQL module to be natively compiled at create time. At this time, the following T-SQL modules can be natively compiled: stored procedures, triggers, and scalar user-defined functions.

In-Memory OLTP is built into SQL Server and SQL Database. And because these objects behave similar 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. You find a Transact-SQL script showing an example for each of these types of objects towards the bottom of this article.

Usage Scenarios for In-Memory OLTP

In-Memory OLTP is not a magic go-fast button, and is not suitable for all workloads. For example, memory-optimized tables don’t bring down your CPU utilization if most of the queries are performing aggregation over large ranges of data – Columnstore indexes help with that scenario.

Here is a list of scenarios and application patterns where we have seen customers be successful with In-Memory OLTP.

High-throughput and low-latency transaction processing

This is the core scenario for which we built In-Memory OLTP: support large volumes of transactions, with consistent low latency for individual transactions.

Common workload scenarios are: trading of financial instruments, sports betting, mobile gaming, and ad delivery. Another common pattern we’ve seen is a “catalog” that is frequently read and/or updated. One example is where you have large files, each distributed over a number of cluster nodes, and you catalog the location of each shard of each file in a memory-optimized table.

Implementation considerations

Use memory-optimized tables for your core transaction tables, i.e., the tables with the most performance-critical transactions. Use natively compiled stored procedures to optimize execution of the logic associated with the business transaction. The more of the logic you can push down into stored procedures in the database, the more benefit you see from In-Memory OLTP.

To get started in an existing application:

  1. use the transaction performance analysis report to identify the objects you want to migrate,
  2. and use the memory-optimization and native compilation advisors to help with migration.

Customer Case Studies

  • CMC Markets leverages In-Memory OLTP in SQL Server 2016 (13.x) to achieve consistent low latency: Because a second is too long to wait, this financial services firm is updating its trading software now.
  • Derivco leverages In-Memory OLTP in SQL Server 2016 (13.x) to support increased throughput and handle spikes in the workload.

Data ingestion, including IoT (Internet-of-Things)

In-Memory OLTP is good at ingesting large volumes of data from many different sources at the same time. And it is often beneficial to ingest data into a SQL Server database compared with other destinations, because SQL Server makes running queries against the data fast, and allows you to get real-time insights.

Common application patterns are:

  • Ingesting sensor readings and events, and allow notifications as well as historical analysis.
  • Managing batch updates, even from multiple sources, while minimizing the impact on the concurrent read workload.

Caching and session state

The In-Memory OLTP technology makes SQL really attractive for maintaining session state (e.g., for an ASP.NET application) and for caching.

ASP.NET session state is a very successful use case for In-Memory OLTP. With SQL Server, one customer was about to achieve 1.2 Million requests per second. In the meantime, they have started using In-Memory OLTP for the caching needs of all mid-tier applications in the enterprise.

Tempdb object replacement

Leverage non-durable tables and memory-optimized table types to replace your traditional TempDB based structures, such as temporary tables, table variables, and table-valued parameters (TVPs).

Memory-optimized table variables and non-durable tables typically reduce CPU and completely remove log IO, when compared with traditional table variables and #temp table.

ETL (Extract Transform Load)

ETL workflows often include load of data into a staging table, transformations of the data, and load into the final tables.

Sample Script

Before you can start using In-Memory OLTP, you need to create a MEMORY_OPTIMIZED_DATA filegroup. In addition, we recommend using database compatibility level 130 (or higher), and set the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to ON.

You can use the script at the following location to create the filegroup in the default data folder, and configure the recommended settings:

  • enable-in-memory-oltp.sql

The following script illustrates In-Memory OLTP objects you can create in your database:

SQL

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON)
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY)
GO
-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON)
GO
-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')
 
  DECLARE @i INT = 1
 
  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0
 
    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1
 
      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END
 
END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0)
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1)
EXECUTE dbo.usp_ingest_table1 @[email protected]
SELECT c1, c2 from dbo.table1
SELECT c1, c2 from dbo.temp_table1
GO
 

Introduction to Memory-Optimized Tables

Memory-optimized tables are fully durable by default, and, like transactions on (traditional) disk-based tables, transactions on memory-optimized tables are fully atomic, consistent, isolated, and durable (ACID). Memory-optimized tables and natively compiled stored procedures support only a subset of Transact-SQL features.

Starting with SQL Server 2016, and in Azure SQL Database, there are no limitations for collations or code pages that are specific to In-Memory OLTP.

The primary storage for memory-optimized tables is the main memory. Rows in the table are read from and written to memory. A second copy of the table data is maintained on disk, but only for durability purposes. See Creating and Managing Storage for Memory-Optimized Objects for more information about durable tables. Data in memory-optimized tables is only read from disk during database recovery (eg. after a server restart).

For even greater performance gains, In-Memory OLTP supports durable tables with transaction durability delayed. Delayed durable transactions are saved to disk soon after the transaction has committed and control was returned to the client. In exchange for the increased performance, committed transactions that have not saved to disk are lost in a server crash or fail over.

Besides the default durable memory-optimized tables, SQL Server also supports non-durable memory-optimized tables, which are not logged and their data is not persisted on disk. This means that transactions on these tables do not require any disk IO, but the data will not be recovered if there is a server crash or failover.

In-Memory OLTP is integrated with SQL Server to provide a seamless experience in all areas such as development, deployment, manageability, and supportability. A database can contain in-memory as well as disk-based objects.

Rows in memory-optimized tables are versioned. This means that each row in the table potentially has multiple versions. All row versions are maintained in the same table data structure. Row versioning is used to allow concurrent reads and writes on the same row.

The following figure illustrates multi-versioning. The figure shows a table with three rows and each row has different versions.

The table has three rows: r1, r2, and r3. r1 has three versions, r2 has two versions, and r3 has four versions. Note that different versions of the same row do not necessarily occupy consecutive memory locations. The different row versions can be dispersed throughout the table data structure.

The memory-optimized table data structure can be seen as a collection of row versions. Rows in disk-based tables are organized in pages and extents, and individual rows addressed using page number and page offset, row versions in memory-optimized tables are addressed using 8-byte memory pointers.

Data in memory-optimized tables is accessed in two ways:

  • Through natively compiled stored procedures.
  • Through interpreted Transact-SQL, outside of a natively-compiled stored procedure. These Transact-SQL statements may be either inside interpreted stored procedures or they may be ad-hoc Transact-SQL statements.

Accessing Data in Memory-Optimized Tables

Memory-optimized tables can be accessed most efficiently from natively compiled stored procedures. Memory-optimized tables can also be accessed with (traditional) interpreted Transact-SQL. Interpreted Transact-SQL refers to accessing memory-optimized tables without a natively compiled stored procedure. Some examples of interpreted Transact-SQL access include accessing a memory-optimized table from a DML trigger, ad hoc Transact-SQL batch, view, and table-valued function.

The following table summarizes native and interpreted Transact-SQL access for various objects.

ACCESSING DATA IN MEMORY-OPTIMIZED TABLES
FeatureAccess Using a Natively Compiled Stored ProcedureInterpreted Transact-SQL AccessCLR Access
Memory-optimized tableYesYesNo*
Memory-optimized table typeYesYesNo
Natively compiled stored procedureNesting of natively compiled stored procedures is now supported. You can use the EXECUTE syntax inside the stored procedures, as long as the referenced procedure is also natively compiled.YesNo*

*You cannot access a memory-optimized table or natively compiled stored procedure from the context connection (the connection from SQL Server when executing a CLR module). You can, however, create and open another connection from which you can access memory-optimized tables and natively compiled stored procedures.

Performance and Scalability

The following factors will affect the performance gains that can be achieved with In-Memory OLTP:

Communication: An application with many calls to short stored procedures may see a smaller performance gain compared to an application with fewer calls and more functionality implemented in each stored procedure.

Transact-SQL Execution: In-Memory OLTP achieves the best performance when using natively compiled stored procedures rather than interpreted stored procedures or query execution. There can be a benefit to accessing memory-optimized tables from such stored procedures.

Range Scan vs Point Lookup: Memory-optimized nonclustered indexes support range scans and ordered scans. For point lookups, memory-optimized hash indexes have better performance than memory-optimized nonclustered indexes. Memory-optimized nonclustered indexes have better performance than disk-based indexes.

  • Starting in SQL Server 2016, the query plan for a memory-optimized table can scan the table in parallel. This improves the performance of analytical queries.
    • Hash indexes also became scannable in parallel in SQL Server 2016.
    • Nonclustered indexes also became scannable in parallel in SQL Server 2016.
    • Columnstore indexes have been scannable in parallel since their inception in SQL Server 2014.

Index operations: Index operations are not logged, and they exist only in memory.

Concurrency: Applications whose performance is affected by engine-level concurrency, such as latch contention or blocking, improves significantly when the application moves to In-Memory OLTP.

The following table lists the performance and scalability issues that are commonly found in relational databases and how In-Memory OLTP can improve performance.

IssueIn-Memory OLTP Impact
Performance

High resource (CPU, I/O, network or memory) usage.
CPU
Natively compiled stored procedures can lower CPU usage significantly because they require significantly fewer instructions to execute a Transact-SQL statement compared to interpreted stored procedures.

In-Memory OLTP can help reduce the hardware investment in scaled-out workloads because one server can potentially deliver the throughput of five to ten servers.

I/O
If you encounter an I/O bottleneck from processing to data or index pages, In-Memory OLTP may reduce the bottleneck. Additionally, the checkpointing of In-Memory OLTP objects is continuous and does not lead to sudden increases in I/O operations. However, if the working set of the performance critical tables does not fit in memory, In-Memory OLTP will not improve performance because it requires data to be memory resident. If you encounter an I/O bottleneck in logging, In-Memory OLTP can reduce the bottleneck because it does less logging. If one or more memory-optimized tables are configured as non-durable tables, you can eliminate logging for data.

Memory
In-Memory OLTP does not offer any performance benefit. In-Memory OLTP can put extra pressure on memory as the objects need to be memory resident.

Network
In-Memory OLTP does not offer any performance benefit. The data needs to be communicated from data tier to application tier.
Scalability

Most scaling issues in SQL Server applications are caused by concurrency issues such as contention in locks, latches, and spinlocks.
Latch Contention
A typical scenario is contention on the last page of an index when inserting rows concurrently in key order. Because In-Memory OLTP does not take latches when accessing data, the scalability issues related to latch contentions are fully removed.

Spinlock Contention
Because In-Memory OLTP does not take latches when accessing data, the scalability issues related to spinlock contentions are fully removed.

Locking Related Contention
If your database application encounters blocking issues between read and write operations, In-Memory OLTP removes the blocking issues because it uses a new form of optimistic concurrency control to implement all transaction isolation levels. In-Memory OLTP does not use TempDB to store row versions.

If the scaling issue is caused by conflict between two write operations, such as two concurrent transactions trying to update the same row, In-Memory OLTP lets one transaction succeed and fails the other transaction. The failed transaction must be re-submitted either explicitly or implicitly, re-trying the transaction. In either case, you need to make changes to the application.

If your application experiences frequent conflicts between two write operations, the value of optimistic locking is diminished. The application is not suitable for In-Memory OLTP. Most OLTP applications don’t have a write conflicts unless the conflict is induced by lock escalation.

Memory Optimization Advisor

The memory-optimization advisor allows you to:

  • Identify any features used in a disk-based table that are not supported for memory-optimized tables.
  • Migrate a table and data to memory-optimized (if there are no unsupported features).

Walkthrough Using the Memory-Optimization Advisor

In Object Explorer, right click the table you want to convert, and select Memory-Optimization Advisor. This will display the welcome page for the Table Memory Optimization Advisor.

Memory Optimization Checklist

When you click Next in the welcome page for the Table Memory Optimization Advisor, you will see the memory optimization checklist. Memory-optimized tables do not support all the features in a disk-based table. The memory optimization checklist reports if the disk-based table uses any features that are incompatible with a memory-optimized table. The Table Memory Optimization Advisor does not modify the disk-based table so that it can be migrated to use In-Memory OLTP. You must make those changes before continuing migration. For each incompatibility found, the Table Memory Optimization Advisor displays a link to information that can help you modify your disk-based tables.

If you wish to keep a list of these incompatibilities, to plan your migration, click the Generate Report to generate a HTML list.

If your table has no incompatibilities and you are connected to a SQL Server 2014 (12.x) instance with In-Memory OLTP, click Next.

Review Optimization Options

The next screen lets you modify options for the migration to In-Memory OLTP:

Memory-optimized filegroup
The name for your memory-optimized filegroup. A database must have a memory-optimized filegroup with at least one file before a memory-optimized table can be created.

If you do not have a memory-optimized filegroup, you can change the default name. Memory-optimized filegroups cannot be deleted. The existence of a memory-optimized filegroup may disable some database-level features such as AUTO CLOSE and database mirroring.

If a database already has a memory-optimized file group, this field will be pre-populated with its name and you will not be able to change the value of this field.

Logical file name and File path
The name of the file that will contain the memory-optimized table. A database must have a memory-optimized file group with at least one file before a memory-optimized table can be created.

If you do not have an existing memory-optimized file group, you can change the default name and path of the file to be created at the end of the migration process.

If you have an existing memory-optimized filegroup, these fields will be pre-populated and you will not be able to change the values.

Rename the original table as
At the end of the migration process, a new memory-optimized table will be created with the current name of the table. To avoid a name conflict, the current table must be renamed. You may change that name in this field.

Estimated current memory cost (MB)
The Memory-Optimization Advisor estimates the amount of memory the new memory-optimized table will consume based on metadata of the disk-based table.

If sufficient memory is not allotted, the migration process may fail.

Also copy table data to the new memory optimized table
Select this option if you wish to also move the data in the current table to the new memory-optimized table. If this option is not selected, the new memory-optimized table will be created with no rows.

The table will be migrated as a durable table by default
In-Memory OLTP supports non-durable tables with superior performance compared to durable memory-optimized tables. However, data in a non-durable table will be lost upon server restart.

If this option is selected, the Memory-Optimization Advisor will create a non-durable table instead of a durable table.

Review Primary Key Conversion

The next screen is Review Primary Key Conversion. The Memory-Optimization Advisor will detect if there are one or more primary keys in the table, and populates the list of columns based on the primary key metadata. Otherwise, if you wish to migrate to a durable memory-optimized table, you must create a primary key.

If a primary key doesn’t exist and the table is being migrated to a non-durable table, this screen will not appear.

For textual columns (columns with types charncharvarchar, and nvarchar) you must select an appropriate collation. In-Memory OLTP only supports BIN2 collations for columns on a memory-optimized table and it does not support collations with supplementary characters.

You can configure the following parameters for the primary key:

Select a new name for this primary key
The primary key name for this table must be unique inside the database. You may change the name of the primary key here.

Select the type of this primary key
In-Memory OLTP supports two types of indexes on a memory-optimized table:

  • A NONCLUSTERED HASH index. This index is best for indexes with many point lookups. You may configure the bucket count for this index in the Bucket Count field.
  • A NONCLUSTERED index. This type of index is best for indexes with many range queries. You may configure the sort order for each column in the Sort column and order list.

Click Next after you make your primary key choices.

Review Index Conversion

The next page is Review Index Conversion. The Memory-Optimization Advisor will detect if there are one or more indexes in the table, and populates the list of columns and data type. The parameters you can configure in the Review Index Conversion page are similar to the previous, Review Primary Key Conversion page.

If the table only has a primary key and it’s being migrated to a durable table, this screen will not appear.

After you make a decision for every index in your table, click Next.

Verify Migration Actions

The next page is Verify Migration Actions. To script the migration operation, click Script to generate a Transact-SQL script. You may then modify and execute the script. Click Migrate to begin the table migration.

After the process is finished, refresh Object Explorer to see the new memory-optimized table and the old disk-based table. You can keep the old table or delete it at your convenience.

Native Compilation Advisor

Transaction Performance Analysis reports tells you which interpreted stored procedures in your database will benefit if ported to use native compilation.

After you identify a stored procedure that you would like to port to use native compilation, you can use the Native Compilation Advisor (NCA) to help you migrate the interpreted stored procedure to native compilation.

In a given interpreted stored procedure, the NCA allows you to identify all the features that are not supported in native modules. The NCA provides documentation links to work-arounds or solutions.

Walkthrough Using the Native Compilation Advisor

In Object Explorer, right click the stored procedure you want to convert, and select Native Compilation Advisor. This will display the welcome page for the Stored Procedure Native Compilation Advisor. Click Next to continue.

Stored Procedure Validation

This page will report if the stored procedure uses any constructs that are not compatible with native compilation. You can click Next to see details. If there are constructs that are not compatible with native compilation, you can click Next to see details.

Stored Procedure Validation Result

If there are constructs that are not compatible with native compilation, the Stored Procedure Validation Result page will display details. You can generate a report (click Generate Report), exit the Native Compilation Advisor, and update your code so that it is compatible with native compilation.

Code Sample

The following sample shows an interpreted stored procedure and the equivalent stored procedure for native compilation. The sample assumes a directory called c:\data.

SQL

CREATE DATABASE Demo 

ON 

PRIMARY(NAME = [Demo_data], 

FILENAME = ‘C:\DATA\Demo_data.mdf’, size=500MB) 

, FILEGROUP [Demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA( 

NAME = [Demo_dir], 

FILENAME = ‘C:\DATA\Demo_dir’) 

LOG ON (name = [Demo_log], Filename=’C:\DATA\Demo_log.ldf’, size=500MB) 

COLLATE Latin1_General_100_BIN2; 

go 

USE Demo; 

go 

CREATE TABLE [dbo].[SalesOrders] 

     [order_id] [int] NOT NULL, 

     [order_date] [datetime] NOT NULL, 

     [order_status] [tinyint] NOT NULL 

     CONSTRAINT [PK_SalesOrders] PRIMARY KEY NONCLUSTERED HASH  

     [order_id] 

) WITH ( BUCKET_COUNT = 2097152) 

) WITH ( MEMORY_OPTIMIZED = ON ) 

go 

— Interpreted. 

CREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME2, @status TINYINT 

AS  

BEGIN  

  INSERT dbo.SalesOrders VALUES (@id, @date, @status); 

END 

go 

— Natively Compiled. 

CREATE PROCEDURE [dbo].[InsertOrderXTP] 

      @id INT, @date DATETIME2, @status TINYINT 

  WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 

AS  

BEGIN ATOMIC WITH  

     (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’ 

     ) 

  INSERT dbo.SalesOrders VALUES (@id, @date, @status); 

END 

go 

SELECT * from SalesOrders; 

go 

EXECUTE dbo.InsertOrder @id= 10, @date = ‘1956-01-01 12:00:00’, @status = 1; 

EXECUTE dbo.InsertOrderXTP @id= 11, @date = ‘1956-01-01 12:01:00’, @status = 2; 

SELECT * from SalesOrders; 

Estimate Memory Requirements for Memory-Optimized Tables

Memory-optimized tables require that sufficient memory exist to keep all of the rows and indexes in memory. Because memory is a finite resource, it is important that you understand and manage memory usage on your system. The topics in this section cover common memory use and management scenarios.

Whether you are creating a new memory-optimized table or migrating an existing disk-based table to an In-Memory OLTP memory-optimized table, it is important to have a reasonable estimate of each table’s memory needs so you can provision the server with sufficient memory. This section describes how to estimate the amount of memory that you need to hold data for a memory-optimized table.

Basic Guidance for Estimating Memory Requirements

Starting with SQL Server 2016 (13.x), there is no limit on the size of memory-optimized tables, though the tables do need to fit in memory. In SQL Server 2014 (12.x) the supported data size is 256GB for SCHEMA_AND_DATA tables.

The size of a memory-optimized table corresponds to the size of data plus some overhead for row headers. When migrating a disk-based table to memory-optimized, the size of the memory-optimized table will roughly correspond to the size of the clustered index or heap of the original disk-based table.

Indexes on memory-optimized tables tend to be smaller than nonclustered indexes on disk-based tables. The size of nonclustered indexes is in the order of [primary key size] * [row count]. The size of hash indexes is [bucket count] * 8 bytes.

When there is an active workload, additional memory is needed to account for row versioning and various operations. How much memory is needed in practice depends on the workload, but to be safe the recommendation is to start with two times the expected size of memory-optimized tables and indexes, and observe what are the memory requirements in practice. The overhead for row versioning always depends on the characteristics of the workload – especially long-running transactions increase the overhead. For most workloads using larger databases (e.g., >100GB), overhead tends to be limited (25% or less).

Detailed Computation of Memory Requirements

Example memory-optimized table

Consider the following memory-optimized table schema:

SQL

CREATE TABLE t_hk
  col1 int NOT NULL  PRIMARY KEY NONCLUSTERED,  
 
  col2 int NOT NULL  INDEX t1c2_index   
      HASH WITH (bucket_count = 5000000),  
 
  col3 int NOT NULL  INDEX t1c3_index   
      HASH WITH (bucket_count = 5000000),  
 
  col4 int NOT NULL  INDEX t1c4_index   
      HASH WITH (bucket_count = 5000000),  
 
  col5 int NOT NULL  INDEX t1c5_index NONCLUSTERED,  
 
  col6 char (50) NOT NULL
  col7 char (50) NOT NULL,   
  col8 char (30) NOT NULL,   
  col9 char (50) NOT NULL  
 
)   WITH (memory_optimized = on)  ;
GO  

Using this schema we will determine the minimum memory needed for this memory-optimized table.

Memory for the table

A memory-optimized table row is comprised of three parts:

  • Timestamps
    Row header/timestamps = 24 bytes.
  • Index pointers
    For each hash index in the table, each row has an 8-byte address pointer to the next row in the index. Since there are 4 indexes, each row will allocate 32 bytes for index pointers (an 8 byte pointer for each index).
  • Data
    The size of the data portion of the row is determined by summing the type size for each data column. In our table we have five 4-byte integers, three 50-byte character columns, and one 30-byte character column. Therefore the data portion of each row is 4 + 4 + 4 + 4 + 4 + 50 + 50 + 30 + 50 or 200 bytes.

The following is a size computation for 5,000,000 (5 million) rows in a memory-optimized table. The total memory used by data rows is estimated as follows:

Memory for the table’s rows

From the above calculations, the size of each row in the memory-optimized table is 24 + 32 + 200, or 256 bytes. Since we have 5 million rows, the table will consume 5,000,000 * 256 bytes, or 1,280,000,000 bytes – approximately 1.28 GB.

Memory for indexes

Memory for each hash index

Each hash index is a hash array of 8-byte address pointers. The size of the array is best determined by the number of unique index values for that index – e.g., the number of unique Col2 values is a good starting point for the array size for the t1c2_index. A hash array that is too big wastes memory. A hash array that is too small slows performance since there will be too many collisions by index values that hash to the same index.

Hash indexes achieve very fast equality lookups such as:

SQL

SELECT * FROM t_hk  
   WHERE Col2 = 3;

Nonclustered indexes are faster for range lookups such as:

SQL

SELECT * FROM t_hk  
   WHERE Col2 >= 3;

If you are migrating a disk-based table you can use the following to determine the number of unique values for the index t1c2_index.

SQL

SELECT COUNT(DISTINCT [Col2])  
  FROM t_hk;

If you are creating a new table, you’ll need to estimate the array size or gather data from your testing prior to deployment.

For information on how hash indexes work in In-Memory OLTP memory-optimized tables, see Hash Indexes.

Setting the hash index array size

The hash array size is set by (bucket_count= value) where value is an integer value greater than zero. If value is not a power of 2, the actual bucket_count is rounded up to the next closest power of 2. In our example table, (bucket_count = 5000000), since 5,000,000 is not a power of 2, the actual bucket count rounds up to 8,388,608 (2^23). You must use this number, not 5,000,000 when calculating memory needed by the hash array.

Thus, in our example, the memory needed for each hash array is:

8,388,608 * 8 = 2^23 * 8 = 2^23 * 2^3 = 2^26 = 67,108,864 or approximately 64 MB.

Since we have three hash indexes, the memory needed for the hash indexes is 3 * 64MB = 192MB.

Memory for nonclustered indexes

Nonclustered indexes are implemented as BTrees with the inner nodes containing the index value and pointers to subsequent nodes. Leaf nodes contain the index value and a pointer to the table row in memory.

Unlike hash indexes, nonclustered indexes do not have a fixed bucket size. The index grows and shrinks dynamically with the data.

Memory needed by nonclustered indexes can be computed as follows:

  • Memory allocated to non-leaf nodes
    For a typical configuration, the memory allocated to non-leaf nodes is a small percentage of the overall memory taken by the index. This is so small it can safely be ignored.
  • Memory for leaf nodes
    The leaf nodes have one row for each unique key in the table that points to the data rows with that unique key. If you have multiple rows with the same key (i.e., you have a non-unique nonclustered index), there is only one row in the index leaf node that points to one of the rows with the other rows linked to each other. Thus, the total memory required can be approximated by:
    • memoryForNonClusteredIndex = (pointerSize + sum(keyColumnDataTypeSizes)) * rowsWithUniqueKeys

Nonclustered indexes are best when used for range lookups, as exemplified by the following query:

SQL

SELECT * FROM t_hk  
   WHERE c2 > 5

Memory for row versioning

To avoid locks, In-Memory OLTP uses optimistic concurrency when updating or deleting rows. This means that when a row is updated, an additional version of the row is created. In addition, deletes are logical – the existing row is marked as deleted, but not removed immediately. The system keeps old row versions (including deleted rows) available until all transactions that could possibly use the version have finished execution.

Because there may be a number of additional rows in memory at any time waiting for the garbage collection cycle to release their memory, you must have sufficient memory to accommodate these additional rows.

The number of additional rows can be estimated by computing the peak number of row updates and deletions per second, then multiplying that by the number of seconds the longest transaction takes (minimum of 1).

That value is then multiplied by the row size to get the number of bytes you need for row versioning.

rowVersions = durationOfLongestTransctoinInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond

Memory needs for stale rows is then estimated by multiplying the number of stale rows by the size of a memory-optimized table row (See Memory for the table above).

memoryForRowVersions = rowVersions * rowSize

Memory for table variables

Memory used for a table variable is released only when the table variable goes out of scope. Deleted rows, including rows deleted as part of an update, from a table variable are not subject to garbage collection. No memory is released until the table variable exits scope.

Table variables defined in a large SQL batch, as opposed to a procedure scope, which are used in many transactions, can consume a lot of memory. Because they are not garbage collected, deleted rows in a table variable can consume a lot memory and degrade performance since read operations need to scan past the deleted rows.

Memory for growth

The above calculations estimate your memory needs for the table as it currently exists. In addition to this memory, you need to estimate the growth of the table and provide sufficient memory to accommodate that growth. For example, if you anticipate 10% growth then you need to multiple the results from above by 1.1 to get the total memory needed for your table.

Configuring Storage for Memory-Optimized Tables

You need to configure storage capacity and input/output operations per second (IOPS).

Storage Capacity

Use the information in Estimate Memory Requirements for Memory-Optimized Tables to estimate the in-memory size of the database’s durable memory-optimized tables. Because indexes aren’t persisted for memory-optimized tables, don’t include the size of indexes.

After you determine the size, you must provide disk space sufficient to hold the checkpoint files, which are used to store newly changed data. The data that’s stored contains not only the contents of new rows that are added to the in-memory tables, but also new versions of existing rows. This storage grows when rows are inserted or updated. Row versions are merged and storage is reclaimed when log truncation occurs. If log truncation is delayed for any reason, the in-memory OLTP store will grow.

A good starting point for sizing storage for this area is to reserve four times the size of durable, in-memory tables. Monitor the space usage and be prepared to expand the storage that’s available to it if necessary.

Storage IOPS

In-Memory OLTP can significantly increase your workload throughput. Therefore, it is important to ensure that IO is not a bottleneck.

  • When migrating disk-based tables to memory-optimized tables, make sure that the transaction log is on a storage media that can support increased transaction log activity. For example, if your storage media supports transaction log operations at 100 MB/sec, and memory-optimized tables result in five times greater performance, the transaction log’s storage media must be able to also support five times performance improvement, to prevent the transaction log activity from becoming a performance bottleneck.
  • Memory-optimized tables are persisted in checkpoint files, which are distributed across one or more containers. Each container should typically be mapped to its own storage device and is used both for increased storage capacity and improved IOPS. You need to ensure that the sequential IOPS of the storage media can support up to 3 times the sustained transaction log throughput. Writes to checkpoint files are 256 KB for data files and 4 KB for delta files.
    • For example, if memory-optimized tables generate a sustained 500 MB/sec of activity in the transaction log, the storage for memory-optimized tables must support 1.5 GB/sec IOPS. The need to support 3 times the sustained transaction log throughput comes from the observation that the data and delta file pairs are first written with the initial data and then need to be read/re-written as part of a merge operation.
  • Another factor in estimating the IOPS for storage is the recovery time for memory-optimized tables. Data from durable tables must be read into memory before a database is made available to applications. Commonly, loading data into memory-optimized tables can be done at the speed of IOPS. So if the total storage for durable, memory-optimized tables is 60 GB and you want to be able to load this data in 1 minute, the IOPS for the storage must be set at 1 GB/sec.
  • Checkpoint files are usually distributed uniformly across all containers, space permitting. With SQL Server 2014 you need to provision an odd number of containers in order to achieve a uniform distribution – starting 2016, both odd and even numbers of containers lead to a uniform distribution.

Encryption

In SQL Server 2016 (13.x) and later versions, storage for memory-optimized tables will be encrypted at rest as part of enabling Transparent Data Encryption (TDE) on the database. For more information, see Transparent Data Encryption. In SQL Server 2014 (12.x) checkpoint files are not encrypted even if TDE is enabled on the database.

Data in non-durable (SCHEMA_ONLY) memory-optimized tables is not written to disk at any time. Therefore, TDE does not apply to such tables.

Hardware considerations for In-Memory OLTP in SQL Server

In-Memory OLTP uses memory and disk in different ways than traditional disk-based tables. The performance improvement you will see with In-Memory OLTP depends the hardware you use. In this blog post we discuss a number of general hardware considerations, and provide generic guidelines for hardware to use with In-Memory OLTP.

CPU

In-Memory OLTP does not require a high-end server to support a high-throughput OLTP workload. We recommend using a mid-range server with 2 CPU sockets. Due to the increased throughput enabled by In-Memory OLTP, 2 sockets are likely going to be enough for your business needs.

We recommend to turn hyper-threading ON with in-memory OLTP. With some OLTP workloads we have seen performance gains of up to 40% when using hyper-threading.

Memory

All memory-optimized tables reside fully in memory. Therefore, you must have enough physical memory for the tables themselves and to sustain the workload running against the database – how much memory you actually need really depends on the workload, but as a starting point you will probably want enough available memory for about 2X the data size. You will also need enough memory for the buffer pool in case the workload also operates on traditional disk-based tables.

To determine how much memory a given memory-optimized table uses, run the following query:

SQL

select object_name(object_id), * from sys.dm_db_xtp_table_memory_stats;

The results will show the memory used for memory-optimized tables and their indexes. The table data includes the user data, as well as all the older row versions that are still required by running transactions or have not yet been cleaned up by the system. The memory used by hash indexes is constant, and does not depend on the number of rows in the table.

It is important to keep in mind when you use in-memory OLTP that your whole database does not need to fit in memory. You can have a multi-Terabyte database and still benefit from in-memory OLTP, as long as the size of your hot data (i.e., the memory-optimized tables) does not exceed 256GB. The maximum number of checkpoint data files SQL Server can manage for a single database is 4000, with each file being 128MB. Although this would give a theoretical maximum of 512GB, in order to guarantee that SQL Server can keep up with merging checkpoint files and not hit the limit of 4000 files, we support up to 256GB. Note that this limit applies only the memory-optimized tables; there is no such size limitation on the traditional disk-based tables in the same SQL Server database.

Non-durable memory-optimized tables (NDTs), i.e., memory-optimized tables with DURABILITY=SCHEMA_ONLY are not persisted on disk. Although NDTs are not limited by the number of checkpoint files, only 256GB is supported. The considerations for log and data drives in the remainder of this post do not apply to non-durable tables, as the data exists only in memory.

Log drive

Log records pertaining to memory-optimized tables are written to the database transaction log, along with the other SQL Server log records.

It is always important to put the log file on a drive that has low latency, such that transactions do not need to wait too long, and to prevent contention on log IO. Your system will run as fast as your slowest component (Amdahl’s law). You need to ensure that, when running In-Memory OLTP, your log IO device does not become a bottleneck. We recommend using a storage device with low latency, at least SSD.

Note that memory-optimized tables use less log bandwidth than disk-based tables, as they do not log index operations and do not log UNDO records. This can help to relieve log IO contention.

Data drive

Persistence of memory-optimized tables using checkpoint files uses streaming IO. Therefore, these files do not need a drive with low latency or fast random IO. Instead, the main factor for these drives is the speed of sequential IO and bandwidth of the host bus adapter (HBA). Thus, you don’t need SSDs for checkpoint files; you can place them on high performance spindles (e.g., SAS), as long as their sequential IO speed meets your requirements.

The biggest factor in determining the speed requirement is your RTO [Recovery Time Objective] on server restart. During database recovery, all data in the memory-optimized tables needs to be read from disk, into memory. Database recovery happens at the sequential read speed of your IO subsystem; disk is the bottleneck.

To meet strict RTO requirements we recommend to spread the checkpoint files over multiple disks, by adding multiple containers to the MEMORY_OPTIMIZED_DATA filegroup. SQL Server supports parallel load of checkpoint files from multiple drives – recovery happens at the aggregate speed of the drives.

In terms of disk capacity, we recommend to have 2-3X the size of the memory-optimized tables available.

Indexes on Memory-Optimized Tables

All memory-optimized tables must have at least one index, because it is the indexes that connect the rows together. On a memory-optimized table, every index is also memory-optimized. There are several ways in which an index on a memory-optimized table differs from a traditional index on a disk-base table:

  • Data rows are not stored on pages, so there is no collection of pages or extents, no partitions or allocation units that can be referenced to get all the pages for a table. There is the concept of index pages for one of the available types of indexes, but they are stored differently than indexes for disk-based tables. They do not accrue the traditional type of fragmentation within a page, so they have no fill factor.
  • Changes made to indexes on memory-optimized tables during data manipulation are never written to disk. Only the data rows, and changes to the data, are written to the transaction log.
  • Memory-optimized indexes are rebuilt when the database is brought back online.

All indexes on memory-optimized tables are created based on the index definitions during database recovery.

The index must be one of the following:

  • Hash index
  • Memory-optimized Nonclustered index (meaning the default internal structure of a B-tree)

Syntax for memory-optimized indexes

Each CREATE TABLE statement for a memory-optimized table must include an index, either explicitly through an INDEX or implicitly through a PRIMAY KEY or UNIQUE constraint.

To be declared with the default DURABILITY = SCHEMA_AND_DATA, the memory-optimized table must have a primary key. The PRIMARY KEY NONCLUSTERED clause in the following CREATE TABLE statement satisfies two requirements:

  • Provides an index to meet the minimum requirement of one index in the CREATE TABLE statement.
  • Provides the primary key that is required for the SCHEMA_AND_DATA clause.

SQL

CREATE TABLE SupportEvent 

    SupportEventId   int NOT NULL 

        PRIMARY KEY NONCLUSTERED, 

    … 

    WITH ( 

        MEMORY_OPTIMIZED = ON, 

        DURABILITY = SCHEMA_AND_DATA); 

Code sample for syntax

This subsection contains a Transact-SQL code block that demonstrates the syntax to create various indexes on a memory-optimized table. The code demonstrates the following:

  1. Create a memory-optimized table.
  2. Use ALTER TABLE statements to add two indexes.
  3. INSERT a few rows of data.

SQL

DROP TABLE IF EXISTS SupportEvent; 

go 

CREATE TABLE SupportEvent 

    SupportEventId   int               not null   identity(1,1) 

    PRIMARY KEY NONCLUSTERED, 

    StartDateTime        datetime2     not null, 

    CustomerName         nvarchar(16)  not null, 

    SupportEngineerName  nvarchar(16)      null, 

    Priority             int               null, 

    Description          nvarchar(64)      null 

    WITH ( 

    MEMORY_OPTIMIZED = ON, 

    DURABILITY = SCHEMA_AND_DATA); 

go 

    ——————– 

ALTER TABLE SupportEvent 

    ADD CONSTRAINT constraintUnique_SDT_CN 

    UNIQUE NONCLUSTERED (StartDateTime DESC, CustomerName); 

go 

ALTER TABLE SupportEvent 

    ADD INDEX idx_hash_SupportEngineerName 

    HASH (SupportEngineerName) WITH (BUCKET_COUNT = 64);  — Nonunique. 

go 

    ——————– 

INSERT INTO SupportEvent 

    (StartDateTime, CustomerName, SupportEngineerName, Priority, Description) 

    VALUES 

    (‘2016-02-23 13:40:41:123’, ‘Abby’, ‘Zeke’, 2, ‘Display problem.’     ), 

    (‘2016-02-24 13:40:41:323’, ‘Ben’ , null  , 1, ‘Cannot find help.’    ), 

    (‘2016-02-25 13:40:41:523’, ‘Carl’, ‘Liz’ , 2, ‘Button is gray.’      ), 

    (‘2016-02-26 13:40:41:723’, ‘Dave’, ‘Zeke’, 2, ‘Cannot unhide column.’); 

go

Duplicate index key values

Duplicate values for an index key might reduce the performance of memory-optimized tables. Duplicates for the system to traverse entry chains for most index read and write operations. When a chain of duplicate entries exceeds 100 entries, the performance degradation can become measurable.

Duplicate hash values

This problem is more visible in the case of hash indexes. Hash indexes suffer more due to the following considerations:

  • The lower cost per operation for hash indexes.
  • The interference of large duplicate chains with the hash collision chain.

To reduce duplication in an index, try the following adjustments:

  • Use a nonclustered index.
  • Add additional columns to the end of the index key, to reduce the number of duplicates.
    • For example, you could add columns that are also in the primary key.

Example improvement

Here is an example of how to avoid any performance inefficiency in your index.

Consider a Customers table that has a primary key on CustomerId, and has an index on column CustomerCategoryID. Typically there will be many customers in a given category. Thus there will be many duplicate values for CustomerCategoryID inside a given key of the index.

In this scenario, the best practice is to use a nonclustered index on (CustomerCategoryID, CustomerId). This index can be used for queries that use a predicate involving CustomerCategoryID, yet the index key does not contain duplication. Therefore, no inefficiencies in index maintenance are cause by either the duplicate CustomerCategoryID values, or by the extra column in the index.

The following query shows the average number of duplicate index key values for the index on CustomerCategoryID in table Sales.Customers, in the sample database WideWorldImporters.

SQL

SELECT AVG(row_count) FROM

    (SELECT COUNT(*) AS row_count

                   FROM Sales.Customers

                   GROUP BY CustomerCategoryID) a

To evaluate the average number of index key duplicates for your own table and index, replace Sales.Customers with your table name, and replace CustomerCategoryID with the list of index key columns.

Comparing when to use each index type

The nature of your particular queries determines which type of index is the best choice.

When implementing memory-optimized tables in an existing application, the general recommendation is to start with nonclustered indexes, as their capabilities more closely resemble the capabilities of traditional clustered and nonclustered indexes on disk-based tables.

Recommendations for nonclustered index use

A nonclustered index is preferable over a hash index when:

  • Queries have an ORDER BY clause on the indexed column.
  • Queries where only the leading column(s) of a multi-column index is tested.
  • Queries test the indexed column by use of a WHERE clause with:
    • An inequality: WHERE StatusCode != ‘Done’
    • A value range scan: WHERE Quantity >= 100

In all the following SELECTs, a nonclustered index is preferable over a hash index:

SQL

SELECT CustomerName, Priority, Description

FROM SupportEvent 

WHERE StartDateTime > DateAdd(day, -7, GetUtcDate()); 

SELECT StartDateTime, CustomerName 

FROM SupportEvent 

ORDER BY StartDateTime DESC; — ASC would cause a scan.

SELECT CustomerName 

FROM SupportEvent 

WHERE StartDateTime = ‘2016-02-26’; 

Recommendations for hash index use

Hash indexes are primarily used for point lookups and not for range scans.

A hash index is preferable over a nonclustered index when queries use equality predicates, and the WHERE clause maps to all index key columns, as in the following example:

SQL

SELECT CustomerName

FROM SupportEvent 

WHERE SupportEngineerName = ‘Liz’;

Multi-column index

A multi-column index could be a nonclustered index or a hash index. Suppose the index columns are col1 and col2. Given the following SELECT statement, only the nonclustered index would be useful to the query optimizer:

SQL

SELECT col1, col3 

FROM MyTable_memop 

WHERE col1 = ‘dn’; 

The hash index needs the WHERE clause to specify an equality test for each of the columns in its key. Else the hash index is not useful to the query optimizer.

Neither index type is useful if the WHERE clause specifies only the second column in the index key.

Summary table to compare index use scenarios

The following table lists all operations that are supported by the different index types. Yes means that the index can efficiently service the request, and No means that the index cannot efficiently satisfy the request.

SUMMARY TABLE TO COMPARE INDEX USE SCENARIOS
OperationMemory-optimized,
hash
Memory-optimized,
nonclustered
Disk-based,
(non)clustered
Index Scan, retrieve all table rows.YesYesYes
Index seek on equality predicates (=).Yes
(Full key is required.)
YesYes
Index seek on inequality and range predicates
(>, <, <=, >=, BETWEEN).
No
(Results in an index scan.)
Yes Yes
Retrieve rows in a sort order that matches the index definition.NoYesYes
Retrieve rows in a sort-order that matches the reverse of the index definition.NoNoYes

Automatic index and statistics management

Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

Sample Database for In-Memory OLTP

The sample migrates five tables in the AdventureWorks database to memory-optimized, and it includes a demo workload for sales order processing. You can use this demo workload to see the performance benefit of using In-Memory OLTP on your server.

In the description of the sample, we discuss the tradeoffs that were made in migrating the tables to In-Memory OLTP to account for the features that are not (yet) supported for memory-optimized tables.

Installing the In-Memory OLTP sample based on AdventureWorks

Follow these steps to install the sample:

  1. Download AdventureWorks2016CTP3.bak and SQLServer2016CTP3Samples.zip from: https://github.com/microsoft/sql-server-samples/releases/tag/adventureworks to a local folder, for example ‘c:\temp’.
  2. Restore the database backup using Transact-SQL or SQL Server Management Studio:
    1. Identify the target folder and filename for the data file, for example

‘h:\DATA\AdventureWorks2016CTP3_Data.mdf’

  • Identify the target folder and filename for the log file, for example

‘i:\DATA\AdventureWorks2016CTP3_log.ldf’

  1. The log file should be placed on a different drive than the data file, ideally a low latency drive such as an SSD or PCIe storage, for maximum performance.

Example T-SQL script:

SQL

RESTORE DATABASE [AdventureWorks2016CTP3]   
  FROM DISK = N'C:\temp\AdventureWorks2016CTP3.bak'   
    WITH FILE = 1,    
  MOVE N'AdventureWorks2016_Data' TO N'h:\DATA\AdventureWorks2016CTP3_Data.mdf',    
  MOVE N'AdventureWorks2016_Log' TO N'i:\DATA\AdventureWorks2016CTP3_log.ldf'
  MOVE N'AdventureWorks2016CTP3_mod' TO N'h:\data\AdventureWorks2016CTP3_mod'  
 GO  
  • To view the sample scripts and workload, unpack the file SQLServer2016CTP3Samples.zip to a local folder. Consult the file In-Memory OLTP\readme.txt for instructions on running the workload.

Description of the sample tables and procedures

The sample creates new tables for products and sales orders, based on existing tables in AdventureWorks. The schema of the new tables is similar to the existing tables, with a few differences, as explained below.

The new memory-optimized tables carry the suffix ‘_inmem’. The sample also includes corresponding tables carrying the suffix ‘_ondisk’ – these tables can be used to make a one-to-one comparison between the performance of memory-optimized tables and disk-based tables on your system..

The memory-optimized tables used in the workload for performance comparison are fully durable and fully logged. They do not sacrifice durability or reliability to attain the performance gain.

The target workload for this sample is sales order processing, where we consider also information about products and discounts. To this end, the table SalesOrderHeader, SalesOrderDetail, Product, SpecialOffer, and SpecialOfferProduct.

Two new stored procedures, Sales.usp_InsertSalesOrder_inmem and Sales.usp_UpdateSalesOrderShipInfo_inmem, are used to insert sales orders and to update the shipping information of a given sales order.

The new schema ‘Demo’ contains helper tables and stored procedures to execute a demo workload.

Concretely, the In-Memory OLTP sample adds the following objects to AdventureWorks:

Tables added by the sample

The New Tables

Sales.SalesOrderHeader_inmem

  • Header information about sales orders. Each sales order has one row in this table.

Sales.SalesOrderDetail_inmem

  • Details of sales orders. Each line item of a sales order has one row in this table.

Sales.SpecialOffer_inmem

  • Information about special offers, including the discount percentage associated with each special offer.

Sales.SpecialOfferProduct_inmem

  • Reference table between special offers and products. Each special offer can feature zero or more products, and each product can be featured in zero or more special offers.

Production.Product_inmem

  • Information about products, including their list price.

Demo.DemoSalesOrderDetailSeed

  • Used in the demo workload to construct sample sales orders.

Disk-based variations of the tables:

  • Sales.SalesOrderHeader_ondisk
  • Sales.SalesOrderDetail_ondisk
  • Sales.SpecialOffer_ondisk
  • Sales.SpecialOfferProduct_ondisk
  • Production.Product_ondisk

Differences between original disk-based and the and new memory-optimized tables

For the most part, the new tables introduced by this sample use the same columns and the same data types as the original tables. However, there are a few differences. We list the differences below, along with a rationale for the changes.

Sales.SalesOrderHeader_inmem

  • Default constraints are supported for memory-optimized tables, and most default constraints we migrated as is. However, the original table Sales.SalesOrderHeader contains two default constraints that retrieve the current date, for the columns OrderDate and ModifiedDate. In a high throughput order processing workload with much concurrency, any global resource can become a point of contention. System time is such a global resource, and we have observed that it can become a bottleneck when running an In-Memory OLTP workload that inserts sales orders, in particular if the system time needs to be retrieved for multiple columns in the sales order header, as well as the sales order details. The problem is addressed in this sample by retrieving the system time only once for each sales order that is inserted, and use that value for the datetime columns in SalesOrderHeader_inmem and SalesOrderDetail_inmem, in the stored procedure Sales.usp_InsertSalesOrder_inmem.
  • Alias user-defined data types (UDTs) – The original table uses two alias UDTs dbo.OrderNumber and dbo.AccountNumber, for the columns PurchaseOrderNumber and AccountNumber, respectively. SQL Server 2016 (13.x) does not support alias UDT for memory-optimized tables, thus the new tables use system data types nvarchar(25) and nvarchar(15), respectively.
  • Nullable columns in index keys – In the original table, the column SalesPersonID is nullable, while in the new tables the column is not nullable and has a default constraint with value (-1). This circumstance is because indexes on memory-optimized tables cannot have nullable columns in the index key; -1 is a surrogate for NULL in this case.
  • Computed columns – The computed columns SalesOrderNumber and TotalDue are omitted, as SQL Server 2016 (13.x) does not support computed columns in memory-optimized tables. The new view Sales.vSalesOrderHeader_extended_inmem reflects the columns SalesOrderNumber and TotalDue. Therefore, you can use this view if these columns are needed.
    • Applies to: SQL Server 2017 (14.x) CTP 1.1.
      Beginning with SQL Server 2017 (14.x) CTP 1.1, computed columns are supported in memory-optimized tables and indexes.
  • Foreign key constraints are supported for memory-optimized tables in SQL Server 2016 (13.x), but only if the referenced tables are also memory-optimized. Foreign keys that reference tables that are also migrated to memory-optimized are kept in the migrated tables, while other foreign keys are omitted. In addition, SalesOrderHeader_inmem is a hot table in the example workload, and foreign keys constraints require additional processing for all DML operations, as it requires lookups in all the other tables referenced in these constraints. Therefore, the assumption is that the app ensures referential integrity for the Sales.SalesOrderHeader_inmem table, and referential integrity is not validated when rows are inserted.
  • Rowguid – The rowguid column is omitted. While uniqueidentifier is support for memory-optimized tables, the option ROWGUIDCOL is not supported in SQL Server 2016 (13.x). Columns of this kind are typically used for either merge replication or tables that have filestream columns. This sample includes neither.

Sales.SalesOrderDetail

  • Default constraints – similar to SalesOrderHeader, the default constraint requiring the system date/time is not migrated, instead the stored procedure inserting sales orders takes care of inserting the current system date/time on first insert.
  • Computed columns – the computed column LineTotal was not migrated as computed columns are not supported with memory-optimized tables in SQL Server 2016 (13.x). To access this column, use the view Sales.vSalesOrderDetail_extended_inmem.
  • Rowguid – The rowguid column is omitted. For details see the description for the table SalesOrderHeader.

Production.Product

  • Alias UDTs – the original table uses the user-defined data type dbo.Flag, which is equivalent to the system data type bit. The migrated table uses the bit data type instead.
  • Rowguid – The rowguid column is omitted. For details see the description for the table SalesOrderHeader.

Sales.SpecialOffer

  • Rowguid – The rowguid column is omitted. For details see the description for the table SalesOrderHeader.

Sales.SpecialOfferProduct

  • Rowguid – The rowguid column is omitted. For details see the description for the table SalesOrderHeader.

Considerations for indexes on memory-optimized tables

The baseline index for memory-optimized tables is the NONCLUSTERED index, which supports point lookups (index seek on equality predicate), range scans (index seek in inequality predicate), full index scans, and ordered scans. In addition, NONCLUSTERED indexes support searching on leading columns of the index key. In fact memory-optimized NONCLUSTERED indexes support all the operations supported by disk-based NONCLUSTERED indexes, with the only exception being backward scans. Therefore, using NONCLUSTERED indexes is a safe choice for your indexes.

HASH indexes are can be used to further optimize the workload. They are particularly optimized for point lookups and row inserts. However, one must consider that they do not support range scans, ordered scans, or search on leading index key columns. Therefore, care needs to be taken when using these indexes. In addition, it is necessary to specify the bucket_count at create time. It should usually be set at between one and two times the number of index key values, but overestimating is usually not a problem.

The indexes on the migrated tables have been tuned for the demo sales order processing workload. The workload relies on inserts and point lookups in the tables Sales.SalesOrderHeader_inmem and Sales.SalesOrderDetail_inmem, and it also relies on point lookups on the primary key columns in the tables Production.Product_inmem and Sales.SpecialOffer_inmem.

Sales.SalesOrderHeader_inmem has three indexes, which are all HASH indexes for performance reasons, and because no ordered or range scans are needed for the workload.

  • HASH index on (SalesOrderID): bucket_count is sized at 10 million (rounded up to 16 million), because the expected number of sales orders is 10 million
  • HASH index on (SalesPersonID): bucket_count is 1 million. The data set provided does not have many sales persons. But this large bucket_count allows for future growth. Plus you don’t pay a performance penalty for point lookups if the bucket_count is oversized.
  • HASH index on (CustomerID): bucket_count is 1 million. The data set provided does not have a lot of customers, but this allows for future growth.

Sales.SalesOrderDetail_inmem has three indexes, which are all HASH indexes for performance reasons, and because no ordered or range scans are needed for the workload.

  • HASH index on (SalesOrderID, SalesOrderDetailID): this is the primary key index, and even though lookups on (SalesOrderID, SalesOrderDetailID) will be infrequent, using a hash index for the key speeds up row inserts. The bucket_count is sized at 50 million (rounded up to 67 million): the expected number of sales orders is 10 million, and this is sized to have an average of five items per order
  • HASH index on (SalesOrderID): lookups by sales order are frequent: you will want to find all the line items corresponding to a single order. bucket_count is sized at 10 million (rounded up to 16 million), because the expected number of sales orders is 10 million
  • HASH index on (ProductID): bucket_count is 1 million. The data set provided does not have a lot of product, but this allows for future growth.

Production.Product_inmem has three indexes

  • HASH index on (ProductID): lookups on ProductID are in the critical path for the demo workload, therefore this is a hash index
  • NONCLUSTERED index on (Name): this will allow ordered scans of product names
  • NONCLUSTERED index on (ProductNumber): this will allow ordered scans of product numbers

Sales.SpecialOffer_inmem has one HASH index on (SpecialOfferID): point lookups of special offers are in the critical part of the demo workload. The bucket_count is sized at 1 million to allow for future growth.

Sales.SpecialOfferProduct_inmem is not referenced in the demo workload, and thus there is no apparent need to use hash indexes on this table to optimize the workload – the indexes on (SpecialOfferID, ProductID) and (ProductID) are NONCLUSTERED.

Notice that in the above some of the bucket_counts are over-sized, but not the bucket_counts for the indexes on SalesOrderHeader_inmem and SalesOrderDetail_inmem: they are sized for just 10 million sales orders. This was done to allow installing the sample on systems with low memory availability, although in those cases the demo workload will fail with out-of-memory. If you do want to scale well beyond 10 million sales orders, feel free to increase the bucket counts accordingly.

Stored Procedures added by the sample

The two key stored procedures for inserting sales order and updating shipping details are as follows:

  • Sales.usp_InsertSalesOrder_inmem
    • Inserts a new sales order in the database and outputs the SalesOrderID for that sales order. As input parameters it takes details for the sales order header, as well as the line items in the order.
    • Output parameter:
      • @SalesOrderID int – the SalesOrderID for the sales order that was just inserted
    • Input parameters (required):
      • @DueDate datetime2
      • @CustomerID int
      • @BillToAddressID [int]
      • @ShipToAddressID [int]
      • @ShipMethodID [int]
      • @SalesOrderDetails Sales.SalesOrderDetailType_inmem – table-valued parameter (TVP) that contains the line items of the order
    • Input parameters (optional):
      • @Status [tinyint]
      • @OnlineOrderFlag [bit]
      • @PurchaseOrderNumber [nvarchar](25)
      • @AccountNumber [nvarchar](15)
      • @SalesPersonID [int]
      • @TerritoryID [int]
      • @CreditCardID [int]
      • @CreditCardApprovalCode [varchar](15)
      • @CurrencyRateID [int]
      • @Comment nvarchar(128)
  • Sales.usp_UpdateSalesOrderShipInfo_inmem
    • Update the shipping information for a given sales order. This will also update the shipping information for all line items of the sales order.
    • This is a wrapper procedure for the natively compiled stored procedures Sales.usp_UpdateSalesOrderShipInfo_native with retry logic to deal with (unexpected) potential conflicts with concurrent transactions updating the same order. For more information about retry logic see the Books Online topic here.
  • Sales.usp_UpdateSalesOrderShipInfo_native
    • This is the natively compiled stored procedure that actually processes the update to the shipping information. It is means to be called from the wrapper stored procedure Sales.usp_UpdateSalesOrderShipInfo_inmem. If the client can deal with failures and implements retry logic, you can call this procedure directly, rather than using the wrapper stored procedure.

The following stored procedure is used for the demo workload.

  • Demo.usp_DemoReset
    • Resets the demo by emptying and reseeding the SalesOrderHeader and SalesOrderDetail tables.

The following stored procedures are used for inserting in and deleting from memory-optimized tables while guaranteeing domain and referential integrity.

  • Production.usp_InsertProduct_inmem
  • Production.usp_DeleteProduct_inmem
  • Sales.usp_InsertSpecialOffer_inmem
  • Sales.usp_DeleteSpecialOffer_inmem
  • Sales.usp_InsertSpecialOfferProduct_inmem

Finally the following stored procedure is used to verify domain and referential integrity.

  1. dbo.usp_ValidateIntegrity
    1. Optional parameter: @object_id – ID of the object to validate integrity for
    1. This procedure relies on the tables dbo.DomainIntegrity, dbo.ReferentialIntegrity, and dbo.UniqueIntegrity for the integrity rules that need to be verified – the sample populates these tables based on the check, foreign key, and unique constraints that exist for the original tables in the AdventureWorks database.
    1. It relies on the helper procedures dbo.usp_GenerateCKCheck, dbo.usp_GenerateFKCheck, and dbo.GenerateUQCheck to generate the T-SQL needed for performing the integrity checks.

Performance Measurements using the Demo Workload

Ostress is a command-line tool that was developed by the Microsoft CSS SQL Server support team. This tool can be used to execute queries or run stored procedures in parallel. You can configure the number of threads to run a given T-SQL statement in parallel, and you can specify how many times the statement should be executed on this thread; ostress will spin up the threads and execute the statement on all threads in parallel. After execution finishes for all threads, ostress will report the time taken for all threads to finish execution.

Installing ostress

Ostress is installed as part of the Report Markup Language (RML) Utilities; there is no standalone installation for ostress.

Installation steps:

  1. Download and run the x64 installation package for the RML utilities
  2. If there is a dialog box saying certain files are in use, click ‘Continue’

Running ostress

Ostress is run from the command-line prompt. It is most convenient to run the tool from the “RML Cmd Prompt”, which is installed as part of the RML Utilities.

To open the RML Cmd Prompt follow these instructions:

In Windows Server 2012 [R2] and in Windows 8 and 8.1, open the start menu by clicking the Windows key, and type ‘rml’. Click on “RML Cmd Prompt”, which will be in the list of search results.

Ensure that the command prompt is located in the RML Utilities installation folder.

The command-line options for ostress can be seen when simply running ostress.exe without any command-line options. The main options to consider for running ostress with this sample are:

  • -S name of Microsoft SQL Server instance to connect to
  • -E use Windows authentication to connect (default); if you use SQL Server authentication, use the options -U and -P to specify the username and password, respectively
  • -d name of the database, for this example AdventureWorks2014
  • -Q the T-SQL statement to be executed
  • -n number of connections processing each input file/query
  • -r the number of iterations for each connection to execute each input file/query

Demo Workload

The main stored procedure used in the demo workload is Sales.usp_InsertSalesOrder_inmem/ondisk. The script in the below constructs a table-valued parameter (TVP) with sample data, and calls the procedure to insert a sales order with five line items.

The ostress tool is used to execute the stored procedure calls in parallel, to simulate clients inserting sales orders concurrently.

Reset the demo after each stress run executing Demo.usp_DemoReset. This procedure deletes the rows in the memory-optimized tables, truncates the disk-based tables, and executes a database checkpoint.

The following script is executed concurrently to simulate a sales order processing workload:

SQL

DECLARE   
      @i int = 0,   
      @od Sales.SalesOrderDetailType_inmem,   
      @SalesOrderID int,   
      @DueDate datetime2 = sysdatetime(),   
      @CustomerID int = rand() * 8000,   
      @BillToAddressID int = rand() * 10000,   
      @ShipToAddressID int = rand() * 10000,   
      @ShipMethodID int = (rand() * 5) + 1;   
  
INSERT INTO @od   
SELECT OrderQty, ProductID, SpecialOfferID   
FROM Demo.DemoSalesOrderDetailSeed   
WHERE OrderID= cast((rand()*106) + 1 as int);   
  
WHILE (@i < 20)   
BEGIN;   
      EXEC Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od;   
      SET @i += 1   
END

With this script, each sample order that is constructed is inserted 20 times, through 20 stored procedures executed in a WHILE loop. The loop is used to account for the fact that the database is used to construct the sample order. In typical production environments, the mid-tier application will construct the sales order to be inserted.

The above script inserts sales orders into memory-optimized tables. The script to insert sales orders into disk-based tables is derived by replacing the two occurrences of ‘_inmem’ with ‘_ondisk’.

We will use the ostress tool to execute the scripts using several concurrent connections. We will use the parameter ‘-n’ to control the number of connections, and the parameter ‘r’ to control how many times the script is executed on each connection.

Running the Workload

To test at scale we insert 10 million sales orders, using 100 connections. This test performs reasonably on a modest server (e.g., 8 physical, 16 logical cores), and basic SSD storage for the log. If the test does not perform well on your hardware, take look at the Section Troubleshooting slow-running tests.If you want to reduce the level of stress for this test, lower the number of connections by changing the parameter ‘-n’. For example to lower the connection count to 40, change the parameter ‘-n100’ to ‘-n40’.

As a performance measure for the workload we use the elapsed time as reported by ostress.exe after running the workload.

The below instructions and measurements use a workload that inserts 10 million sales orders. For instructions to run a scaled-down workload inserting 1 million sales orders, see the instructions in ‘In-Memory OLTP\readme.txt’ that is part of the SQLServer2016CTP3Samples.zip archive.

Memory-optimized tables

We will start by running the workload on memory-optimized tables. The following command opens 100 threads, each running for 5,000 iterations. Each iteration inserts 20 sales orders in separate transactions. There are 20 inserts per iteration to compensate for the fact that the database is used to generate the data to be inserted. This yield a total of 20 * 5,000 * 100 = 10,000,000 sales order inserts.

Open the RML Cmd Prompt, and execute the following command:

Click the Copy button to copy the command, and paste it into the RML Utilities command prompt.

Console

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2016CTP3 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_inmem, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_inmem @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"  

On one test server with a total number of 8 physical (16 logical) cores, this took 2 minutes and 5 seconds. On a second test server with 24 physical (48 logical) cores, this took 1 minute and 0 seconds.

Observe the CPU utilization while the workload is running, for example using task manager. You will see that CPU utilization is close to 100%. If this is not the case, you have a log IO bottleneck.

Disk-based tables

The following command will run the workload on disk-based tables. This workload may take a while to execute, which is largely due to latch contention in the system. Memory-optimized table are latch-free and thus do not suffer from this problem.

Open the RML Cmd Prompt, and execute the following command:

Click the Copy button to copy the command, and paste it into the RML Utilities command prompt.

Console

ostress.exe -n100 -r5000 -S. -E -dAdventureWorks2016CTP3 -q -Q"DECLARE @i int = 0, @od Sales.SalesOrderDetailType_ondisk, @SalesOrderID int, @DueDate datetime2 = sysdatetime(), @CustomerID int = rand() * 8000, @BillToAddressID int = rand() * 10000, @ShipToAddressID int = rand() * 10000, @ShipMethodID int = (rand() * 5) + 1; INSERT INTO @od SELECT OrderQty, ProductID, SpecialOfferID FROM Demo.DemoSalesOrderDetailSeed WHERE OrderID= cast((rand()*106) + 1 as int); while (@i < 20) begin; EXEC Sales.usp_InsertSalesOrder_ondisk @SalesOrderID OUTPUT, @DueDate, @CustomerID, @BillToAddressID, @ShipToAddressID, @ShipMethodID, @od; set @i += 1 end"  

On one test server with a total number of 8 physical (16 logical) cores, this took 41 minutes and 25 seconds. On a second test server with 24 physical (48 logical) cores, this took 52 minutes and 16 seconds.

The main factor in the performance difference between memory-optimized tables and disk-based tables in this test is the fact that when using disk-based tables, SQL Server cannot not fully utilize the CPU. The reason is latch contention: concurrent transactions are attempting to write to the same data page; latches are used to ensure only one transaction at a time can write to a page. The In-Memory OLTP engine is latch-free, and data rows are not organized in pages. Thus, concurrent transactions do not block each other’s inserts, thus enabling SQL Server to fully utilize the CPU.

You can observe the CPU utilization while the workload is running, for example using task manager. You will see with disk-based tables the CPU utilization is far from 100%. On a test configuration with 16 logical processors, the utilization would hover around 24%.

Optionally, you can view the number of latch waits per second using Performance Monitor, with the performance counter ‘\SQL Server:Latches\Latch Waits/sec’.

Resetting the demo

To reset the demo, open the RML Cmd Prompt, and execute the following command:

Console

ostress.exe -S. -E -dAdventureWorks2016CTP3 -Q"EXEC Demo.usp_DemoReset"  

Depending on the hardware, this may take a few minutes to run.

We recommend a reset after every demo run. Because this workload is insert-only, each run will consume more memory, and thus a reset is required to prevent running out of memory.

Troubleshooting slow-running tests

Test results will typically vary with hardware, and also the level of concurrency used in the test run. A couple of things to look for if the results are not as expected:

  • Number of concurrent transactions: When running the workload on a single thread, performance gain with In-Memory OLTP will likely be less than 2X. Latch contention is only a big problem if there is a high level of concurrency.
  • Low number of cores available to SQL Server: This means there will be a low level of concurrency in the system, as there can only be as many concurrently executing transactions as there are cores available to SQL.
    • Symptom: if the CPU utilization is high when running the workload on disk-based tables, this means there is not a lot of contention, pointing to a lack of concurrency.
  • Speed of the log drive: If the log drive cannot keep up with the level of transaction throughput in the system, the workload becomes bottlenecked on log IO. Although logging is more efficient with In-Memory OLTP, if log IO is a bottleneck, the potential performance gain is limited.
    • Symptom: if the CPU utilization is not close to 100% or is very spiky when running the workload on memory-optimized tables, it is possible there is a log IO bottleneck. This can be confirmed by opening Resource Monitor and looking at the queue length for the log drive.

Memory and Disk Space Utilization in the Sample

In the below we describe what to expect in terms of memory and disk space utilization for the sample database. We also show the results we have seen in on a test server with 16 logical cores.

Memory utilization for the memory-optimized tables

Overall utilization of the database

The following query can be used to obtain the total memory utilization for In-Memory OLTP in the system.

SQL

SELECT type  
   , name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'  

Snapshot after the database has just been created:

OVERALL UTILIZATION OF THE DATABASE
TypeNamePages_mb
MEMORYCLERK_XTPDefault94
MEMORYCLERK_XTPDB_ID_5877
MEMORYCLERK_XTPDefault0
MEMORYCLERK_XTPDefault0

The default memory clerks contain system-wide memory structures and are relatively small. The memory clerk for the user database, in this case database with ID 5, is about 900 MB.

Memory utilization per table

The following query can be used to drill down into the memory utilization of the individual tables and their indexes:

SQL

SELECT object_name(t.object_id) AS [Table Name
     , memory_allocated_for_table_kb  
 , memory_allocated_for_indexes_kb  
FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t   
ON dms.object_id=t.object_id  
WHERE t.type='U'  

The following table displays the results of this query for a fresh installation of the sample:

MEMORY UTILIZATION PER TABLE
Table Namememory_allocated_for_table_kbmemory_allocated_for_indexes_kb
SpecialOfferProduct_inmem643840
DemoSalesOrderHeaderSeed19845504
SalesOrderDetail_inmem15316663552
DemoSalesOrderDetailSeed6410432
SpecialOffer_inmem38192
SalesOrderHeader_inmem7168147456
Product_inmem12412352

As you can see the tables are fairly small: SalesOrderHeader_inmem is about 7 MB, and SalesOrderDetail_inmem is about 15 MB in size.

What is striking here is the size of the memory allocated for indexes, compared to the size of the table data. That is because the hash indexes in the sample are pre-sized for a larger data size. Note that hash indexes have a fixed size, and thus their size will not grow with the size of data in the table.

Memory utilization after running the workload

After insert 10 million sales orders, the all-up memory utilization looks similar to the following:

SQL

SELECT type  
, name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'  
 
MEMORY UTILIZATION AFTER RUNNING THE WORKLOAD
TypeNamePages_mb
MEMORYCLERK_XTPDefault146
MEMORYCLERK_XTPDB_ID_57374
MEMORYCLERK_XTPDefault0
MEMORYCLERK_XTPDefault0

As you can see, SQL Server is using a bit under 8 GB for the memory-optimized tables and indexes in the sample database.

Looking at the detailed memory usage per table after one example run:

SQL

SELECT object_name(t.object_id) AS [Table Name
     , memory_allocated_for_table_kb  
 , memory_allocated_for_indexes_kb  
FROM sys.dm_db_xtp_table_memory_stats dms JOIN sys.tables t   
ON dms.object_id=t.object_id  
WHERE t.type='U'  
 
Table Namememory_allocated_for_table_kbmemory_allocated_for_indexes_kb
SalesOrderDetail_inmem5113761663552
DemoSalesOrderDetailSeed6410368
SpecialOffer_inmem28192
SalesOrderHeader_inmem1575679147456
Product_inmem11112032
SpecialOfferProduct_inmem643712
DemoSalesOrderHeaderSeed19845504

We can see a total of about 6.5 GB of data. Notice that the size of the indexes on the table SalesOrderHeader_inmem and SalesOrderDetail_inmem is the same as the size of the indexes before inserting the sales orders. The index size did not change because both tables are using hash indexes, and hash indexes are static.

After demo reset

The stored procedure Demo.usp_DemoReset can be used to reset the demo. It deletes the data in the tables SalesOrderHeader_inmem and SalesOrderDetail_inmem, and re-seeds the data from the original tables SalesOrderHeader and SalesOrderDetail.

Now, even though the rows in the tables have been deleted, this does not mean that memory is reclaimed immediately. SQL Server reclaims memory from deleted rows in memory-optimized tables in the background, as needed. You will see that immediately after demo reset, with no transactional workload on the system, memory from deleted rows is not yet reclaimed:

SQL

SELECT type  
, name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'  
 
TypeNamePages_mb
MEMORYCLERK_XTPDefault2261
MEMORYCLERK_XTPDB_ID_57396
MEMORYCLERK_XTPDefault0
MEMORYCLERK_XTPDefault0

This is expected: memory will be reclaimed when the transactional workload is running.

If you start a second run of the demo workload you will see the memory utilization decrease initially, as the previously deleted rows are cleaned up. At some point the memory size will increase again, until the workload finishes. After inserting 10 million rows after demo reset, the memory utilization will be very similar to the utilization after the first run. For example:

SQL

SELECT type  
, name  
, pages_kb/1024 AS pages_MB   
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'  
TypeNamePages_mb
MEMORYCLERK_XTPDefault1863
MEMORYCLERK_XTPDB_ID_57390
MEMORYCLERK_XTPDefault0
MEMORYCLERK_XTPDefault0

Disk utilization for memory-optimized tables

The overall on-disk size for the checkpoint files of a database at a given time can be found using the query:

SQL

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]  
FROM sys.filegroups f JOIN sys.database_files df   
   ON f.data_space_id=df.data_space_id  
WHERE f.type=N'FX'  
  

Initial state

When the sample filegroup and sample memory-optimized tables are created initially, a number of checkpoint files are pre-created and the system starts filling the files – the number of checkpoint files pre-created depends on the number of logical processors in the system. As the sample is initially very small, the pre-created files will be mostly empty after initial create.

The following code shows the initial on-disk size for the sample on a machine with 16 logical processors:

SQL

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]  
FROM sys.filegroups f JOIN sys.database_files df   
   ON f.data_space_id=df.data_space_id  
WHERE f.type=N'FX'  
 
On-disk size in MB
2312

As you can see, there is a big discrepancy between the on-disk size of the checkpoint files, which is 2.3 GB, and the actual data size, which is closer to 30 MB.

Looking closer at where the disk-space utilization comes from, you can use the following query. The size on disk returned by this query is approximate for files with state in 5 (REQUIRED FOR BACKUP/HA), 6 (IN TRANSITION TO TOMBSTONE), or 7 (TOMBSTONE).

SQL

SELECT state_desc  
 , file_type_desc  
 , COUNT(*) AS [count
 , SUM(CASE  
   WHEN state = 5 AND file_type=0 THEN 128*1024*1024  
   WHEN state = 5 AND file_type=1 THEN 8*1024*1024  
   WHEN state IN (6,7) THEN 68*1024*1024  
   ELSE file_size_in_bytes  
    END) / 1024 / 1024 AS [on-disk size MB]   
FROM sys.dm_db_xtp_checkpoint_files  
GROUP BY state, state_desc, file_type, file_type_desc  
ORDER BY state, file_type  

For the initial state of the sample, the result will look something like for a server with 16 logical processors:

State_descFile_type_descCountOn-disk size mb
PRECREATEDDATA162048
PRECREATEDDELTA16128
UNDER CONSTRUCTIONDATA1128
UNDER CONSTRUCTIONDELTA18

As you can see, most of the space is used by precreated data and delta files. SQL Server pre-created one pair of (data, delta) files per logical processor. In addition, data files are pre-sized at 128 MB, and delta files at 8 MB, in order to make inserting data into these files more efficient.

The actual data in the memory-optimized tables is in the single data file.

After running the workload

After a single test run that inserts 10 million sales orders, the overall on-disk size looks something like this (for a 16-core test server):

SQL

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]  
FROM sys.filegroups f JOIN sys.database_files df   
   ON f.data_space_id=df.data_space_id  
WHERE f.type=N'FX'  
 
On-disk size in MB
8828

The on-disk size is close to 9 GB, which comes close to the in-memory size of the data.

Looking more closely at the sizes of the checkpoint files across the various states:

SQL

SELECT state_desc  
 , file_type_desc  
 , COUNT(*) AS [count
 , SUM(CASE  
   WHEN state = 5 AND file_type=0 THEN 128*1024*1024  
   WHEN state = 5 AND file_type=1 THEN 8*1024*1024  
   WHEN state IN (6,7) THEN 68*1024*1024  
   ELSE file_size_in_bytes  
    END) / 1024 / 1024 AS [on-disk size MB]   
FROM sys.dm_db_xtp_checkpoint_files  
GROUP BY state, state_desc, file_type, file_type_desc  
ORDER BY state, file_type  
 
state_descfile_type_desccounton-disk size MB
PRECREATEDDATA162048
PRECREATEDDELTA16128
UNDER CONSTRUCTIONDATA1128
UNDER CONSTRUCTIONDELTA18

We still have 16 pairs of pre-created files, ready to go as checkpoints are closed.

There is one pair under construction, which is used until the current checkpoint is closed. Along with the active checkpoint files this gives about 6.5 GB of disk utilization for 6.5 GB of data in memory. Recall that indexes are not persisted on disk, and thus the overall size on disk is smaller than the size in memory in this case.

After demo reset

After demo reset, disk space is not reclaimed immediately if there is no transactional workload on the system, and there are not database checkpoints. For checkpoint files to be moved through their various stages and eventually be discarded, a number of checkpoints and log truncation events need to happen, to initiate merge of checkpoint files, as well as to initiate garbage collection. These will happen automatically if you have a transactional workload in the system [and take regular log backups, in case you are using the FULL recovery model], but not when the system is idle, as in a demo scenario.

In the example, after demo reset, you may see something like

SQL

SELECT SUM(df.size) * 8 / 1024 AS [On-disk size in MB]  
FROM sys.filegroups f JOIN sys.database_files df   
   ON f.data_space_id=df.data_space_id  
WHERE f.type=N'FX'  
 
On-disk size in MB
11839

At nearly 12 GB, this is significantly more than the 9 GB we had before the demo reset. This is because some checkpoint file merges have been started, but some of the merge targets have not yet been installed, and some of the merge source files have not yet been cleaned up, as can be seen from the following:

SQL

SELECT state_desc  
 , file_type_desc  
 , COUNT(*) AS [count
 , SUM(CASE  
   WHEN state = 5 AND file_type=0 THEN 128*1024*1024  
   WHEN state = 5 AND file_type=1 THEN 8*1024*1024  
   WHEN state IN (6,7) THEN 68*1024*1024  
   ELSE file_size_in_bytes  
    END) / 1024 / 1024 AS [on-disk size MB]   
FROM sys.dm_db_xtp_checkpoint_files  
GROUP BY state, state_desc, file_type, file_type_desc  
ORDER BY state, file_type  
 
state_descfile_type_desccounton-disk size MB
PRECREATEDDATA162048
PRECREATEDDELTA16128
ACTIVEDATA385152
ACTIVEDELTA381331
MERGE TARGETDATA7896
MERGE TARGETDELTA756
MERGED SOURCEDATA131772
MERGED SOURCEDELTA13455

Merge targets are installed and merged source are cleaned up as transactional activity happens in the system.

After a second run of the demo workload, inserting 10 million sales orders after the demo reset, you will see that the files constructed during the first run of the workload have been cleaned up. If you run the above query several times while the workload is running, you can see the checkpoint files make their way through the various stages.

After the second run of the workload insert 10 million sales orders you will see disk utilization very similar to, though not necessarily the same as after the first run, as the system is dynamic in nature. For example:

SQL

SELECT state_desc  
 , file_type_desc  
 , COUNT(*) AS [count
 , SUM(CASE  
   WHEN state = 5 AND file_type=0 THEN 128*1024*1024  
   WHEN state = 5 AND file_type=1 THEN 8*1024*1024  
   WHEN state IN (6,7) THEN 68*1024*1024  
   ELSE file_size_in_bytes  
    END) / 1024 / 1024 AS [on-disk size MB]   
FROM sys.dm_db_xtp_checkpoint_files  
GROUP BY state, state_desc, file_type, file_type_desc  
ORDER BY state, file_type  
 
state_descfile_type_desccounton-disk size MB
PRECREATEDDATA162048
PRECREATEDDELTA16128
UNDER CONSTRUCTIONDATA2268
UNDER CONSTRUCTIONDELTA216
ACTIVEDATA415608
ACTIVEDELTA41328

In this case, there are two checkpoint file pairs in the ‘under construction’ state, which means multiple file pairs were moved to the ‘under construction’ state, likely due to the high level of concurrency in the workload. Multiple concurrent threads required a new file pair at the same time, and thus moved a pair from ‘precreated’ to ‘under construction’.