CA_SQLHEKATON Load Simulation

Double click on the CA Setup script below then Copy, Paste into a SQL Server 2014 Query Analyser window and execute. See here for a technical summary – https://paulbrewer.wordpress.com/2017/07/30/hekaton-the-good-the-bad-and-the-ugly/.

Copy / Paste only works with Chrome or newer versions of Internet Explorer, a line feed/character return problem corrupts the T-SQL if copied using Internet Explorer 8 or lower.


USE CA_SQLHEKATON
GO

------------------------------------------------------------------------------------------------
-- Set these 4 variables per test
DECLARE @StressLevel int = 1; -- Workload types - 1 for OLTP bias (2 minutes execution time), 2 for Hybrid or 3 for OLAP (20 minutes execution time)
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 @RC int;
DECLARE @TestCase varchar(50);
DECLARE @Description1 VARCHAR(50) = 'Concurrent Requests = 1'
DECLARE @Description2 VARCHAR(50) = 'Stress Level = ' + CAST(@StressLevel AS CHAR(1));
DECLARE @Description3 VARCHAR(50) = 'Baseline';

DECLARE @x INT = 1;
WHILE @x < 11
BEGIN

    IF @x = 1  SET @TestCase = 'OLTP5';
    IF @x = 2  SET @TestCase = 'OLTP4';
    IF @x = 3  SET @TestCase = 'OLTP3';
    IF @x = 4  SET @TestCase = 'OLTP2';
    IF @x = 5  SET @TestCase = 'OLTP1';
    IF @x = 6  SET @TestCase = 'OLAP1';
    IF @x = 7  SET @TestCase = 'OLAP2';
    IF @x = 8  SET @TestCase = 'OLAP3';
    IF @x = 9  SET @TestCase = 'OLAP4';
    IF @x = 10 SET @TestCase = 'OLAP5';

    -- IO - SQL Set Based Queries
    EXECUTE @RC = dbo.sp_HeartBeat
     @Batches_IO = @OLTP_Weighting
    ,@Batches_CPU = 0
    ,@Batches_Hekaton = 0
    ,@NewPatterns = @OLAP_Weighting
    ,@TestCase = @TestCase
    ,@StressLevel = @StressLevel
    ,@Description1 = @Description1
    ,@Description2 = @Description2
    ,@Description3 = @Description3

    -- CPU
    EXECUTE @RC = dbo.sp_HeartBeat
     @Batches_IO = 0
    ,@Batches_CPU = @OLTP_Weighting
    ,@Batches_Hekaton = 0
    ,@NewPatterns = @OLAP_Weighting
    ,@TestCase = @TestCase
    ,@StressLevel = @StressLevel
    ,@Description1 = @Description1
    ,@Description2 = @Description2
    ,@Description3 = @Description3 

    -- Hekaton
    EXECUTE @RC = dbo.sp_HeartBeat
     @Batches_IO = 0
    ,@Batches_CPU = 0
    ,@Batches_Hekaton = @OLTP_Weighting
    ,@NewPatterns = @OLAP_Weighting
    ,@TestCase = @TestCase
    ,@StressLevel = @StressLevel
    ,@Description1 = @Description1
    ,@Description2 = @Description2
    ,@Description3 = @Description3 

    SET @OLTP_Weighting = @OLTP_Weighting / 2;
    SET @OLAP_Weighting = @OLAP_Weighting * 2;
    SET @x = @x + 1;

END;
GO

The graphs below show all PAL template performance counters for Windows Server on the left and SQL Server on the right, gathered during 2 runs of the OLTP / OLAP simulation script above. The first test (2 minutes) at Stress Level = 1 and the second test (20 minutes) at Stress Level = 3.

Hekaton_Chaos

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: