CA_Stress_SQL2014

Double click on the CA Setup script below then Copy, Paste into a SQL Server 2014 Query Analyser window and execute.

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.


-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Cellular Automation Database Setup
--   SQL Server 2014 Compatible Version
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.
-- =============================================
     
USE master;
GO
          
---------------------------------------------------------------------------------------------------------------
-- Database Setup  
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'CellularAutomation')
BEGIN
    ALTER DATABASE CellularAutomation SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CellularAutomation
END
GO
   
CREATE DATABASE CellularAutomation
GO
  
-- Room to grow
ALTER DATABASE CellularAutomation 
MODIFY FILE
    (NAME = CellularAutomation,
    SIZE = 500MB);
GO
   
ALTER DATABASE CellularAutomation 
MODIFY FILE
    (NAME = CellularAutomation_Log,
    SIZE = 500MB);
GO
  
ALTER DATABASE CellularAutomation SET RECOVERY SIMPLE;
     
ALTER DATABASE CellularAutomation
ADD FILEGROUP CellularAutomation_MOD CONTAINS MEMORY_OPTIMIZED_DATA
GO
  
DECLARE @nSQL NVARCHAR(2000), @DB_DataFolder NVARCHAR(100);
  
SELECT @DB_DataFolder = REVERSE(RIGHT(REVERSE(physical_name),(LEN(physical_name)-CHARINDEX('\', REVERSE(physical_name),1))+1))
FROM sys.master_files
WHERE [name] = 'CellularAutomation'
  
SELECT @nSQL = ' 
ALTER DATABASE CellularAutomation
ADD FILE(NAME = CellularAutomation_MOD, 
FILENAME = ' + '''' + @DB_DataFolder + 'CellularAutomation_XTP_MOD' + '''' + ') 
TO FILEGROUP CellularAutomation_MOD'
  
EXEC sp_executesql @nSQL;
  
---------------------------------------------------------------------------------------------------------------
-- In-memory and disk table setup
USE CellularAutomation
GO
EXEC sp_changedbowner 'sa'
GO 
            
CREATE TABLE dbo.GridReference_Hekaton
(
     
    z INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000) NOT NULL,
    Session_ID INT NOT NULL, 
    Pattern_ID INT NOT NULL, 
    x INT NOT NULL,
    y INT NOT NULL, 
    merkle_exists BIT NULL DEFAULT 0,
    neighbours INT NULL DEFAULT 0,
    INDEX IDX_GridReference_Hekaton NONCLUSTERED (Session_ID, Pattern_ID, x, y, z)
)
WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA)
GO
     
     
CREATE TABLE dbo.Merkle_Hekaton
(
    z INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000) NOT NULL,
    Session_ID INT NOT NULL,
    Pattern_ID INT NOT NULL, 
    x INT NOT NULL, 
    y INT NOT NULL
    INDEX IDX_Merkle_Hekaton NONCLUSTERED (Session_ID, Pattern_ID, x, y, z) 
) 
WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA)
GO     
      
CREATE TABLE dbo.Merkle
(
    Session_ID INT NOT NULL,
    Pattern_ID INT NOT NULL, 
    x INT NOT NULL, 
    y INT NOT NULL,
    CONSTRAINT PK_Merkle PRIMARY KEY CLUSTERED
    (Session_ID, Pattern_ID ASC, x ASC, y ASC) ON [PRIMARY]
) ON [PRIMARY]
GO
        
CREATE TABLE dbo.GridReference 
(
    Session_ID INT,
    z INT IDENTITY(1,1), 
    Pattern_ID INT NOT NULL, 
    x INT NOT NULL,
    y INT NOT NULL, 
    merkle_exists BIT NULL DEFAULT 0,
    neighbours INT NULL DEFAULT 0,
    CONSTRAINT PK_GridReference PRIMARY KEY CLUSTERED
    (Session_ID,x,y,z) ON [PRIMARY]
) ON [PRIMARY]
GO
  
CREATE TABLE dbo.TimingResults(
    z INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000) NOT NULL,
    Session_ID INT NOT NULL,
    StartTime DATETIME NOT NULL,
    EndTime DATETIME NOT NULL,
    NewPatterns INT NOT NULL,
    StressLevel INT,
    InitialMerkleCount INT NOT NULL,
    Generator CHAR(3) NOT NULL,
    GeneratorSucceeded BIT DEFAULT(1) NOT NULL,
    TestCase VARCHAR(50) DEFAULT ('N/A') NOT NULL ,
	Description1 VARCHAR(50) NULL,
	Description2 VARCHAR(50) NULL,
	Description3 VARCHAR(50) NULL,
    INDEX IDX_TimingResults NONCLUSTERED (EndTime, z) 
) 
WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA)
GO
 
 
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x new patterns
IF OBJECT_ID('CellularAutomation.dbo.CA_InitPatterns_Hekaton') IS NOT NULL
    DROP PROCEDURE dbo.CA_InitPatterns_Hekaton;
GO
     
CREATE PROCEDURE dbo.CA_InitPatterns_Hekaton  (@StressLevel INT = 1, @Session_ID INT)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT , LANGUAGE = 'us_english' )
 
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Initialise Starting Patterns
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
DELETE FROM dbo.Merkle_Hekaton WHERE Session_ID = @Session_ID;
DELETE FROM dbo.GridReference_Hekaton WHERE Session_ID = @Session_ID;
      
IF @StressLevel >= 1
BEGIN
      
    -- Blinker
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,6,8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,8);
  
END
      
IF @StressLevel >= 2
BEGIN
      
    -- Toad
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,6);
  
    -- Beacon
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,-6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,-6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,-7);
  
    ----Pulsar
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-3);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-5);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-3);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-5);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,4,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-12);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,4,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-8);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-17);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-17);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,-13);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,-7);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-9);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-9);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-8);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-7);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-9);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-9);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-8);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-11);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-11);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-12);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-11);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-11);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-12);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-13);
  
END;
      
IF @StressLevel = 3
BEGIN
    -- 2 Gosper Glider Guns
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,2,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,2,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,14,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,14,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,19,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,24,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,24,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,20);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,36,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,36,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,37,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,37,18);
      
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,52,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,52,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,53,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,53,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,64,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,65,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,63,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,63,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,64,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,65,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,66,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,67,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,67,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,69,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,74,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,74,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,20);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,86,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,86,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,87,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,87,18);
  
END;
      
END; -- Create Procedure    
GO
 
 
---------------------------------------------------------------------------------------------------------------
-- Initialize Patterns (start new test cycle) Procedure 
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_InitPatterns')
EXEC ('CREATE PROC dbo.CA_InitPatterns AS SELECT ''stub version, to be replaced''')
GO 
             
ALTER PROCEDURE dbo.CA_InitPatterns @StressLevel INT = 2
AS
BEGIN
 
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Initialise Starting Patterns
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
 
SET NOCOUNT ON;
DECLARE @Session_ID INT;
SELECT @Session_ID = @@SPID;
      
IF @StressLevel IS NULL
    SET @StressLevel = 2;
      
IF @StressLevel NOT IN (1,2,3)
BEGIN;
    RAISERROR('Input parameters can only be 1 (gentle), 2(moderate), 3 (severe)', 16, 1);
    RETURN;
END;
      
DELETE FROM dbo.Merkle_Hekaton WHERE Session_ID = @Session_ID;
DELETE FROM dbo.Merkle WHERE Session_ID = @Session_ID;
DELETE FROM dbo.GridReference_Hekaton WHERE Session_ID = @Session_ID;
DELETE FROM dbo.GridReference WHERE Session_ID = @Session_ID;
      
IF @StressLevel >= 1
BEGIN
      
    -- Blinker
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,6,8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,8);
  
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,8);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,6,8);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,8);    
END
      
IF @StressLevel >= 2
BEGIN
      
    -- Toad
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,6);
  
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,6);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,6);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,6);
                 
    -- Beacon
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,-6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,-6);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,-7);
  
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,-4);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,-4);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-9,-5);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-8,-5);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,-6);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,-6);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-7,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,-6,-7);
  
    ----Pulsar
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-3);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-5);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-3);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-4);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-5);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-5);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,4,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-12);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,4,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-8);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-17);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-17);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,-13);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,-7);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-9);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-9);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-8);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-8);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-7);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-7);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-9);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-9);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-8);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-11);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-11);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-12);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-11);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-11);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-12);
             
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-13);
  
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-3);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-4);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-5);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-5);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-3);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-4);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-5);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-5);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,4,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-12);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,4,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,5,-8);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-17);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-17);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-12);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,-13);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-8);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,-7);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-8);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-9);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-9);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-8);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-8);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-7);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-7);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-9);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-9);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-8);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-12);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,7,-11);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-11);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,8,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,9,-12);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-11);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-11);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,-12);
             
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-12);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,11,-13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,-13);
  
END;
      
IF @StressLevel = 3
BEGIN
    -- 2 Gosper Glider Guns
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,2,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,2,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,14,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,14,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,19,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,24,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,24,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,20);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,36,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,36,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,37,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,37,18);
      
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,52,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,52,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,53,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,53,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,64,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,65,12);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,63,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,63,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,64,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,65,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,66,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,67,13);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,67,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,69,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,16);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,74,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,74,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,14);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,15);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,19);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,20);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,86,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,86,18);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,87,17);
    INSERT INTO dbo.Merkle_Hekaton(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,87,18);
  
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,2,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,2,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,3,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,14,12);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,12);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,14);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,12,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,13,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,14,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,15,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,16,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,17,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,14);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,18,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,19,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,22,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,23,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,24,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,24,19);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,14);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,19);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,26,20);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,36,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,36,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,37,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,37,18);
      
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,52,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,52,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,53,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,53,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,64,12);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,65,12);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,63,13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,14);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,62,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,63,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,64,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,65,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,66,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,67,13);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,67,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,14);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,68,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,69,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,72,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,16);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,73,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,74,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,74,19);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,14);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,15);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,19);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,76,20);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,86,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,86,18);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,87,17);
    INSERT INTO dbo.Merkle(Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, 1,87,18);
  
END;
      
END; -- Create Procedure    
GO
  
---------------------------------------------------------------------------------------------------------------
-- Display Patterns Procedure 
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_DspPatterns_SQL')
EXEC ('CREATE PROC dbo.CA_DspPatterns_SQL AS SELECT ''stub version, to be replaced''')
GO 
          
ALTER PROCEDURE dbo.CA_DspPatterns_SQL @Pattern_ID INT = 0, @Session_ID INT = @@SPID
AS
BEGIN
 
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Displays Patterns in SQL Tables
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
  
    SET NOCOUNT ON;
    DECLARE @RowCount INT;
  
    IF ISNULL(@Pattern_ID,0) = 0
        SELECT @Pattern_ID = (SELECT MAX(Pattern_ID) FROM dbo.Merkle WHERE Session_ID = @Session_ID)
  
    IF ISNULL(@Session_ID,0) = 0
        SET @Session_ID = @@SPID
        
    SET @RowCount = (SELECT COUNT(*) FROM dbo.Merkle WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID)
    IF @RowCount = 0
    BEGIN
        RAISERROR('No patterns exist for this session, run the initialization procedure first.', 16, 1);
        RETURN
    END;
        
    DECLARE @x_upper INT, @x_lower INT, @y_upper INT, @y_lower INT;
        
    SELECT @x_upper = (SELECT MAX(x) FROM dbo.Merkle WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @x_upper = @x_upper + 1;
          
    SELECT @x_lower = (SELECT MIN(x) FROM dbo.Merkle WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @x_lower = @x_lower - 1;
          
    SELECT @y_upper = (SELECT MAX(y) FROM dbo.Merkle WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @y_upper = @y_upper + 1;
          
    SELECT @y_lower = (SELECT MIN(y) FROM dbo.Merkle WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @y_lower = @y_lower - 1;
        
    WITH x_axis (x_coordinate) AS
    (
        SELECT @x_lower AS x_coordinate
        UNION ALL
        SELECT x_coordinate + 1
        FROM x_axis
        WHERE x_coordinate <= @x_upper
    ),
    y_axis (y_coordinate) AS
    (
        SELECT @y_lower AS y_coordinate
        UNION ALL
        SELECT y_coordinate + 1
        FROM y_axis
        WHERE y_coordinate <= @y_upper
    ) ,
    grid_reference (x_coordinate, y_coordinate, grid_reference) AS
    (
        SELECT
            x.x_coordinate,
            y.y_coordinate,
            'POLYGON( (' +
            CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate) AS VARCHAR(7)) + ' ' + CAST((y_coordinate) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) +
        ') )' AS grid_reference
        FROM x_axis x
        CROSS JOIN y_axis y
    )
        
    SELECT m.x, m.y, CAST(g.grid_reference AS GEOMETRY)
    FROM dbo.Merkle m
    INNER JOIN grid_reference g  
        ON g.x_coordinate = m.x
        AND g.y_coordinate = m.y
    WHERE m.Pattern_ID = @Pattern_ID 
    AND Session_ID = @Session_ID OPTION ( MAXRECURSION 32767 );
          
        
END
GO
      
---------------------------------------------------------------------------------------------------------------
-- Display Hekaton Patterns Procedure 
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_DspPatterns_Hekaton')
EXEC ('CREATE PROC dbo.CA_DspPatterns_Hekaton AS SELECT ''stub version, to be replaced''')
GO 
             
ALTER PROCEDURE dbo.CA_DspPatterns_Hekaton @Pattern_ID INT = 0, @Session_ID INT = @@SPID
AS
BEGIN
 
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Displays patterns in Hekaton
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
  
    SET NOCOUNT ON;
    DECLARE @RowCount INT;
     
    IF ISNULL(@Pattern_ID,0) = 0
        SELECT @Pattern_ID = (SELECT MAX(Pattern_ID) FROM dbo.Merkle_Hekaton WHERE Session_ID = @Session_ID)
     
    IF ISNULL(@Session_ID,0) = 0
        SET @Session_ID = @@SPID
           
    SET @RowCount = (SELECT COUNT(*) FROM dbo.Merkle_Hekaton WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID)
    IF @RowCount = 0
    BEGIN
        PRINT 'No patterns for this session/Pattern_ID.'
        RETURN
    END;
           
    DECLARE @x_upper INT, @x_lower INT, @y_upper INT, @y_lower INT;
           
    SELECT @x_upper = (SELECT MAX(x) FROM dbo.Merkle_Hekaton WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @x_upper = @x_upper + 1;
             
    SELECT @x_lower = (SELECT MIN(x) FROM dbo.Merkle_Hekaton WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @x_lower = @x_lower - 1;
             
    SELECT @y_upper = (SELECT MAX(y) FROM dbo.Merkle_Hekaton WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @y_upper = @y_upper + 1;
             
    SELECT @y_lower = (SELECT MIN(y) FROM dbo.Merkle_Hekaton WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID);
    SET @y_lower = @y_lower - 1;
           
    WITH x_axis (x_coordinate) AS
    (
        SELECT @x_lower AS x_coordinate
        UNION ALL
        SELECT x_coordinate + 1
        FROM x_axis
        WHERE x_coordinate <= @x_upper
    ),
    y_axis (y_coordinate) AS
    (
        SELECT @y_lower AS y_coordinate
        UNION ALL
        SELECT y_coordinate + 1
        FROM y_axis
        WHERE y_coordinate <= @y_upper
    ) ,
    grid_reference (x_coordinate, y_coordinate, grid_reference) AS
    (
        SELECT
            x.x_coordinate,
            y.y_coordinate,
            'POLYGON( (' +
            CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate) AS VARCHAR(7)) + ' ' + CAST((y_coordinate) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate) AS VARCHAR(7)) + ','  +
            CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) +
        ') )' AS grid_reference
        FROM x_axis x
        CROSS JOIN y_axis y
    )
           
    SELECT m.x, m.y, CAST(g.grid_reference AS GEOMETRY)
    FROM dbo.Merkle_Hekaton m
    INNER JOIN grid_reference g  
        ON g.x_coordinate = m.x
        AND g.y_coordinate = m.y
    WHERE m.Pattern_ID = @Pattern_ID 
    AND Session_ID = @Session_ID OPTION ( MAXRECURSION 32767 );
             
           
END
GO     
     
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x new patterns
IF OBJECT_ID('CellularAutomation.dbo.CA_GenPatterns_Hekaton') IS NOT NULL
    DROP PROCEDURE dbo.CA_GenPatterns_Hekaton;
GO
     
CREATE PROCEDURE dbo.CA_GenPatterns_Hekaton  (@Iterations INT, @Session_ID INT)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT , LANGUAGE = 'us_english' )
     
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Generates new patterns using Hekaton in memory tables and procedures.
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
     
    DECLARE @Iteration INT, @LastIteration INT, @CurrentIteration INT, @NewIteration INT, @RowCount INT;
     
    -- Defaults
    IF ISNULL(@Iterations,0) = 0
        SET @Iterations = 1;
     
            -- Defaults
    IF ISNULL(@Session_ID,0) = 0
        SET @Session_ID = 1;
      
     -- Patterns Generated, Iterations
    SET @CurrentIteration = 1;
    WHILE @CurrentIteration <= @Iterations
    BEGIN
     
      
        SELECT @LastIteration  = MAX(Pattern_ID) FROM dbo.Merkle_Hekaton WHERE Session_ID = @Session_ID;
           
        SELECT  @RowCount = COUNT(*) FROM dbo.Merkle_Hekaton WHERE Session_ID = @Session_ID;
        IF @RowCount = 0
        BEGIN
            RETURN
        END;
          
        -- Create new working set of merkles, and the 8 cells adjacent to them
        SET @NewIteration = @LastIteration + 1;
   
        -- Housekeeping - Tidy the Grid Reference worktable
        DELETE FROM dbo.GridReference_Hekaton --Query Stats P-HEK Q-DEL1
        WHERE Session_ID = @Session_ID;
     
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y,merkle_exists, Pattern_ID)  --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x,y,1,@NewIteration FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- NE Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x-1,y+1,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID        
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- N Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x,y+1,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID        
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- NW Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID,x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x+1,y+1,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID        
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- W Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x+1,y,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID        
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- SW Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x+1,y-1,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- S Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x,y-1,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID        
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- SE Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x-1,y-1,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID        
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        -- E Neighbour
        INSERT INTO dbo.GridReference_Hekaton(Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-HEK Q-INS1
        SELECT @Session_ID, x-1,y,@NewIteration,1 FROM dbo.Merkle_Hekaton 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID        
        GROUP BY x,y
        HAVING COUNT(*) > 0;
      
        --------------------------------------------------------------------------------------------------
        -- Cells come alive or stay alive at next iteration rule
       ----------------------------------------------------------------------------------------------------
        -- Merkles stays alive or are born
        INSERT INTO dbo.Merkle_Hekaton (Session_ID, Pattern_ID,x,y) --Query Stats P-HEK Q-INS2
        SELECT @Session_ID, @NewIteration,gr1.x,gr1.y
        FROM dbo.GridReference_Hekaton gr1
        JOIN dbo.GridReference_Hekaton gr2
            ON gr1.x = gr2.x
            AND gr1.y = gr2.y
        WHERE gr1.merkle_exists = 1
        AND gr2.merkle_exists = 0
        AND gr1.Session_ID = @Session_ID
        AND gr2.Session_ID = @Session_ID
        AND gr1.Pattern_ID = @NewIteration
        AND gr2.Pattern_ID =  @NewIteration
        GROUP BY gr1.x, gr1.y
        HAVING COUNT(*) = 2; 
     
        INSERT INTO dbo.Merkle_Hekaton (Session_ID, Pattern_ID,x,y) --Query Stats P-HEK Q-INS3
        SELECT @Session_ID, @NewIteration,gr1.x,gr1.y
        FROM dbo.GridReference_Hekaton gr1
        JOIN dbo.GridReference_Hekaton gr2
            ON gr1.x = gr2.x
            AND gr1.y = gr2.y
        WHERE gr1.merkle_exists = 1
        AND gr2.merkle_exists = 0
        AND gr1.Session_ID = @Session_ID
        AND gr2.Session_ID = @Session_ID
        AND gr1.Pattern_ID = @NewIteration
        AND gr2.Pattern_ID =  @NewIteration
        GROUP BY gr1.x, gr1.y
        HAVING COUNT(*) = 3;
     
        DELETE FROM dbo.GridReference_Hekaton WHERE Session_ID = @Session_ID AND merkle_exists = 1;  --Query Stats P-HEK Q-DEL2
     
        INSERT INTO dbo.Merkle_Hekaton (Session_ID, Pattern_ID,x,y) --Query Stats P-HEK Q-INS4
        SELECT @Session_ID, @NewIteration,x,y
        FROM dbo.GridReference_Hekaton gr
        WHERE gr.Session_ID = @Session_ID
        AND gr.Pattern_ID = @NewIteration
        GROUP BY x,y 
        HAVING COUNT(*) = 3;
      
       --------------------------------------------------------------------------------------------------
        SET @CurrentIteration = @CurrentIteration + 1
        --------------------------------------------------------------------------------------------------
    END -- Iterations
     
END -- Procedure
GO
  
  
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x iteractions of new patterns using set based queries
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_GenPatterns_IO')
EXEC ('CREATE PROC dbo.CA_GenPatterns_IO AS SELECT ''stub version, to be replaced''')
GO 
          
ALTER PROCEDURE dbo.CA_GenPatterns_IO @NewPatterns INT = 1
AS
BEGIN
 
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Generates new patterns using set based queries
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
  
    SET NOCOUNT ON;
    DECLARE @Iteration INT, @LastIteration INT, @CurrentIteration INT, @NewIteration INT, @RowCount INT;
  
    DECLARE @Session_ID INT;
    SELECT @Session_ID = @@SPID;
        
    -- Defaults
    IF ISNULL(@NewPatterns,0) = 0
        SET @NewPatterns = 1;
   
     -- Patterns Generated, Iterations
    SET @CurrentIteration = 1;
    WHILE @CurrentIteration <= @NewPatterns
    BEGIN
   
        SELECT @LastIteration  = MAX(Pattern_ID) FROM dbo.Merkle WHERE Session_ID = @Session_ID;
        
        SELECT  @RowCount = COUNT(*) FROM dbo.Merkle WHERE Session_ID = @Session_ID;
        IF @RowCount = 0
        BEGIN
            RAISERROR('No patterns exist for this session, run the initialization procedure first.', 16, 1);
            RETURN
        END;
       
        --------------------------------------------------------------------------------------------------
        -- Create new working set of merkles, and the 8 cells adjacent to them
        SET @NewIteration = @LastIteration + 1;
   
         -- Housekeeping - Tidy the Grid Reference worktable
        DELETE FROM dbo.GridReference WHERE Session_ID = @Session_ID; --Query Stats P-IO Q-DEL1
     
        INSERT INTO dbo.GridReference (Session_ID, x,y,merkle_exists, Pattern_ID)
        SELECT @Session_ID, x,y,1,@NewIteration FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- NE Neighbour
        INSERT INTO dbo.GridReference (Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x-1,y+1,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- N Neighbour
        INSERT INTO dbo.GridReference (Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x,y+1,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- NW Neighbour
        INSERT INTO dbo.GridReference (Session_ID,x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x+1,y+1,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- W Neighbour
        INSERT INTO dbo.GridReference (Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x+1,y,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- SW Neighbour
        INSERT INTO dbo.GridReference (Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x+1,y-1,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- S Neighbour
        INSERT INTO dbo.GridReference (Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x,y-1,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- SE Neighbour
        INSERT INTO dbo.GridReference (Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x-1,y-1,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        -- E Neighbour
        INSERT INTO dbo.GridReference (Session_ID, x,y, Pattern_ID, neighbours) --Query Stats P-IO Q-INS1
        SELECT @Session_ID, x-1,y,@NewIteration,1 FROM dbo.Merkle 
        WHERE Pattern_ID = @LastIteration
        AND Session_ID = @Session_ID;
   
        --------------------------------------------------------------------------------------------------
        -- Empty cells come alive at next iteration rule
        INSERT INTO dbo.Merkle (Session_ID, Pattern_ID,x,y) --Query Stats P-IO Q-INS2
        SELECT @Session_ID, @NewIteration,x,y
        FROM dbo.GridReference gr
        WHERE merkle_exists = 0
        AND gr.Session_ID = @Session_ID
        AND gr.Pattern_ID = @NewIteration
        AND NOT EXISTS 
        (
            SELECT 1
            FROM dbo.GridReference 
            WHERE x = gr.x
            AND y = gr.y
            AND merkle_exists = 1
            AND Session_ID = @Session_ID
            AND Pattern_ID = @NewIteration
        )
        GROUP BY x,y 
        HAVING COUNT(*) = 3;
   
        --------------------------------------------------------------------------------------------------
        -- Merkle cells stays alive at next iteration rule
        INSERT INTO dbo.Merkle (Session_ID, Pattern_ID,x,y) --Query Stats P-IO Q-INS3
        SELECT @Session_ID, @NewIteration,gr.x,gr.y
        FROM dbo.GridReference gr
        INNER JOIN
        (
            SELECT x,y, COUNT(*) AS adjacentmerkles
            FROM dbo.GridReference 
            WHERE merkle_exists = 0
            AND Session_ID = @Session_ID
            AND Pattern_ID = @NewIteration
            GROUP BY x,y
        ) neighbours
            ON neighbours.x = gr.x
            AND neighbours.y = gr.y
        WHERE gr.merkle_exists = 1
        AND neighbours.adjacentmerkles IN (2,3)
        AND Session_ID = @Session_ID
        AND Pattern_ID = @NewIteration;
   
       --------------------------------------------------------------------------------------------------
        SET @CurrentIteration = @CurrentIteration + 1
        --------------------------------------------------------------------------------------------------
    END -- Iterations
END -- Procedure
GO
    
   
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x iteractions of new patterns using geometry
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_GenPatterns_CPU')
EXEC ('CREATE PROC dbo.CA_GenPatterns_CPU AS SELECT ''stub version, to be replaced''')
GO 
          
ALTER PROCEDURE dbo.CA_GenPatterns_CPU @NewPatterns INT = 1
AS
BEGIN
 
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Generates new patterns using geometry
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
  
    SET NOCOUNT ON;
    DECLARE @RowCount INT, @Iteration INT, @LastIteration INT, @CurrentIteration INT, @NewIteration INT;
    DECLARE @x_upper INT, @x_lower INT, @y_upper INT, @y_lower INT;
    DECLARE @x INT, @y INT, @NeighboursCount INT, @grid_reference GEOMETRY;
    DECLARE @Session_ID INT;
    SELECT @Session_ID = @@SPID;
         
    IF OBJECT_ID('tempdb..#MerkleGrid') IS NOT NULL
        DROP TABLE #MerkleGrid;
        
    -- Merkle Cells
    CREATE TABLE #MerkleGrid (x INT NOT NULL,y INT NOT NULL, grid_reference GEOMETRY NULL);
    ALTER TABLE #MerkleGrid ADD PRIMARY KEY CLUSTERED (x, y);
        
    -- Default to 1 iteration
    IF ISNULL(@NewPatterns,0) = 0
        SET @NewPatterns = 1;
        
    -- Patterns Generated Iterations Counter
    SET @CurrentIteration = 1;
    WHILE @CurrentIteration <= @NewPatterns
    BEGIN
        
        TRUNCATE TABLE #MerkleGrid;
        
        SET @RowCount = (SELECT COUNT(*) FROM dbo.Merkle WHERE Session_ID = @Session_ID)
        IF @RowCount = 0
        BEGIN
            RAISERROR('No patterns exist for this session, run the initialization procedure first.', 16, 1);
            RETURN
        END;
        
        SET @LastIteration = (SELECT MAX(Pattern_ID) FROM dbo.Merkle WHERE Session_ID = @Session_ID)
        
        SELECT @x_upper = (SELECT MAX(x) FROM dbo.Merkle WHERE Pattern_ID = @LastIteration AND Session_ID = @Session_ID);
        SET @x_upper = @x_upper + 1;
          
        SELECT @x_lower = (SELECT MIN(x) FROM dbo.Merkle WHERE Pattern_ID = @LastIteration AND Session_ID = @Session_ID);
        SET @x_lower = @x_lower - 1;
          
        SELECT @y_upper = (SELECT MAX(y) FROM dbo.Merkle WHERE Pattern_ID = @LastIteration AND Session_ID = @Session_ID);
        SET @y_upper = @y_upper + 1;
          
        SELECT @y_lower = (SELECT MIN(y) FROM dbo.Merkle WHERE Pattern_ID = @LastIteration AND Session_ID = @Session_ID);
        SET @y_lower = @y_lower - 1;
        
        --------------------------------------------------------------------------------------------------
        -- Merkle stays alive at next iteration rule (2 or 3 merkle neighbours)
        WITH x_axis (x_coordinate) AS --Query Stats P-CPU Q-INS1A
        (
            SELECT @x_lower AS x_coordinate
            UNION ALL
            SELECT x_coordinate + 1
            FROM x_axis
            WHERE x_coordinate <= @x_upper
        ) ,
        y_axis (y_coordinate) AS
        (
            SELECT @y_lower AS y_coordinate
            UNION ALL
            SELECT y_coordinate + 1
            FROM y_axis
            WHERE y_coordinate <= @y_upper
        ) ,
        grid_reference (x_coordinate, y_coordinate, grid_reference, merkle_exists) AS
        (
            SELECT
                x.x_coordinate,
                y.y_coordinate,
                'POLYGON( (' +
                    CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) + ','  +
                    CAST((x_coordinate) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) + ','  +
                    CAST((x_coordinate) AS VARCHAR(7)) + ' ' + CAST((y_coordinate) AS VARCHAR(7)) + ','  +
                    CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate) AS VARCHAR(7)) + ','  +
                    CAST((x_coordinate+1.2) AS VARCHAR(7)) + ' ' + CAST((y_coordinate+1.2) AS VARCHAR(7)) +
                ') )' AS grid_reference
                ,CASE ISNULL(m.Pattern_ID,0) WHEN 0 THEN 'N' ELSE 'Y' END AS merkle_exists
            FROM x_axis x
            CROSS JOIN y_axis y
            LEFT OUTER JOIN dbo.Merkle m
                ON m.Pattern_ID =  @LastIteration
                AND m.x = x.x_coordinate
                AND m.y = y.y_coordinate
                AND Session_ID = @Session_ID
        )
         
        INSERT INTO #MerkleGrid (x, y, grid_reference)  --Query Stats P-CPU Q-INS1B
        SELECT x_coordinate, y_coordinate, gr.grid_reference
        FROM grid_reference gr
        WHERE gr.merkle_exists = 'Y'
        OPTION ( MAXRECURSION 32767 );
   
        --------------------------------------------------------------------------------------------------
        -- 'Cell Dies' rules (process existing Merkles, see if they still live at the next iteration)
        DECLARE c1 CURSOR FOR
        SELECT (@LastIteration + 1) AS NewIteration, x,y, grid_reference
        FROM #MerkleGrid g1
          
        OPEN c1;
        FETCH NEXT FROM c1 INTO @NewIteration, @x, @y, @grid_reference
        WHILE @@FETCH_STATUS = 0
        BEGIN
        
        
            SELECT @NeighboursCount = COUNT(*)   --Query Stats P-CPU Q-INS2
            FROM #MerkleGrid g
            WHERE EXISTS 
            ( 
                SELECT 1 
                FROM dbo.Merkle 
                WHERE Pattern_ID = @LastIteration 
                AND x = g.x
                AND y = g.y
                AND Session_ID = @Session_ID
            )
            AND g.y IN (@y,@y+1, @y-1)
            AND g.x IN (@x,@x+1, @x-1)
            AND g.grid_reference.STIntersects(@grid_reference) = 1;
      
            SELECT @NeighboursCount = @NeighboursCount - 1 --Ignore intersection with self.
    
            IF @NeighboursCount = 2 OR @NeighboursCount = 3
                INSERT INTO dbo.Merkle (Session_ID, Pattern_ID, x, y) VALUES(@Session_ID, @NewIteration, @x, @y);
        
            FETCH NEXT FROM c1 INTO @NewIteration, @x, @y, @grid_reference
        END;
        CLOSE c1;
        DEALLOCATE c1;
        
        --------------------------------------------------------------------------------------------------
        -- Cell comes alive rules, a Merkle is born 
        ;WITH merkles (x,y) AS --Query Stats P-CPU Q-INS3
        (
            SELECT x,y
            FROM dbo.Merkle 
            WHERE Pattern_ID = @LastIteration
            AND Session_ID = @Session_ID
        ) ,
        all_framing_cells (x, y) AS
        (
            SELECT x,y
            FROM merkle
            UNION
            SELECT x-1, y+1 -- NE
            FROM merkles m
            UNION
            SELECT x, y+1 -- N
            FROM merkles m
            UNION
            SELECT x+1, y+1 -- NW
            FROM merkles m
            UNION
            SELECT x+1, y -- W
            FROM merkles m
            UNION
            SELECT x+1, y-1 -- SW
            FROM merkles m
            UNION
            SELECT x, y-1 -- S
            FROM merkles m
            UNION
            SELECT x-1, y-1 -- SE
            FROM merkles m
            UNION
            SELECT x-1, y -- E
            FROM merkles m
        ) ,
        dead_framing_cells (x, y, merkle_exists) AS
        (
            SELECT afc.x, afc.y, ISNULL(m.merkle_exists,'N') AS merkle_exists 
            FROM all_framing_cells afc
            LEFT OUTER JOIN
            (
                SELECT x,y, 'Y' as merkle_exists  
                FROM dbo.Merkle
                WHERE Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) m 
                ON m.x = afc.x
                AND m.y = afc.y
        ) ,
        CellChecker AS
        (
            SELECT
                g.x, g.y,
                SUM
                ( 
                    ISNULL(Merkle_Exists_NW,0) +
                    ISNULL(Merkle_Exists_N,0) +
                    ISNULL(Merkle_Exists_NE,0) +
                    ISNULL(Merkle_Exists_E,0) +
                    ISNULL(Merkle_Exists_SE,0) +
                    ISNULL(Merkle_Exists_S,0) +
                    ISNULL(Merkle_Exists_SW,0) +
                    ISNULL(Merkle_Exists_W,0)
                ) AS NeighboursCount
            FROM dead_framing_cells g
    
            -- top_left
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_NW
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) top_left
                ON top_left.x = g.x - 1
                AND top_left.y = g.y + 1
          
            -- above
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_N
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) top_over
                ON top_over.x = g.x
                AND top_over.y = g.y + 1
          
            -- top right
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_NE
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) top_right
                ON top_right.x = g.x + 1
                AND top_right.y = g.y + 1
          
            -- bottom right
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_SE
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) bottom_right
                ON bottom_right.x = g.x + 1
                AND bottom_right.y = g.y - 1
          
            -- bottom below
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_S
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) bottom_under
                ON bottom_under.x = g.x
                AND bottom_under.y = g.y - 1
          
            -- bottom left
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_SW
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) bottom_left
                ON bottom_left.x = g.x - 1
                AND bottom_left.y = g.y - 1
          
            -- middle_left
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_W
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) middle_left
                ON middle_left.x = g.x - 1
                AND middle_left.y = g.y
          
            -- bottom left
            LEFT OUTER JOIN
            (
                SELECT x,y, 1 AS Merkle_Exists_E
                FROM dbo.Merkle  m
                WHERE m.Pattern_ID = @LastIteration
                AND Session_ID = @Session_ID
            ) middle_right
                ON middle_right.x = g.x + 1
                AND middle_right.y = g.y
          
            WHERE 
            (top_left.x IS NOT NULL
            OR top_over.x IS NOT NULL
            OR top_right.x IS NOT NULL
            OR bottom_right.x IS NOT NULL
            OR bottom_under.x IS NOT NULL
            OR bottom_left.x IS NOT NULL
            OR middle_left.x IS NOT NULL
            OR middle_right.x IS NOT NULL
            )
            GROUP BY g.x,g.y
        )           
   
        INSERT INTO dbo.Merkle (Session_ID, Pattern_ID, x, y) 
        SELECT @Session_ID, @NewIteration, gr.x, gr.y
        FROM dead_framing_cells gr
        JOIN
        (
            SELECT x, y, NeighboursCount
            FROM CellChecker cc
        ) neighbours
            ON neighbours.x = gr.x
            AND neighbours.y = gr.y     
   
        WHERE gr.merkle_exists = 'N'
        AND neighbours.NeighboursCount = 3 OPTION ( MAXRECURSION 32767 );
        
        --------------------------------------------------------------------------------------------------
        SET @CurrentIteration = @CurrentIteration + 1
        --------------------------------------------------------------------------------------------------
    END -- Iterations
END -- Procedure
GO
 
---------------------------------------------------------------------------------------------------------------
-- Hekaton in-memory procedure to save timing results 
IF OBJECT_ID('CellularAutomation.dbo.CA_TimingResultsSave') IS NOT NULL
    DROP PROCEDURE dbo.CA_TimingResultsSave;
GO
     
CREATE PROCEDURE dbo.CA_TimingResultsSave  
(	
	@Session_ID INT, @StartTime DATETIME, @EndTime DATETIME, @NewPatterns INT, @StressLevel INT, @InitialMerkleCount INT, @Generator CHAR(3), 
	@GeneratorSucceeded BIT, @TestCase VARCHAR(50), @Description1 VARCHAR(50) = NULL, @Description2 VARCHAR(50) = NULL, @Description3 VARCHAR(50) = NULL
)
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT , LANGUAGE = 'us_english' )
 
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Initialise Starting Patterns
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
 
INSERT INTO dbo.TimingResults (Session_ID, StartTime, EndTime, NewPatterns, StressLevel, InitialMerkleCount, Generator, GeneratorSucceeded, TestCase, Description1, Description2, Description3)
SELECT @Session_ID, @StartTime, @EndTime, @NewPatterns, @StressLevel, @InitialMerkleCount, @Generator,@GeneratorSucceeded, @TestCase, @Description1, @Description2, @Description3; 
 
END -- End Procedure
GO
 
   
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x iteractions of new patterns, calls either IO or CPU suffixed procedures
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_GenPatterns')
EXEC ('CREATE PROC dbo.CA_GenPatterns AS SELECT ''stub version, to be replaced''')
GO 
          
ALTER PROCEDURE dbo.CA_GenPatterns 
	@NewPatterns INT = 1, @Generator VARCHAR(3) = 'IO', @TestCase VARCHAR(50) = 'Test 1', @StressLevel INT = 0,
	@Description1 VARCHAR(50) = NULL, @Description2 VARCHAR(50) = NULL, @Description3 VARCHAR(50) = NULL
AS
BEGIN
   
-- =============================================
--   Author: Paul Brewer
--   Create date: 23nd June, 2015
--   Description: Driver Procedure for pattern generators
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
 
SET NOCOUNT ON;
DECLARE @StartTime DATETIME;
DECLARE @EndTime DATETIME;
 
DECLARE @InitialMerkleCount INT;

SELECT @InitialMerkleCount = COUNT(*)
FROM dbo.Merkle
WHERE Session_ID = @@SPID
AND Pattern_ID = (SELECT MAX(PATTERN_ID) FROM dbo.Merkle WHERE Session_ID = @@SPID);
 
     
-- Defaults 
IF @Generator IS NULL
    SET @Generator = 'IO';
   
IF @NewPatterns IS NULL
    SET @NewPatterns = 1
  
-- Validation 
IF @Generator NOT IN ('IO','CPU', 'HEK')
BEGIN;
    RAISERROR('Available pattern generators are IO, HEK and CPU', 16, 1);
    RETURN;
END;
   
 
SET @StartTime = GETDATE()
  
-- Generate patterns
BEGIN TRY
    IF @Generator = 'IO'
        EXEC dbo.CA_GenPatterns_IO @NewPatterns;
  
    IF @Generator = 'CPU'
        EXEC dbo.CA_GenPatterns_CPU @NewPatterns;
  
    IF @Generator = 'HEK'
        EXEC dbo.CA_GenPatterns_Hekaton  @Iterations = @NewPatterns, @Session_ID = @@SPID; 
 
    SET @EndTime = GETDATE(); 
    EXEC dbo.CA_TimingResultsSave @@SPID, @StartTime, @EndTime, @NewPatterns, @StressLevel, @InitialMerkleCount, @Generator,1, @TestCase, @Description1, @Description2, @Description3; 
  
END TRY
BEGIN CATCH
  
    SET @EndTime = GETDATE(); 
    EXEC dbo.CA_TimingResultsSave @@SPID, @StartTime, @EndTime, @NewPatterns, @StressLevel, @InitialMerkleCount, @Generator,0, @TestCase, @Description1, @Description2, @Description3; 
  
END CATCH
  
END;
GO
 
 
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x iteractions of new patterns, calls either IO or CPU suffixed procedures
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_LoadTestExecute')
EXEC ('CREATE PROC dbo.CA_LoadTestExecute AS SELECT ''stub version, to be replaced''')
GO 
          
ALTER PROCEDURE dbo.CA_LoadTestExecute 
	@Batches_IO INT = 1, @Batches_CPU INT = 1, @Batches_Hekaton INT = 1, @NewPatterns INT = 1, @TestCase VARCHAR(50) = 'Benchmark Test 1', 
	@StressLevel INT = 2, @Description1 VARCHAR(50) = NULL, @Description2 VARCHAR(50) = NULL, @Description3 VARCHAR(50) = NULL
AS
BEGIN
   
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Driver procedure to stress testing
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
 
SET NOCOUNT ON;
DECLARE @HighestBatch INT = 0;
DECLARE @CurrentBatch INT = 0;
DECLARE @Generator CHAR(3);
DECLARE @Batches_IO_Complete INT = 0;
DECLARE @Batches_CPU_Complete INT = 0;
DECLARE @Batches_Hekaton_Complete INT = 0;
 
-- Validation
IF @Batches_IO = 0 AND @Batches_CPU = 0 AND @Batches_Hekaton = 0
BEGIN
    RAISERROR('No Pattern Generator requested, nothing to do.', 16, 1);
    RETURN;
END
 
IF @StressLevel NOT IN (1,2,3) 
BEGIN
    RAISERROR('Valid stress Levels are 1 (gentle) 2 (mederate) and 3 (severe).', 16, 1);
    RETURN;
END
 
IF @NewPatterns < 1  
BEGIN
    RAISERROR('Please enter a valid valid for - New Patterns Per Generator call - ', 16, 1);
    RETURN;
END
 
-- Iterate for most requested batches
;WITH All_Requests AS
(
    SELECT @Batches_CPU AS Batch
    UNION 
    SELECT @Batches_IO AS Batch
    UNION 
    SELECT @Batches_Hekaton AS Batch
),
Highest_Request AS
( 
    SELECT MAX(Batch) Batch
    FROM All_Requests
)
 
SELECT @HighestBatch = Batch
FROM Highest_Request
   
WHILE @CurrentBatch < @HighestBatch
BEGIN
 
    -- Benchmark CPU
    IF @Batches_CPU_Complete < @Batches_CPU
    BEGIN 
        SET @Generator = 'CPU';
        EXECUTE dbo.CA_InitPatterns @StressLevel;
        EXECUTE dbo.CA_GenPatterns @NewPatterns, @Generator, @TestCase, @StressLevel, @Description1, @Description2, @Description3;
        SET @Batches_CPU_Complete = @Batches_CPU_Complete + 1;
    END
   
    -- Benchmark IO
    IF @Batches_IO_Complete < @Batches_IO
    BEGIN 
        SET @Generator = 'IO';
        EXECUTE dbo.CA_InitPatterns @StressLevel;
        EXECUTE dbo.CA_GenPatterns @NewPatterns, @Generator, @TestCase, @StressLevel, @Description1, @Description2, @Description3;
        SET @Batches_IO_Complete = @Batches_IO_Complete + 1;
    END
 
    -- Benchmark Hekaton
    IF @Batches_Hekaton_Complete < @Batches_Hekaton
    BEGIN 
        SET @Generator = 'HEK';
        EXECUTE dbo.CA_InitPatterns_Hekaton @StressLevel,@@SPID;
        EXECUTE dbo.CA_GenPatterns @NewPatterns, @Generator, @TestCase, @StressLevel, @Description1, @Description2, @Description3;
        SET @Batches_Hekaton_Complete = @Batches_Hekaton_Complete + 1;
    END
   
    SET @CurrentBatch = @CurrentBatch + 1
 
END
   
END --Procedure
GO
 
 
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x iteractions of new patterns, calls either IO or CPU suffixed procedures
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_LoadTestResults')
EXEC ('CREATE PROC dbo.CA_LoadTestResults AS SELECT ''stub version, to be replaced''')
GO 
          
ALTER PROCEDURE dbo.CA_LoadTestResults
AS
BEGIN
   
-- =============================================
--   Author: Paul Brewer
--   Create date: 22nd June, 2015
--   Description: Display Load Test Results and Timings
     
-- Version History 
-- V1.0 7th June, 2015 - Initial version
-- V3.0 9th June, 2015 SQLServerCentral Publish
-- V4.0 19th June, 2015 - Revisions - 2014 only version using in-memory and disk tables/procedures + query search strings for tuning
-- V4.1 22nd June, 2015 - Revisions - Add CA_LoadTestExecute and CA_LoadTestResults stored procedures
-- V4.3 26th June, 2016 - Revisions - Improve test results query
-- V4.4 30th June, 2016 - Revisions - Introduce a Hekaton specific Pattern Initialization procedure 
-- V4.5 1st July , 2016 - Revisions - Convert Timings Results table to in-memory and create a Hekaton 'save results' procedure 
-- V4.6 3rd July, 2015 - Revisions - Add 10 second delta for x-axis pivot
-- V4.7 17th July, 2015 - Revisions - Results recording StressLevel incorrectly
-- V4.8 19/07/2015 - Revisions - Include @@SERVERNAME in results for quicker excel analysis
-- V4.9 04/10/2015 - Revisions - Add optional 'Test Description1,2,3' columns to results table, for easier PIVOT
      
-- 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/05/03/sql-server-cellular-automation-using-geometry/
-- This is a stress test benchmarking tool, use with care and at your own risk.

-- =============================================
 
-----------------------------------------------------------------------
-- Individual, actual, results

SELECT 
	@@SERVERNAME AS ServerName, SQ1.Session_ID, SQ1.z, SQ1.NewPatterns, SQ1.Generator, SQ1.StressLevel, SQ1.TestCase,  -- Filters
	DATEDIFF(MICROSECOND, SQ1.StartTime, SQ1.EndTime) AS DurationMicroSec, -- Values (Sum) Lower is faster execution time
	SQ1.StartTime, EndTime, -- Informational
	CONVERT(VARCHAR(16),EndTime,120) AS EndTimeMin, CAST(CAST(EndTime AS TIME) AS VARCHAR(4)) + '0' EndTime10MinDelta, -- x Axis Pivot,
	Description1, Description2, Description3
FROM dbo.TimingResults SQ1
ORDER BY StartTime, EndTime;

 
 
END

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: