For an instructor lead, in-depth look at learning SQL click below.
The SQL Server In-Memory OLTP, also known as Hekaton, is an integral part of Microsoft SQL Server designed to accelerate applications by storing and manipulating data in the system’s main memory. This technique can dramatically improve the performance of your existing SQL Server applications.
Why In-Memory OLTP?
Compared to regular SQL Server operations where data is stored on disk and processed through the Buffer Pool, In-Memory OLTP is designed to maintain data in memory and work with that data using atomic transactions. This approach minimizes disk I/O operations and improves the performance of your SQL Server applications.
Getting Started
To begin utilizing the benefits of In-Memory OLTP, you need to create a memory-optimized table. Memory-optimized tables function similarly to a classic SQL Server table, with a notable difference being that their data and schema are always resident in memory.
Below is an example of how you can create a memory-optimized table:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[SampleTable_MemoryOptimized] ( [SampleTableId] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), [SampleColumn] NVARCHAR(50) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) |
Working With Memory-Optimized Tables
After the memory-optimized table is created, you can begin performing traditional actions like INSERT, UPDATE, DELETE, and SELECT. The flow of these operations remains the same as with traditional tables. The only difference is that data is stored in-memory which optimizes actions on it. Below is an example.
|
1 2 3 |
INSERT INTO [dbo].[SampleTable_MemoryOptimized] VALUES (1, 'Test') |
Creating Natively Compiled Stored Procedures
In-Memory OLTP isn’t just about tables that are held in memory. To further improve performance, you can create natively compiled stored procedures. These types of procedures are compiled to machine code when they are created, which leads to better execution times and throughput. Here is an example:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE PROCEDURE [dbo].[usp_SampleProcedure_MemoryOptimized] @SampleTableId INT, @SampleColumn NVARCHAR(50) WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english') INSERT INTO [dbo].[SampleTable_MemoryOptimized] (SampleTableId, SampleColumn) VALUES (@SampleTableId, @SampleColumn) END |
In conclusion, In-Memory OLTP is an incredible feature that can drastically improve the performance of your SQL Server applications. The examples above illustrate the basic usage of this feature, but the possibilities are truly endless when you start exploring further.
Further Reading
For more advanced topics on this feature, such as how to manage memory for In-Memory OLTP and how to optimize data access, check out Microsoft’s official documentation at: In-Memory OLTP (In-Memory Optimization).