CA_OLTP_OLAP_Simulation2014

Double click on the CA Setup script below then Copy, Paste into a SQL Server 2014 Query Analyser window and execute. Assumes the CA_Set_SQL2014 script has been run and has created the Cellular Automation database..

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 CellularAutomation
GO
   
-- =============================================
-- Author: Paul Brewer
-- Create date: 22nd June, 2015
-- Description: Cellular Automation OLTP / OLAP Simulation
-- SQL Server 2014 compatible version
   
-- Version History 
-- V4.9 4 October, 2015 
   
-- License: Cellular Automation for SQL Server CPU, IO and Hekaton Benchmarking (CA) is free to download 
-- and use for personal, educational, and internal corporate purposes, provided that this header is
-- preserved. Redistribution or sale of CA, in whole or in part, is prohibited without the author's express
-- written consent.
   
-- Feedback: mailto:paulbrewer@yahoo.co.uk
-- Updates, instructions and disclaimers: https://paulbrewer.wordpress.com/2015/06/27/hekaton-the-good-the-bad-and-the-ugly/
-- This is a stress test benchmarking tool, use with care and at your own risk.
-- Can be run in multiple concurrent sessions.
-- Comment out the Hekaton call if stress testing SQL Server 2008 or 2012.
-- Assumes the CA_Setup_2014 script has been run - https://paulbrewer.wordpress.com/ca_stress_sql2014/
-- =============================================
  
     
------------------------------------------------------------------------------------------------
-- 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.CA_LoadTestExecute 
	 @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.CA_LoadTestExecute 
	 @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.CA_LoadTestExecute 
	 @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;
   
EXECUTE dbo.CA_LoadTestResults; 
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: