Cellular Automation – SQL Server CPU, IO and Hekaton Stress Testing

• Paul Brewer

  SSCrazy

  Points: 2955

  More actions

  June 15, 2015 at 12:05 am

  #398589

  Comments posted to this topic are about the item Cellular Automation – SQL Server CPU, IO and Hekaton Stress Testing

• John N Hick

  Ten Centuries

  Points: 1320

  More actions

  June 17, 2015 at 9:00 am

  #1806096

  Thanks for taking the time to write and post this, Paul. This was a very, very deep dive!

• Paul Brewer

  SSCrazy

  Points: 2955

  More actions

  June 17, 2015 at 9:19 am

  #1806106

  Thank you for the positive feedback John, very much appreciated.

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  June 25, 2015 at 8:11 am

  #1808079

  Whoa. This is going to require a re-read when I have time to concentrate. Thanks.

• Paul Brewer

  SSCrazy

  Points: 2955

  More actions

  June 25, 2015 at 11:44 am

  #1808177

  Hi,

  If you are interested in how quick Hekaton is, run the SQLServer2014 setup script attached to the article then the script below in 1 or more Query Analyser sessions. It will stress the instance it's running on for up to 20 minutes so needs to be an isolated server. Notice the duration_seconds differences in the timing results between SQL & Hekaton, Hekaton is really quick for the OLTP bias workload types. Thanks for looking at it.

  Paul

  USE CellularAutomation

  GO

  ------------------------------------------------------------------------------------------------

  -- Set these 2 variables per test

  DECLARE @StressLevel int = 3; -- Workload type biase - 1 for OLTP or 3 for OLAP

  DECLARE @ConcurrentRequests VARCHAR(3) = '1'; -- How many concurrent requests are running?

  ------------------------------------------------------------------------------------------------

  DECLARE @rc int;

  DECLARE @TestCase varchar(50);

  DECLARE @OLTP_Weighting INT = 512; -- Divided by 2 for each iteration of x

  DECLARE @OLAP_Weighting INT = 1; -- Multiplied by 2 for each iteration of x

  DECLARE @x INT = 1;

  WHILE @x < 11

  BEGIN

  IF @x = 1 SET @TestCase = 'OLTP5 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));

  IF @x = 2 SET @TestCase = 'OLTP4 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 3 SET @TestCase = 'OLTP3 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 4 SET @TestCase = 'OLTP2 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 5 SET @TestCase = 'OLTP1 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 6 SET @TestCase = 'OLAP1 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 7 SET @TestCase = 'OLAP2 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 8 SET @TestCase = 'OLAP3 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 9 SET @TestCase = 'OLAP4 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  IF @x = 10 SET @TestCase = 'OLAP5 - StressLevel' + CAST(@StressLevel AS CHAR(1)) + ' - CR' + CAST(@ConcurrentRequests AS VARCHAR(3));;

  EXECUTE @rc = [dbo].[CA_LoadTestExecute]

  @OLTP_Weighting -- IO_Batches (OLTP Weighting Factor - Generator Procedure Calls)

  ,0 -- CPU Batches (OLTP Weighting Factor - Generator Procedure Calls)

  ,@OLTP_Weighting -- Hekaton Batches (OLTP Weighting Factor - Generator Procedure Calls)

  ,@OLAP_Weighting -- New Patterns Requested per call - OLAP Weighting Factor 1

  ,@TestCase

  ,@StressLevel; -- Initial Patterns - OLAP Weighting Factor 2

  SET @OLTP_Weighting = @OLTP_Weighting / 2;

  SET @OLAP_Weighting = @OLAP_Weighting * 2;

  SET @x = @x + 1;

  END;

  EXECUTE dbo.CA_LoadTestResults;