Microsoft released a whitepaper recently titled ‘In-Memory OLTP – Common Workload Patterns and Migration Considerations’. In it they show how Hekaton performance varies in comparison with traditional SQL Server tables and procedures, and the workload patterns that suit in-memory OLTP. While not disputing the Microsoft whitepaper, it doesn’t include code samples to support the points made, this article does.
This test workload pattern shows that Increasing index sizes and logical reads have a negative impact on Hekaton performance, but conversely the more procedure calls a second the better it performs in comparison with traditional SQL. It uses a Cellular Automation (CA) mathematical database application to place similar demands in Hekaton and a corresponding set of native SQL Server tables and stored procedures.
The test was performed in a single query analyser session so the Hekaton concurrency improvements aren’t considered, the CA application does support multiple concurrent sessions but they weren’t used. Even just the benefit of not having to compile and interpret queries makes a massive difference, 75 times faster in this reproducible test.
Execute the OLTP / OLAP Test Simulation
- First run the Cellular Automation SQL Server 2014 Stress Testing Application setup script https://paulbrewer.wordpress.com/ca_stress_sql2014/
- Start PERFMON then run the ‘OLTP / OLAP Simulation’ script below twice, once with @StressLevel set to 1 then again with it set to 3. When these have both finished then the test is complete so stop PERFMON. https://paulbrewer.wordpress.com/ca_oltp_olap_sim/
OLTP / OLAP Spectrum Analysis and Conclusions
The simulation script varies the workload type as the test progresses, initially favouring OLTP but transitioning gradually to more of an OLAP bias. The ‘OLTP / OLAP Simulator’ script initially makes 512 procedure calls each requesting the generation of 1 new pattern (OLTP Bias), this slides across the OLTP / OLAP spectrum until finally 1 procedure call is made requesting the generation of 512 new patterns (OLAP Bias). The Pie Charts show how long each set of procedures took to complete the requests at different Stress Levels, notice Hekaton performance falling away relative to the native SQL ‘IO’ procedure as the cost of each call increases and the frequency of the calls decreases.
The difference in total execution times between Hekaton and SQL Server stored procedures and queries varies according to the batches called and the cost per batch. When the batch costs are low and frequent (XLTP below) then there are significant gains in using Hekaton, but as cost increases and frequency decreases (OLAP below) then the gains are gradually lost.
Stress Test Description
The OLTP / OLAP bias is heavily influenced by the ‘Stress Level’ parameter in the simulation test script which can be assigned a value of either 1, 2 or 3. The Cellular Automation application generates new patterns based on applying ‘cell comes alive’ / ‘cell dies’ rules to an initial starting pattern. The more complex the starting pattern, the more intensive the subsequent workload.
@StressLevel = 1 (Heavy OLTP Bias Workload)
The initial state includes one simple, 3 Merkle Pattern that oscillates between 2 states, there is very little work involved in constructing the next new pattern.
@StressLevel = 3 (Heavy OLAP Bias Workload)
Gosper Glider Gun patterns are created at Stress Level 3 which give birth to two new glider patterns once every 30th iteration, the workload and IO cost increase gradually as new patterns are created.
The Good – OLTP
Natively Compiled Stored Procedures.
When a procedure is called hundreds of times a second the savings from not having to compile or interpret T-SQL queries can be significant. The graph below shows the total duration of procedures calls for the SQL Server and Hekaton pattern generator stored procedures. Both performed exactly the same work (batch requests and batch size) using very similar queries and indexes, the results in the graph are filtered as below:
- Stress Level = 1
- OLTP 1 to 5 workloads
OLTP bias workloads are Hekatons strength, the total procedure execution time to perform a very similar workload is significantly lower than the corresponding interpreted SQL Server procedure calls using disk based tables. The graph below shows compilation and lock request counters during the OLTP bias test cases.
The Bad – OLAP
Performance deteriorates in Hekaton as the size of the indexes, and logical reads, increases. The graph below shows the total duration of procedures calls for the SQL Server and Hekaton pattern generator stored procedures. Both performed exactly the same work (batch requests and batch size) using very similar queries and indexes, the results in the graph are filtered as below:
- Stress Level = 3
- OLAP 1 to 5 workloads
OLAP bias workloads are Hekatons weakness. Execution times deteriorate in comparison with SQL Server disk based tables and interpreted stored procedures performing the same workload, as the batch size increases. Memory usage also becomes an issue when processing larger indexes, ‘System out of memory’ errors become a risk.
The Ugly – System Out of Memory
Request 1,000 new Hekaton Stress Level 3 patterns in 4 concurrent sessions and the error message below is typically shown after about 80 seconds. The only recovery I have found is to restart the SQL Service and the Hekaton database is in a recovery state for sometime after the instance comes back online. Data lose is typical although I haven’t researched it, an alternative table schema definition in the setup script might resolve this problem.
Performance Monitor showed ‘Memory Node – Stolen Memory, Total Memory’ and ‘Resource Pool – Used Memory’ counters all spiking just prior to the crash.
In specific circumstances, Hekaton is 75 (achieved with these scripts) or more times faster than native SQL procedures. Hekaton performance degrades in comparison with native SQL procedures though as the workload cost per batch increases and the frequency of batch requests decreases. As workload types become more OLAP bias, memory becomes an issue and performance deteriorates.
- ‘SQL Server Internals: In-Memory OLTP’ by Kalen Delaney