Hekaton (CA_SQLHekaton) Performance Benchmarking

A Microsoft whitepaper, ‘In-Memory OLTP – Common Workload Patterns and Migration Considerations’, highlights Hekaton performance and describes the workload patterns that suit in-memory OLTP. This is an independent assessment of Hekaton that includes the scripts and processes used so the result can be reproduced and is open to scrutiny.

Benchmarks were taken for OLTP and OLAP bias work loads running as a single request then again in two concurrent requests. This was an attempt to access the performance of in memory tables and compiled stored procedures on a Windows Server 2016 virtual machine with SQL Server 2016 SP1, 8 GB of RAM and 2 vCPU’s. The CA benchmarking process (see references) was invoked using OSTRESS and the parameters below  to simulate first an OLTP bias workload with many small batches, then an OLAP bias workload.

Concurrent Requests @Batches @IO_Benchmark @Hek_Benchmark @NewPatternsInBatch @StressLevel @Description1 @Description2
2 1000 1 0 1 1 OLTP Two Concurrent Requests
2 1000 0 1 1 1 OLTP Two Concurrent Requests
2 10 1 0 100 3 OLAP Two Concurrent Requests
2 10 0 1 100 3 OLAP Two Concurrent Requests
1 1000 1 0 1 1 OLTP One Concurrent Request
1 1000 0 1 1 1 OLTP One Concurrent Request
1 10 1 0 100 3 OLAP One Concurrent Request
1 10 0 1 100 3 OLAP One Concurrent Request

OLTP Bias Workload Results

When processing 1,000 small batches, Hekaton performance was typically four times faster and more consistent mainly due to precompiled stored procedures.

Benchmark Min. 1st Qu Median    Mean 3rd Qu Max
OLTP – SQL IO 0.00 7.00 10.00 16.77 14.00 507.00
OLTP – Hekaton 0.00 3.00 3.00 3.81 4.00 217.00

OLAP Bias Workload Results

When processing 10 larger batches, Hekaton performance was typically five times faster and far more consistent mainly due to lock and latch contention changes.

Benchmark Min. 1st Qu Median    Mean 3rd Qu Max
OLAP – SQL IO 15,050 17,294 23,534 32,275 40,173 88,643
OLAP – Helaton 1,363 4,323 5,630 5,963 7,430 10,040

 

References

 

Sample Scripts

A PowerShell call to OSTRESS (Requires RML tools for SQL Server 2016) to invoke 2 then 1 concurrent requests to the benchmarking process. The script only works when one line is highlighted and run at a time, if the entire script is executed in one go the results are unpredictable.


cd "C:\Program Files\Microsoft Corporation\RMLUtils"
Set-Location .
cls

######################## 
# Hekaton / SQL
# Two concurrent requests
# OLTP
./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log1"
./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log2"
# OLAP
./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1,@Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log3"
./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log4"


######################## 
# Hekaton / SQL
# One concurrent request
# OLTP
./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log1"
./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log2"
# OLAP
./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1,@Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log3"
./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log4"


 

The benchmark results from the query below were saved in a CSV file


SELECT CONVERT(VARCHAR(16),[BatchStartTime],120) AS StartTime
      ,[BatchDurationMS]
      ,[BenchmarkPerspective]
	  ,Description1
	  ,Description2
	  ,Description3
FROM [dbo].[CA_BenchmarkResults]

 

The CSV file was imported into R Studio and graphed using the script below:


rm(list=ls())
ca <- read.csv(file = "c:/DataScience/CA_Hekaton_20170930.csv", header = TRUE, sep = ",", stringsAsFactors = FALSE)
summary(ca)
head(ca)

# Factors
ca$StartTime <- as.factor(ca$StartTime)
ca$AutomationEngine <- as.factor(ca$BenchmarkPerspective)
ca$OLTP_OLAP <- as.factor(ca$Description1)
ca$Concurreny <- as.factor(ca$Description2)

#install.packages("ggplot2")
library("ggplot2")

fill <- "#4271AE"
line <- "#1F3552"

ca_OLTP <- subset(ca, ca$OLTP_OLAP == "OLTP")
ca_OLAP <- subset(ca, ca$OLTP_OLAP == "OLAP")

p1 <- ggplot(ca_OLTP, aes(x = AutomationEngine, y = BatchDurationMS)) + geom_boxplot()
p1 <- p1 + scale_x_discrete(name = "Automation Engine") +
  scale_y_continuous(name = "Mean Benchmark Duration Milliseconds") + 
  geom_boxplot(fill = fill, colour = line)  
p1 <- p1 +  ggtitle("Boxplot of OLTP Duration by Automation Engine - Hekaton or SQL IO")
p1 <- p1 + facet_wrap(~Concurreny)
p1

p1 <- ggplot(ca_OLAP, aes(x = AutomationEngine, y = BatchDurationMS)) + geom_boxplot()
p1 <- p1 + scale_x_discrete(name = "Automation Engine") +
  scale_y_continuous(name = "Mean Benchmark Duration Milliseconds") + 
  geom_boxplot(fill = fill, colour = line)  
p1 <- p1 +  ggtitle("Boxplot of OLAP Duration by Automation Engine - Hekaton or SQL IO")
p1 <- p1 + facet_wrap(~Concurreny)
p1

summary(subset(ca_OLTP$BatchDurationMS,ca_OLTP$AutomationEngine== "HEK"))
summary(subset(ca_OLTP$BatchDurationMS,ca_OLTP$AutomationEngine == "IO "))
summary(subset(ca_OLAP$BatchDurationMS,ca_OLAP$AutomationEngine== "HEK"))
summary(subset(ca_OLAP$BatchDurationMS,ca_OLAP$AutomationEngine == "IO "))

 



Categories: Hekaton, Performance Benchmarking

%d bloggers like this: