In-Memory OLTP in SQL Server 2014 or later is a powerful engine integrated into the SQL Server Engine, optimized for Online Transaction Processing (OLTP). In-Memory Optimization in SQL Server, has certain characteristics and can help increasing performance in a great number of scenarios.
A Short Description of the SQL Server In-Memory OLTP Technology
If I was about to describe this technology in two simple sentences I would do so with the below:
- It eliminates both locks and latches with an optimistic multi-version concurrency control mechanism.
- It introduces memory-optimized tables and natively-compiled stored procedures.
But In-Memory Optimization achieves significant speedups so why say it depends”?
Best-Suited Workload Types for In-Memory Optimization in SQL Server
- High Data Insert Rate
- Examples: Smart Metering, System Telemetry
- Read Performance and Scale
- Example: Social Network Browsing
- Compute Heavy Data Processing
- Examples: Manufacturing supply chains or retailers
- Low latency Execution
- Examples: Online Gaming Platforms, Capital Markets
- Session State Management
- Example: Managing sessions (i.e. user requests, etc.) for a heavily-visited websites
This of course does not mean that other workload types that are not in the above list cannot benefit from In-Memory Optimization but for them a more thorough investigation/study will be required.
How to Check if In-Memory Optimization in SQL Server can Help You
There are many ways to investigate if using In-Memory Optimization in SQL Server could give you the performance gain for you are looking for:
- Reading Case Studies
- Different Tests
- Using Memory Optimization Advisor Tool in SQL server
Introducing: SQLNetHub’s In-Memory OLTP Simulator
- Memory-Optimized with Natively-Compiled Stored Procedure
Try In-Memory OLTP Simulator free for 30 days!
- Introducing In-Memory Optimization in SQL Server
- In-Memory Optimization in SQL Server: A Simple Experiment
- Getting Started with SQL Server In-Memory OLTP – Part 1
- Getting Started with SQL Server In-Memory OLTP – Part 2
- Where are temporary tables stored in SQL Server?
- SQL Server 2016: TempDB Enhancements
- tempdb growth
Check our other related SQL Server Performance articles.
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).