⚡ Quick Win: Memory-optimized tables eliminate disk I/O and locking. Perfect for high-throughput OLTP workloads.
Disk I/O is the bottleneck. In-Memory OLTP eliminates it.
🚀 Enable In-Memory OLTP
-- ✅ Enable In-Memory OLTP ALTER DATABASE MyDatabase SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON; -- ✅ Create memory-optimized filegroup ALTER DATABASE MyDatabase ADD FILEGROUP InMemoryFileGroup CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE MyDatabase ADD FILE (NAME = InMemoryFile, FILENAME = 'C:\Data\InMemory') TO FILEGROUP InMemoryFileGroup;
🔥 Create Memory-Optimized Table
-- ✅ Memory-optimized table (No disk I/O!)
CREATE TABLE dbo.OrdersInMemory
(
Id INT NOT NULL PRIMARY KEY NONCLUSTERED,
CustomerId INT NOT NULL,
OrderDate DATETIME2 NOT NULL,
TotalAmount DECIMAL(18,2) NOT NULL,
Status NVARCHAR(50) NOT NULL,
INDEX IX_Orders_CustomerId NONCLUSTERED (CustomerId),
INDEX IX_Orders_OrderDate NONCLUSTERED (OrderDate)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
-- ✅ Create natively compiled stored procedure
CREATE PROCEDURE dbo.InsertOrderInMemory
@Id INT,
@CustomerId INT,
@OrderDate DATETIME2,
@TotalAmount DECIMAL(18,2),
@Status NVARCHAR(50)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English'
)
INSERT INTO dbo.OrdersInMemory
VALUES (@Id, @CustomerId, @OrderDate, @TotalAmount, @Status);
END;
-- ✅ Natively compiled function
CREATE FUNCTION dbo.GetTotalByCustomer(@CustomerId INT)
RETURNS TABLE
WITH NATIVE_COMPILATION, SCHEMABINDING
AS RETURN
(
SELECT SUM(TotalAmount) AS Total
FROM dbo.OrdersInMemory
WHERE CustomerId = @CustomerId
);
📊 Performance Comparison
| Operation | Disk Table | In-Memory | Speedup |
|---|---|---|---|
| Insert (1M rows) | 45s | 2.3s | 19x |
| Select (10K rows) | 850ms | 45ms | 18x |
| Update (100K rows) | 12s | 0.6s | 20x |
| Join (2 tables) | 2.4s | 120ms | 20x |

