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

  • Paul Brewer

    SSCrazy

    Points: 2827

    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: 1315

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

  • Paul Brewer

    SSCrazy

    Points: 2827

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

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

  • Paul Brewer

    SSCrazy

    Points: 2827

    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;

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

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