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

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

Share on Facebook0Share on Google+0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0
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).

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”?

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.

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. 


For all the above reasons I 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
The available editions of In-Memory OLTP Simulator are:

  • Community Edition (free)
  • Ultimate Edition

The main differences between the two editions is that in Community Edition you can execute only six standard scenarios and change only their number of records whereas in the Ultimate Edition, in addition to the basic functionality, among other, you can create, execute and manage custom scenarios as well get access to an executive report. For a full feature comparison please visit: https://www.inmemoltpsim.com/features

Below you can find the presentation:

Resources:


Recommended eBooks on SQL Server:

Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Tuning SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Administering SQL Server: eBook by SQL Server MVP Artemakis Artemiou
Artemakis Artemiou
Artemakis Artemiou is a Senior SQL Server Architect, Author, Software Developer and a Microsoft Data Platform MVP. He has over 15 years of experience in the IT industry in various roles. Among other, via his initiative SQLEBooks.com, Artemakis authors and publishes eBooks on different topics 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). Additionally he is the founder of the SQLArtBits initiative that aims to provide the technical community with simple, yet powerful and high-quality SQL Server tools. Currently, the highlights of these tools are DBA Security Advisor and In-Memory OLTP Simulator. Artemakis's official website can be found at aartemiou.com. Artemakis's blogs can be found at: SQLNetHub.com and TechHowTos.com.