Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL In-Memory OLTP: 100x Faster Transactions with Memory-Optimized Tables

- 29.08.26 - ErcanOPAK

⚡ 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

Related posts:

Why Indexes Sometimes Make Queries Slower

Why Indexes Sometimes Make Queries Slower

SQL Server: Fix a Partition SWITCH That Rejects a Staging Table Whose Schema Looks Identical but Isn...

SQL Server: Fix a Partition SWITCH That Rejects a Staging Table Whose Schema Looks Identical but Isn...

How to solve 'Size property has an invalid size of 0' in C#

How to solve 'Size property has an invalid size of 0' in C#

Views: 13

Post navigation

ASP.NET Core Rate Limiting: Protect Your API from DDoS and Abuse
Docker Networks: Master Container Communication for Microservices

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (981)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (981)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com