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

Question

Post reply

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John N Hick Ten Centuries Points: 1320 More actions · Answer 1

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 · Answer 2

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

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

Paul Brewer SSCrazy Points: 2955 More actions · Answer 4

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;