In-Memory Optimization in SQL Server: Will my Workload Execute Faster?

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.
However, organizations always make one key question (besides the cost) when it comes to invest in a new technology, for example in this case the question would most probably look like:  “Will we benefit in terms of significant performance and time gain if we invest in this technology?”
The answer, of course, is “It Depends” 🙂

But In-Memory Optimization achieves significant speedups so why say it depends”?

 

Best-Suited Workload Types for In-Memory Optimization in SQL Server

The reason for the classic “it depends” answer is because of the workload types each Organization’s DBMS systems usually process. You see, there is a list of best-suited workload types where In-Memory Optimization can provide significant performance. Some of these are:
  • 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:

Another way is simulation. I’m a big fan of simulation (and I’m sure the rest of the world is too) because with simulation you can process actual production data/workloads in the safeness of an isolated non-production environment equipped with a promising new technology or a new processing method. The results of the simulation can indicate (maybe not in a 100% factor but very close to that) if processing part of your workload or all of it using the In-Memory OLTP Engine in SQL Server can give you the performance gain you are looking for.

 

Learn more about In-Memory OLTP in SQL Server – Enroll to the 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!)

Enroll Now with Discount!

 

 

Video: How to Create a SQL Server In-Memory OLTP-Enabled Database

 

Course Video Preview: Boost SQL Server Database Performance with In Memory OLTP

 

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:

 

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

Loading...

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

© SQLNetHub