SQL Server In-Memory OLTP Part 1 - Article on SQLNetHub

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

by

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!)

Learn More

 

 

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

Featured Online Courses:

 

Read Also:

 

Check our other related SQL Server Performancearticles.

Check our online courses!

Check our eBooks!

Subscribe to our YouTube channel!

Subscribe to our newsletter and stay up to date!

 

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

Views: 4,017