Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This article is for the developer who is in a hurry to learn the basics of the In-Memory OLTP performance features of Microsoft SQL Server and Azure SQL Database.
For In-Memory OLTP, this article provides the following:
SQL Server and SQL Database have only minor variations in their support of In-Memory technologies.
In the wild some bloggers refer to the In-Memory OLTP as Hekaton.
SQL Server provides In-Memory features that can greatly improve the performance of many application systems. The most straight forward considerations are described in this section.
Systems that must process large numbers of SQL INSERTs concurrently are excellent candidates for the OLTP features.
Systems which process heavy calculations in Transact-SQL are excellent candidates.
Later you might visit the following articles which offer demonstrations of performance gains from In-Memory OLTP:
In-Memory Analytics refers to SQL SELECTs which aggregate transactional data, typically by inclusion of a GROUP BY clause. The index type called columnstore is central to operational analytics.
There are two major scenarios:
The present article focuses on OLTP, and not on Analytics. For information on how columnstore indexes bring Analytics to SQL, see:
A sequence of excellent blog posts elegantly explains columnstore indexes from several perspectives. The majority of the posts describe further the concept of real-time operational analytics, which columnstore supports. These posts were authored by Sunil Agarwal, a Program Manager at Microsoft, in March 2016.
Let's look at the main features of In-Memory OLTP.
The T-SQL keyword MEMORY_OPTIMIZED, on the CREATE TABLE statement, is how a table is created to exist in active memory, instead of on disk.
A Memory-optimized tables has one representation of itself in active memory, and secondary copy on the disk.
The T-SQL keyword NATIVE_COMPILATION, on the CREATE PROCEDURE statement, is how a natively compiled stored procedure is created. The T-SQL statements are compiled to machine code on first use of the native proc each time the database is cycled online. The T-SQL instructions no longer endure slow interpretation of every instruction.
A native module can reference memory-optimized tables only, and it cannot reference disk-based tables.
There are three types of natively compiled modules:
In-Memory OLTP and Columnstore are available in Azure SQL Database. For details see Optimize Performance using In-Memory Technologies in SQL Database.
This section begins a sequence of numbered sections that together demonstrate the Transact-SQL syntax you can use to implement In-Memory OLTP features.
First, it is important that your database be set to a compatibility level of at least 130. Next is the T-SQL code to view the current compatibility level that your current database is set to.
SELECT d.compatibility_level
FROM sys.databases as d
WHERE d.name = Db_Name();
Next is the T-SQL code to update the level, if necessary.
ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 130;
When a transaction involves both a disk-based table and a memory-optimized table, we call that a cross-container transaction. In such a transaction it is essential that the memory-optimized portion of the transaction operate at the transaction isolation level named SNAPSHOT.
To reliably enforce this level for memory-optimized tables in a cross-container transaction, alter your database setting by executing the following T-SQL.
ALTER DATABASE CURRENT
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
On Microsoft SQL Server, before you can create a memory-optimized table you must first create a FILEGROUP that you declare CONTAINS MEMORY_OPTIMIZED_DATA. The FILEGROUP is assigned to your database. For details see:
On Azure SQL Database, you need not and cannot create such a FILEGROUP.
The following sample T-SQL script enables a database for In-Memory OLTP and configures all recommended settings. It works with both SQL Server and Azure SQL Database: enable-in-memory-oltp.sql.
Note that not all SQL Server features are supported for databases with a MEMORY_OPTIMIZED_DATA filegroup. For details on limitations see: Unsupported SQL Server Features for In-Memory OLTP
The crucial Transact-SQL keyword is the keyword MEMORY_OPTIMIZED.
CREATE TABLE dbo.SalesOrder
(
SalesOrderId integer not null IDENTITY
PRIMARY KEY NONCLUSTERED,
CustomerId integer not null,
OrderDate datetime not null
)
WITH
(MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA);
Transact-SQL INSERT and SELECT statements against a memory-optimized table are the same as for a regular table.
ALTER TABLE...ADD/DROP can add or remove a column from a memory-optimized table, or an index.
The crucial keyword is NATIVE_COMPILATION.
CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId
@_CustomerId INT
WITH
NATIVE_COMPILATION,
SCHEMABINDING
AS
BEGIN ATOMIC
WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english')
DECLARE @SalesOrderId int, @OrderDate datetime;
SELECT TOP 1
@SalesOrderId = s.SalesOrderId,
@OrderDate = s.OrderDate
FROM dbo.SalesOrder AS s
WHERE s.CustomerId = @_CustomerId
ORDER BY s.OrderDate DESC;
RETURN @SalesOrderId;
END;
The keyword SCHEMABINDING means the tables referenced in the native proc cannot be dropped unless the native proc is dropped first. For details see Creating Natively Compiled Stored Procedures.
Note that you do not need to create a natively compiled stored procedure to access a memory-optimized table. You can also reference memory-optimized tables from traditional stored procedures and ad hoc batches.
Populate the table with two rows of data.
INSERT into dbo.SalesOrder
( CustomerId, OrderDate )
VALUES
( 42, '2013-01-13 03:35:59' ),
( 42, '2015-01-15 15:35:59' );
An EXECUTE call to the natively compiled stored procedure follows.
DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
EXECUTE @LatestSalesOrderId =
ncspRetrieveLatestSalesOrderIdForCustomerId 42;
SET @mesg = CONCAT(@LatestSalesOrderId,
' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;
Here is the actual PRINT output:
-- 2 = Latest SalesOrderId, for CustomerId = 42
The preceding plain examples give you a foundation for learning the more advanced features of In-Memory OLTP. The following sections are a guide to the special considerations you might need to know, and to where you can see the details about each.
The following subsections briefly describe how the In-Memory OLTP features work internally to provide improved performance.
Dual nature: A memory-optimized table has a dual nature: one representation in active memory, and the other on the hard disk. Each transaction is committed to both representations of the table. Transactions operate against the much faster active memory representation. Memory-optimized tables benefit from the greater speed of active memory versus the disk. Further, the greater nimbleness of active memory makes practical a more advanced table structure that is optimized for speed. The advanced structure is also pageless, so it avoids the overhead and contention of latches and spinlocks.
No locks: The memory-optimized table relies on an optimistic approach to the competing goals of data integrity versus concurrency and high throughput. During the transaction, the table does not place locks on any version of the updated rows of data. This can greatly reduce contention in some high volume systems.
Row versions: Instead of locks, the memory-optimized table adds a new version of an updated row in the table itself, not in tempdb. The original row is kept until after the transaction is committed. During the transaction, other processes can read the original version of the row.
Less logging: The before and after versions of the updated rows are held in the memory-optimized table. The pair of rows provides much of the information that is traditionally written to the log file. This enables the system to write less information, and less often, to the log. Yet transactional integrity is ensured.
Converting a regular interpreted stored procedure into a natively compiled stored procedure greatly reduces the number of instructions to execute during run time.
As is common in computer science, the performance gains provided by the In-Memory features are a trade-off. The better features bring benefits that are more valuable than the extra costs of the feature. You can find comprehensive guidance about the trade-offs at:
The rest of this section lists some of the major planning and trade-off considerations.
Estimate memory: You must estimate the amount of active memory that your memory-optimized table will consume. Your computer system must have adequate memory capacity to host a memory-optimized table. For details see:
Partition your large table: One way to meet the demand for lots of active memory is to partition your large table into parts in-memory that store hot recent data rows versus other parts on the disk that store cold legacy rows (such as sales orders that have been fully shipped and completed). This partitioning is a manual process of design and implementation. See:
Indexes for Memory-Optimized Tables are different in some ways from indexes on traditional on-disk tables. Hash Indexes are available only on memory-optimized tables.
You must plan to ensure there will be sufficient active memory for your planned memory-optimized table and its indexes. See:
A memory-optimized table can be declared with DURABILITY = SCHEMA_ONLY:
Table variables can also be declared as memory-optimized. See:
The types of natively compiled modules available through Transact-SQL are:
A natively compiled user defined function (UDF) runs faster than an interpreted UDF. Here are some things to consider with UDFs:
For test data and explanation about the performance of native UDFs, see:
Refer to these other articles that discuss special considerations for memory-optimized tables:
The following article, and its children articles in the table of contents (TOC), explain the details about natively compiled stored procedures.
Here are articles that offer code to demonstrate the performance gains you can achieve by using In-Memory OLTP:
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Learning path
Use advance techniques in canvas apps to perform custom updates and optimization - Training
Use advance techniques in canvas apps to perform custom updates and optimization
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.