One of the key features 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. 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
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.
|Figure 1: In-Memory OLTP Simulator Start Page.|
How Does it Work?
The Community Edition (free) of In-Memory OLTP Simulator supports six standard scenarios that are executed against three modes (where available):
- 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.
|Figure 2: In-Memory OLTP Simulator Simulation Page.|
There are currently two editions of In-Memory OLTP Simulator available:
- Community Edition: This is the free version of the tool where basic functionality is enabled.
- 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.
|Figure 3: In-Memory OLTP Simulator Simulation Page After Running a Simulation Multiple Times.|
In-Memory OLTP Simulator will be released on July 8 2015!