In-Memory OLTP Simulator: The Experiment

A few days ago we published a 38-page technical paper titled “In-Memory OLTP Simulator: The Experiment“.

In-Memory OLTP Simulator allows the user to easily simulate virtually any workload against the powerful In-Memory OLTP Engine of Microsoft SQL Server.

This paper presents a series of simulations for a specific scenario against different workload sizes. The paper discusses the findings of these simulations by describing how the entire process was designed using In-Memory OLTP Simulator, as well as by observing and analyzing the performance trends after executing the different versions of the scenario against SQL Server’s In-Memory OLTP Engine.

The highlight of the paper is the fact that for a workload of 5M records, I achieved breakthrough performance with aspeedup of 26x over disk-based tables!

You can download the paper here.

26x speedup using In-Memory OLTP
Simulation of Updating 5M Customers: 26x Speedup Using Memory-Optimized Tables

In-Memory OLTP Simulator Editions

In-Memory OLTP Simulator comes in two editions: the Community Edition which is free which provides basic functionality,  and the Ultimate Edition which requires a license and provides advanced functionality.

In the Ultimate Edition of In-Memory OLTP Simulator you have access to premium features. The most significant feature is the ability to create, modify, export and import custom scenarios via Scenario Manager. Custom scenarios follow the same structure like standard scenarios with the difference that it is you who write their definition and this gives you unlimited power in terms of simulating virtually any workload against SQL Server® In-Memory Optimization.

The table below displays the different features available in each edition of In-Memory OLTP Simulator.

Feature Ultimate Edition Community Edition
Run Standard Scenarios Yes Yes
View Current Usage Statistics Yes Yes
Change Number of Records for Standard Scenarios Yes Yes
View Active Scenario Definition Yes Yes
View Current Resource Usage Yes Yes
Stop Simulation Yes Yes
Drop Scenario Yes Yes
Drop All Scenarios Yes Yes
Take Simulator Database Offline Yes Yes
Bring Simulator Database Online Yes Yes
Simulation Statistics Yes Yes
Export Statistics to Text File Yes Yes
Export Graphs to Image File Yes Yes
Print Statistics Yes Yes
View Schema of Tables Yes Yes
View Result of Simulation Yes Yes
Set Emergency Thresholds Yes Yes
Real-Time Resource Monitoring Yes Yes
Generate and Use Multiple Simulation Databases Yes No
Create, Modify and Run Custom Scenarios Yes No
Export Custom Scenarios Yes No
Import Custom Scenarios Yes No
Executive Report Yes No
Print Report Yes No
Export Report Yes No
Send Report in Text Format by Email Yes No


In-Memory OLTP Simulator Frequently Asked Questions (FAQ)

  • What is In-Memory OLTP Simulator?
    In-Memory OLTP Simulator is third-party tool that allows the user to easily create and run scenarios with different workloads against the In-Memory OLTP Engine of SQL Server®.
  • What is a Simulation Scenario?
    A simulation scenario is a set of table and stored procedure definitions that generated the data structures, workload and processes that run during a simulation in In-Memory OLTP Simulator.
  • Which editions of SQL Server® can be used?
    In-Memory OLTP Simulator connects and generates its database only in SQL Server editions that support In-Memory OLTP. Currently this is support in the Evaluation, Developer and Enterprise editions of SQL Server 2014 and SQL Server 2016.
  • What are the differences between the Community and Ultimate Editions?
    The Community Edition which is free, allows running only the six standard scenarios available in the tool. The Ultimate Edition in addition to the basic functionality it allows to create and use custom simulation scenarios, generate and use multiple simulation databases as well as access the Executive Report functionality.
  • Can I modify standard scenarios?
    Standard scenarios cannot be modified neither in the Community or the Ultimate Editions. However, as an additional functionality for all users of the tool, the use can change the number of records for all standard scenarios with maximum number 1 million records.
  • Can I change the number of records for standard scenarios?
    Yes, you can change the number of records for standard scenarios within the range of 100K – 1M records. You can perform this action from Configure  Scenario Manager.
  • Can I generate and connect to multiple Simulator databases?
    Yes, you can do this in the Ultimate Edition.
  • In case a simulation is taking too long can I interrupt it?
    You can forcibly stop the simulation using the functionality provided under the “Emergency” menu. I.e. you can choose to “Stop Simulation” as well as “Drop Scenario” in order to free-up any resources occupied by the scenario’s execution.
  • What does “Initialize Scenario” do?
    It creates all the data structures as defined in the selected simulation scenario’s definition as well as it prepares the data to be processed (i.e. sample data generation, or data from other sources).
  • What is “Disk-Based” mode?
    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.
  • What is “Memory-Optimized” mode?
    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.
  • What is “Memory-Optimized” with “Natively-Compiled Stored Procedure” mode?
    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.

Video: Introducing In-Memory OLTP Simulator

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 different sources (i.e. data from Production databases using Linked Servers) into the Simulator’s database, simulate real-life scenarios with the use of custom scenarios as well as get in-depth analytics.

Please watch the below introductory video: