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

Introducing In-Memory OLTP Simulator

by

One of the key features originally shipped with SQL Server 2014 was the long-awaited In-Memory OLTP, codenamed as project “Hekaton”. In-Memory OLTP introduced new data structures for optimizing the performance of OLTP workloads. These data structures are called “memory-optimized” tables and along with natively compiled stored procedures, they can offer a huge performance boost when it comes to processing large volumes of data, especially in data environments with high levels of concurrency. In certain cases, the execution time can be up to 50 times faster.

 

Best-Suited Workloads for SQL Server In-Memory OLTP

As its name implies, the In-Memory OLTP Engine is optimized for OLTP workloads. Examples of such workloads include:

  • Staging tables (i.e. DW)
  • High data insert rate (i.e. smart metering)
  • Low latency execution (i.e. investment trading)

Since the early releases (previews, CTPs, RCs) of SQL Server 2014, I have been studying and testing the In-Memory OLTP Engine and of course the inevitable came: I became a fun of it!

Throughout the hundreds of experiments I ran against the In-Memory OLTP Engine, the idea of developing a special simulation tool came up and this is what I am presenting today.

After almost two years of development, today I’m presenting “In-Memory OLTP Simulator“.

Learn more about SQL Server In-Memory OLTP – 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/ Live Demos / Downloadable Resources and more!)

Learn More

What Does In-Memory OLTP Simulator Do?

In-Memory OLTP Simulator enables the IT Professional and Developer to easily test the powerful In-Memory OLTP Engine of SQL Server 2014 (or later) with different simulations and against different workloads.
In the Ultimate Edition of the tool, among other, the user can load data from other sources (i.e. using Linked Servers) into the Simulator’s database and along with the logic of a custom scenario, simulate real life scenarios and get in-depth analytics on performance when utilizing In-Memory Optimization and Natively-Compiled Stored Procedures in SQL Server.
In addition to using In-Memory OLTP Simulator for benchmarking and performance analysis, the user can also use it for resource-sizing as it provides statistics on resource usage (i.e. CPU and Memory) when running simulations.
In-Memory OLTP Simulator - SQLNetHub
Figure 1: In-Memory OLTP Simulator Start Page

 

Try In-Memory OLTP Simulator free for 14 days!

 

How Does it Work?

The free 30-day trial version of In-Memory OLTP Simulator supports six standard scenarios that are executed against three modes (where available):
  • Disk-Based
  • Memory-Optimized
  • Memory-Optimized with Natively-Compiled Stored Procedure
In Disk-Based execution mode, all tables are stored in disk storage. This is the “traditional” execution mode which can be found in all Relational Database Management Systems (RDBMs). The Disk-Based mode is considered as the baseline for all simulations.

 

In Memory-Optimized execution mode, all tables are loaded and processed in-memory. Under this mode, memory-optimized tables can be also combined with disk-based tables if this better suits the user’s needs.

 

In Memory-Optimized with Natively-Compiled Stored Procedure execution mode, the full power of SQL Server In-Memory OLTP Engine is being utilized. Along with the memory-optimized data structures, a new special type of SQL Server stored procedure is used. Natively compiled stored procedures are compiled when they are created (in contrast to the disk-based stored procedures where they are compiled at their first execution) thus achieving significantly faster execution.

 

In-Memory OLTP Simulator undertakes the execution of the scenarios in each mode, monitors the resource usage, logs all statistics and provides rich analytics. Additionally, in the Ultimate Edition of the tool, the user is able to create/modify and import/export custom scenarios as well as get access to multiple processing options and more analytics.
In-Memory OLTP Simulator - SQLNetHub
Figure 2: In-Memory OLTP Simulator Simulation Page.

Available Editions

There are currently two editions of In-Memory OLTP Simulator available:
  • Trial Version: This is the free 14-day trial version of the tool where basic functionality is enabled and there is a time limitation of 14 days.

 

  • Ultimate Edition: This edition contains all the basic functionality plus the ability to create/modify and import/export custom scenarios as well as have access to more analytics. The Ultimate Edition of In-Memory OLTP Simulator can be very useful for organizations or individuals that want to create custom scenarios and simulate real Production data and processes with using In-Memory Optimization in SQL Server.
In-Memory OLTP Simulator - SQLNetHub
Figure 3: In-Memory OLTP Simulator Simulation Page After Running a Simulation Multiple Times.

 

Featured Online Courses:

Views: 3,861