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.

 

Introducing: SQLNetHub’s In-Memory OLTP Simulator

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 30 days!