Hi friends! In this article, we will be answering different frequently asked questions about SQL Server In-Memory OLTP.
We will begin with the basics and then move to more advanced-level questions.
What is SQL Server In-Memory OLTP?
SQL Server In-Memory OLTP, is a powerful engine, seamlessly integrated into the SQL Server Engine (version 2014 and later), optimized for Online Transaction Processing (OLTP). In-Memory OLTP, introduces specific data structures for optimizing the performance of OLTP workloads. So, you can have memory-optimized tables, natively-compiled stored procedures, memory-optimized table variables, etc.
What do I need for Using In-Memory OLTP in SQL Server? Is there any additional cost?
There is no additional cost for using In-Memory OLTP in SQL Server. You just need to have a SQL Server version and edition, that allows you to use it. Also, when you use In-Memory OLTP in SQL Server, it goes without saying that, depending on the volume of data you will be processing in-memory, you will also need to have the proper RAM capacity on the database server.
Which versions and editions of SQL Server support In-Memory OLTP?
In-Memory OLTP was initially released with SQL Server 2014. Therefore, it is supported in SQL Server 2014 Enterprise, Developer and Evaluation editions.
Since SQL Server 2016 SP1, In-Memory OLTP has become available in more editions of SQL Server. Therefore, it is supported from SQL Server 2016 SP1 onwards, on the following editions of SQL Server:
- Enterprise Edition
- Developer Edition
- Evaluation Edition
- Standard Edition
- Web Edition
- Express Edition
Of course, something to take into consideration when using In-Memory OLTP with specific editions of SQL Server 2016 SP1 and later (i.e. SQL Server 2016 SP1 Express) are other limitations that might be limiting the usage of In-Memory OLTP (i.e. max RAM allowed per SQL instance, max size per database, etc.).
What are Memory-Optimized Tables?
Memory-optimized tables, are special data structures in SQL Server, that store their data into memory using multiple versions of each row’s data. This technique is characterized as “Non-blocking multi-version optimistic concurrency control” and eliminates both Locks and Latches thus achieving breakthrough performance.
What happens to my data in-memory when the database server is restarted?
There are two scenarios regarding this question, which are related to the type of memory-optimized tables that you use.
Note that there are two types of memory-optimized tables: (i) Durable, and (ii) Non-Durable.
Therefore, when creating a memory-optimized table (or migrating a disk-based table to a memory-optimized), you have two options:
- Option 1: Create a durable memory-optimized table which means that in the case of server crash or failover the data will be available as they will be recovered from transaction logs.
- Option 2: Create a non-durable table which means that in the case of a server crash or failover the data in the table will be lost.
So, it is up to you which memory-optimized table to use, depending of course of the nature of the data to be stored in each table.
What are Natively-Compiled Stored Procedures?
Natively-compiled stored procedures, are SQL Server objects that can access only memory-optimized data structures such as memory-optimized tables, table variables, etc. Natively-compiled stored procedures are compiled to native code (DLL) upon their creation (in contrast to interpreted stored procedures where they are compiled at first execution), along with some aggressive optimizations that take place during this phase, which is something that makes them significantly faster than interpreted stored procedures.
Can In-Memory OLTP improve the performance of any workload?
Generally speaking, In-Memory OLTP, is a technology that can definitely help the performance of your SQL Server workloads. However, as its name implies, it can significantly improve the performance of OLTP workloads. To this end, below, you can see a list of such workload types that can benefit the most when using SQL Server In-Memory OLTP:
- 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, which is something that leads to the next frequently asked question.
How can I check if SQL Server In-Memory Optimization can improve the performance of my workload?
There are different techniques that you can use, in order to assess whether SQL Server In-Memory optimization can improve the performance of a specific workload type.
To this end, you can:
- Read case studies
- Perform different tests
- Use the Memory Optimization Advisor tool in SQL server
- Perform simulations
Regarding the latter, you might want to check our software tool named “In-Memory OLTP Simulator“. With In-Memory OLTP Simulator, you can easily benchmark standard and custom scenarios of data workloads and processing against the powerful In-Memory OLTP Engine in SQL Server, get rich analytics and insights, and see how Memory Optimization in SQL Server can help you boost the performance of your data processes.
Is it complex to use In-Memory OLTP in SQL Server?
No, it is not complex at all. On the contrary, it is very easy, since the In-Memory OLTP Engine is seamlessly integrated in the SQL Server Engine. To this end, in order to start using In-Memory OLTP, you just need to decide which tables will be defined as memory-optimized tables, as well as, which database processes will be migrated to natively-compiled stored procedures. This of course, it is something that requires careful analysis of your data processes and structures involved.
Do I need to change my application’s logic or code for using In-Memory OLTP?
No, you do not need to change anything in the application for using In-Memory OLTP. Any changes (i.e. switching to memory-optimized tables and natively-compiled stored procedures) are performed only on the database-level.
Get Started with SQL Server In-Memory OLTP – Enroll to our Course!
We recommend enrolling to our online course titled “Boost SQL Server Database Performance with In-Memory OLTP” and learn how to harness the power of SQL Server’s In-Memory Optimization and boost the performance of your data processes!
In this course, you will learn all about SQL Server’s In-Memory Database Processing Engine also known as In-Memory OLTP. To this end, you will learn how to enable In-Memory OLTP in SQL Server, what memory-optimized tables and natively-compiled stored procedures are, as well as, how to boost the performance of your data processes using this powerful SQL Server feature.
Video: Frequently Asked Questions about SQL Server In-Memory OLTP
Video: How to Create a SQL Server In-Memory OLTP-Enabled Database
Course Video Preview: Boost SQL Server Database Performance with In Memory OLTPEnroll to the course!
Introducing In-Memory OLTP Simulator: A Simulation Tool for In-Memory OLTP
- Memory-Optimized with Natively-Compiled Stored Procedure
Try In-Memory OLTP Simulator free for 14 days!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- Boost SQL Server Database Performance with In-Memory OLTP
- 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
SQL Server In-Memory OLTP Official Documentation:
Check our other related SQL Server Performancearticles.
Subscribe to our newsletterand 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 Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. 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. Moreover, Artemakis teaches on Udemy, you can check his courses here.