Use in-memory OLTP in Azure SQL Database to improve your application performance
Applies to:
Azure SQL Database
In-memory OLTP can be used to improve the performance of transaction processing, data ingestion, and transient data scenarios, without increasing the pricing tier.
- Premium (DTU) and Business Critical (vCore) tier databases support in-memory OLTP tables.
- Hyperscale supports a subset of in-memory OLTP objects, but does not include memory-optimized tables. For more information, see Hyperscale limitations.
Follow these steps to adopt in-memory OLTP in your existing database.
Step 1: Ensure you are using a Premium or Business Critical tier database
In-memory OLTP is supported only in Premium (DTU) and Business Critical (vCore) tier of Azure SQL Database. In-memory OLTP is supported if the returned result is 1
(not 0
):
SELECT DatabasePropertyEx(Db_Name(), 'IsXTPSupported');
XTP stands for Extreme Transaction Processing.
Step 2: Identify objects to migrate to in-memory OLTP
SQL Server Management Studio (SSMS) includes a Transaction Performance Analysis Overview report that you can run against a database with an active workload. The report identifies tables and stored procedures that are candidates for migration to in-memory OLTP.
In SSMS, to generate the report:
- In the Object Explorer, right-click your database node.
- Select Reports > Standard Reports > Transaction Performance Analysis Overview.
For more information on assessing the benefits of in-memory OLTP, see Determining if a table or stored procedure should be ported to in-memory OLTP.
Step 3: Create a comparable test database
Suppose the report indicates your database has a table that would benefit from being converted to a memory-optimized table. We recommend that you first test to confirm the indication by testing.
You need a test copy of your production database. The test database should be at the same service tier level as your production database.
To ease testing, tweak your test database as follows:
Connect to the test database by using SQL Server Management Studio (SSMS).
To avoid needing the
WITH (SNAPSHOT)
option in queries, set the current database'sMEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT
option, as shown in the following T-SQL statement:ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
Step 4: Migrate tables
You must create and populate a memory-optimized copy of the table you want to test. You can create it by using either:
- The handy Memory Optimization Wizard in SSMS.
- Use T-SQL commands.
Memory Optimization Wizard in SSMS
To use this migration option:
Connect to the test database with SSMS.
In the Object Explorer, right-click on the table, and then select Memory Optimization Advisor.
The Table Memory Optimizer Advisor wizard is displayed.
In the wizard, select Migration validation (or the Next button) to see if the table has any unsupported features that are unsupported in memory-optimized tables. For more information, see:
- The memory optimization checklist in Memory Optimization Advisor.
- Transact-SQL Constructs Not Supported by in-memory OLTP.
- Migrating to in-memory OLTP.
If the table has no unsupported features, the advisor can perform the actual schema and data migration for you.
Manual T-SQL
To use this migration option:
- Connect to your test database by using SSMS (or a similar utility).
- Obtain the complete T-SQL script for your table and its indexes.
- In SSMS, right-click your table node.
- Select Script Table As > CREATE To > New Query Window.
- In the script window, add
WITH (MEMORY_OPTIMIZED = ON)
to theCREATE TABLE
statement. - If there is a CLUSTERED index, change it to NONCLUSTERED.
- Rename the existing table by using sp_rename.
- Create the new memory-optimized copy of the table by running your edited
CREATE TABLE
script. - Copy the data to your memory-optimized table by using
INSERT...SELECT * INTO
:INSERT INTO [<new_memory_optimized_table>] SELECT * FROM [<old_disk_based_table>];
Step 5 (optional): Migrate stored procedures
The in-memory feature can also modify a stored procedure for improved performance.
Considerations with natively compiled stored procedures
A natively compiled stored procedure must have the following options on its T-SQL WITH
clause:
- NATIVE_COMPILATION: meaning the Transact-SQL statements in the procedure are all compiled to native code for efficient execution.
- SCHEMABINDING: meaning tables that the stored procedure cannot have their column definitions changed in any way that would affect the stored procedure, unless you drop the stored procedure.
A native module must use one big ATOMIC block for transaction management. There is no role for an explicit BEGIN TRANSACTION
or ROLLBACK TRANSACTION.
If your code detects a violation of a business rule, it can terminate the atomic block with a THROW statement.
Typical CREATE PROCEDURE for natively compiled
Typically the T-SQL to create a natively compiled stored procedure is similar to the following template:
CREATE PROCEDURE schemaname.procedurename
@param1 type1, ...
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'<desired sys.syslanuages.sysname value>'
)
...
END;
- For the
TRANSACTION_ISOLATION_LEVEL
, SNAPSHOT is the most common value for the natively compiled stored procedure. However, a subset of the other values is also supported:- REPEATABLE READ
- SERIALIZABLE
- The
LANGUAGE
value must be present in thesys.syslanguages
view, in thename
column. For example,N'us_english'
.
How to migrate a stored procedure
The migration steps are:
- Obtain the
CREATE PROCEDURE
script to the regular interpreted stored procedure. - Rewrite its header to match the previous template.
- Determine whether the stored procedure T-SQL code uses any features that are not supported for natively compiled stored procedures. Implement workarounds if necessary. For more information, see Migration issues for natively compiled stored procedures.
- Rename the old stored procedure by using sp_rename. Or simply DROP it.
- Run your edited
CREATE PROCEDURE
T-SQL script.
Step 6: Run your workload in test
Run a workload in your test database that is similar to the workload that runs in your production database. This should reveal the performance gain achieved by your use of the in-memory feature for tables and stored procedures.
Major attributes of the workload are:
- Number of concurrent connections.
- Read/write ratio.
To tailor and run the test workload, consider using the handy ostress.exe tool. For more information, see In-memory sample in Azure SQL Database.
To minimize network latency, run your test in the same Azure geographic region where the database exists.
Step 7: Post-implementation monitoring
Consider monitoring the performance effects of your in-memory implementations in production: