Boost SQL Server Database Performance with In-Memory OLTP

Frequently Asked Questions About SQL Server In-Memory OLTP

by

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.

Boost SQL Server Database Performance with In-Memory OLTP - Online Course
(Lifetime Access, Q&A, Certificate of Completion and more!)

Learn More

 

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 OLTP

Enroll to the course!

 

 

Introducing In-Memory OLTP Simulator: A Simulation Tool for In-Memory OLTP

For all the above reasons we created “In-Memory OLTP Simulator“. In-Memory OLTP Simulator enables the user to easily test the powerful In-Memory OLTP Engine of SQL Server 2014 (or later) against different workloads via a simple and intelligent GUI. In-Memory OLTP Simulator features three simulation modes which are:
    • Disk-Based
    • Memory-Optimized
    • Memory-Optimized with Natively-Compiled Stored Procedure

 

In-Memory OLTP Simulator - SQLNetHub
Main screen of In-Memory OLTP Simulator

 

The available editions of In-Memory OLTP Simulator are:
In-Memory OLTP Simulator - Simulation Statistics Page - SQLNetHub
In-Memory OLTP Simulator – Simulation Statistics Page

 

Try In-Memory OLTP Simulator free for 14 days!

 

 

Featured Online Courses:

 

Read Also:

 

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: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 2,287