CA_SQLHekaton

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

Change to SQLCMD mode and set the Database Name, Data and Log file drive variables.

See here for a technical summary of the database – https://paulbrewer.wordpress.com/2017/07/30/hekaton-the-good-the-bad-and-the-ugly/

Copy and 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.


/*********************************************************************************************
Cellular Automation - SQL 2014 and Azure compatible Hekaton workload simulation v3.3 (2017-10-02)
(C) 2017, Paul Brewer
                       
Feedback: paulbrewer@yahoo.co.uk
Updates:  https://paulbrewer.wordpress.com/sql_hekaton_heartbeat/
Description - https://paulbrewer.wordpress.com/2015/07/19/sql-server-performance-synthetic-transaction-baseline/

2017-09-01  - V1    - Cellular Automation procedures written on holiday in France
2017-09-09  - V1.1  - Add error handling and an explicit transaction boundary around all updates               
2017-09-28  - V2.1  - Rename the results table, simplify the benchmarking procedure
2017-09-28  - V2.2  - Table changes and set read committed snapshot to avoid deadlocks during concurrent benchmarks  
2017-10-01  - v3.1  - Adjust clustered indexes to increase cardinality and better support access patterns
2017-10-01  - v3.2  - Missing index on grid reference
2017-10-02  - V3.3  - Schema check before create procedure

-------------------------------------------------------------------------------------------------------------*/

/*=============================================================================================================
ON PREMISE CREATE DATABASE
=============================================================================================================*/

SET NOEXEC OFF
USE master;
GO
---------------------------------------------------------------------------------------------------------------
-- Execution SQLCMD mode error
:SETVAR IsSqlCmdEnabled "True"
GO
IF ISNULL(N'$(IsSqlCmdEnabled)','false') NOT LIKE N'True'
	RAISERROR('SQLCMD mode is required to successfully execute this script, please cancel and switch modes',1,16)
GO

---------------------------------------------------------------------------------------------------------------
-- Initialize the new database, set these variables
:SETVAR DatabaseName "CA_SQLHekaton"
:SETVAR DataDrive "C:\MSSQL\"
:SETVAR LogDrive "C:\MSSQL\" 
            
---------------------------------------------------------------------------------------------------------------
-- Database Setup, drop if exists and create with memory optimized filegroup
IF DATABASEPROPERTYEX('$(databasename)','status') IS NOT NULL
BEGIN
    ALTER DATABASE $(DatabaseName) SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE $(DatabaseName)
END
  
     
CREATE DATABASE $(DatabaseName)
ON
( 
    NAME = $(DatabaseName)_data,
    FILENAME = N'$(DataDrive)$(DatabaseName)_data.mdf',
    SIZE = 10,
    MAXSIZE = 500,
    FILEGROWTH = 5 )
    LOG ON
    ( NAME = $(DatabaseName)_log,
    FILENAME = N'$(LogDrive)$(DatabaseName).ldf',
    SIZE = 5MB,
    MAXSIZE = 5000MB,
    FILEGROWTH = 5MB 
);     
  
  
ALTER DATABASE $(DatabaseName)
ADD FILEGROUP $(DatabaseName)_MOD CONTAINS MEMORY_OPTIMIZED_DATA
  
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] = '$(DatabaseName)_data'
  
SELECT @nSQL = '
ALTER DATABASE $(DatabaseName)
ADD FILE(NAME = $(DatabaseName)_MOD, 
FILENAME = ' + '''' + @DB_DataFolder + '$(DatabaseName)_XTP_MOD' + '''' + ') 
TO FILEGROUP $(DatabaseName)_MOD'
    
EXEC sp_executesql @nSQL;
 
ALTER DATABASE $(DatabaseName) SET RECOVERY SIMPLE;    
GO
  
---------------------------------------------------------------------------------------------------------------
-- Change recovery mode, read committed snapshot and owner 
USE $(DatabaseName)
GO
ALTER DATABASE $(DatabaseName) SET RECOVERY SIMPLE;    
GO
ALTER DATABASE $(DatabaseName) SET READ_COMMITTED_SNAPSHOT ON 
GO
EXEC sp_changedbowner 'sa'
GO 


/*=============================================================================================================
ON PREMISE AND SQL AZURE - CREATE DATABASE APPLICATION
=============================================================================================================*/
 
--------------------------------------------------------------------------------------------------------------
-- http://www.sommarskog.se/error_handling/Part1.html 
CREATE PROCEDURE error_handler_sp AS
  
DECLARE @errmsg   nvarchar(2048),
        @severity tinyint,
        @state    tinyint,
        @errno    int,
        @proc     sysname,
        @lineno   int
            
SELECT @errmsg = error_message(), @severity = error_severity(),
        @state  = error_state(), @errno = error_number(),
        @proc   = error_procedure(), @lineno = error_line()
        
IF @errmsg NOT LIKE '***%'
BEGIN
    SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                    ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                    ltrim(str(@errno)) + ': ' + @errmsg
END
RAISERROR('%s', @severity, @state, @errmsg);
 
GO
          
---------------------------------------------------------------------------------------------------------------
-- Tables
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 NOT NULL DEFAULT 0,
    neighbours INT NULL DEFAULT 0,
    INDEX IDX_GridReference_Hekaton1 NONCLUSTERED (Session_ID, Pattern_ID, x, y, z),
    INDEX IDX_GridReference_Hekaton2 NONCLUSTERED (Session_ID, Pattern_ID, Merkle_Exists,x, y) 

)
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
(
    z INT IDENTITY(1,1),
    Session_ID INT NOT NULL,
    Pattern_ID INT NOT NULL, 
    x INT NOT NULL, 
    y INT NOT NULL,
    CONSTRAINT PK_Merkle PRIMARY KEY NONCLUSTERED
    (z) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX CIX_Merkle_Session_ID ON dbo.Merkle(Session_ID, Pattern_ID, x, y, z); 
GO
          
CREATE TABLE dbo.GridReference 
(
    z INT IDENTITY(1,1), 
    Session_ID INT,
    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 NONCLUSTERED
    (z) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX CIX_GridReference_Session_ID ON dbo.GridReference(Session_ID, Pattern_ID, x, y, z); 
GO

CREATE NONCLUSTERED INDEX CIX_GridReference_MerkelExists ON dbo.GridReference(Session_ID, Pattern_ID, merkle_exists) INCLUDE(x,y); 
GO
    
CREATE TABLE dbo.CA_BenchmarkResults(
    z INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000) NOT NULL,
    Session_ID INT NOT NULL,
    BenchmarkStartTime DATETIME NOT NULL,
    BatchStartTime DATETIME NOT NULL,
	BatchDurationMS BIGINT, 
    NewPatternsInBatch INT NOT NULL,
    StressLevel TINYINT NOT NULL,
    BenchmarkPerspective CHAR(3) NOT NULL,
    GeneratorSucceeded BIT DEFAULT(1) NOT NULL,
    ServerName VARCHAR(250) NULL ,
    Description1 VARCHAR(50) NULL,
    Description2 VARCHAR(50) NULL,
    Description3 VARCHAR(50) NULL,
    INDEX IDX_TimingResults NONCLUSTERED (Session_ID) 
) 
WITH (MEMORY_OPTIMIZED = ON,  DURABILITY = SCHEMA_AND_DATA)
GO
   
---------------------------------------------------------------------------------------------------------------
-- Procedure initial pattern setup
IF OBJECT_ID('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' )
    BEGIN TRY
 
        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 TRY
    BEGIN CATCH 
        IF error_number() <> 2627 
        BEGIN
        ;THROW
        END
    END CATCH          
END; -- Create Procedure    
GO
   
---------------------------------------------------------------------------------------------------------------
-- Initial Pattern setup 
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
   
    SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY
        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;
        
        BEGIN TRANSACTION;
 
            DELETE FROM dbo.Merkle WHERE Session_ID = @Session_ID;
            DELETE FROM dbo.GridReference WHERE Session_ID = @Session_ID;
        
            IF @StressLevel >= 1
            BEGIN
        
                -- Blinker
                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(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(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(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(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;
     
        COMMIT;
    END TRY
 
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC error_handler_sp
        RETURN 55555
    END CATCH   
            
END; -- Create Procedure    
GO
    
 
---------------------------------------------------------------------------------------------------------------
-- Display Patterns 
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

	SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY
   
        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
          
        IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.Merkle WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID)
        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 TRY
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC error_handler_sp
        RETURN 55555
    END CATCH                 
          
END
GO
        
---------------------------------------------------------------------------------------------------------------
-- Display Hekaton Patterns 
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

	SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY
       
        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
             
        IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.Merkle_Hekaton WHERE Pattern_ID = @Pattern_ID AND Session_ID = @Session_ID)
        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 TRY
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC error_handler_sp
        RETURN 55555
    END CATCH                 
             
END
GO     
       
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x enumerations, test cycle factors
IF OBJECT_ID('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' )
 
    BEGIN TRY
 
        DECLARE @Iteration INT, @LastIteration INT, @CurrentIteration INT, @NewIteration INT, @RowCount INT, @z 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 TOP 1 @z = z FROM dbo.Merkle_Hekaton WHERE Session_ID = @Session_ID;
            IF @z IS NULL
            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 TRY
    BEGIN CATCH 
        IF error_number() <> 2627 
        BEGIN
        ;THROW
        END
    END CATCH         
END -- Procedure
GO
    
    
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x enumerations, test cycle factors
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
    SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY
       
        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
     
            BEGIN TRANSACTION;
 
                SELECT @LastIteration  = MAX(Pattern_ID) FROM dbo.Merkle WHERE Session_ID = @Session_ID;
          
                -- Bad query found using Query Store
                --SELECT  @RowCount = COUNT(*) FROM dbo.Merkle WHERE Session_ID = @Session_ID;
                --IF @RowCount = 0
                IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.Merkle WHERE Session_ID = @Session_ID)
                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
                --------------------------------------------------------------------------------------------------
            COMMIT
        END -- Iterations
    END TRY
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC error_handler_sp
        RETURN 55555
    END CATCH   
END -- Procedure
GO
      
     
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x enumerations, test cycle factors
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
    SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY;
 
        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;
          
            -- Bad query found using Query Store
            --SET @RowCount = (SELECT COUNT(*) FROM dbo.Merkle WHERE Session_ID = @Session_ID)
            --IF @RowCount = 0
            IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.Merkle WHERE Session_ID = @Session_ID)
            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;
          
 
            BEGIN TRANSACTION;
 
                --------------------------------------------------------------------------------------------------
                -- 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.grid_reference.STIntersects(@grid_reference) = 1
  
                    -- Comment out to perform unnecessary CLR calls, to increase CPU load.
                    --AND g.y IN (@y,@y+1, @y-1)
                    --AND g.x IN (@x,@x+1, @x-1);
                    -- Comment out to perform unnecessary CLR calls, to increase CPU load.
        
                    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 );
 
            COMMIT         
            --------------------------------------------------------------------------------------------------
            SET @CurrentIteration = @CurrentIteration + 1
            --------------------------------------------------------------------------------------------------
        END -- Iterations
    END TRY
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC error_handler_sp
        RETURN 55555
    END CATCH   
END -- Procedure
GO
   
---------------------------------------------------------------------------------------------------------------
-- Hekaton in-memory procedure to save timing results 
IF OBJECT_ID('dbo.CA_TimingResultsSave') IS NOT NULL
    DROP PROCEDURE dbo.CA_BenchmarkResults;
GO
       
CREATE PROCEDURE dbo.CA_TimingResultsSave  
(   
    @Session_ID INT, @BenchmarkStartTime DATETIME, @BatchStartTime DATETIME, @BatchDurationMS BIGINT, @NewPatternsInBatch INT, @StressLevel INT, @BenchmarkPerspective CHAR(3), 
    @GeneratorSucceeded BIT, @ServerName 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' )
 
BEGIN TRY  
    INSERT INTO dbo.CA_BenchmarkResults (Session_ID, BenchmarkStartTime, BatchStartTime, BatchDurationMS, NewPatternsInBatch, StressLevel, BenchmarkPerspective, GeneratorSucceeded, ServerName, Description1, Description2, Description3)
    SELECT @Session_ID, @BenchmarkStartTime, @BatchStartTime, @BatchDurationMS, @NewPatternsInBatch, @StressLevel, @BenchmarkPerspective, @GeneratorSucceeded, @ServerName, @Description1, @Description2, @Description3; 
END TRY
BEGIN CATCH 
    IF error_number() <> 2627 
    BEGIN
    ;THROW
    END
END CATCH  
END -- End Procedure
GO
   
     
---------------------------------------------------------------------------------------------------------------
-- Procedure to generate x enumerations, factors, calls to hek, IO or CPU benchmarks
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 
    @BenchmarkStartTime DATETIME, @NewPatternsInBatch INT, @BenchmarkPerspective VARCHAR(3), @StressLevel TINYINT,
    @Description1 VARCHAR(50) = NULL, @Description2 VARCHAR(50) = NULL, @Description3 VARCHAR(50) = NULL
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY    

        DECLARE @BatchStartTime DATETIME = GETDATE();
		DECLARE @BatchDurationMS BIGINT;
     
        -- Generate patterns
        IF @BenchmarkPerspective = 'IO'
            EXEC dbo.CA_GenPatterns_IO @NewPatternsInBatch;
    
        IF @BenchmarkPerspective = 'CPU'
            EXEC dbo.CA_GenPatterns_CPU @NewPatternsInBatch;
    
        IF @BenchmarkPerspective = 'HEK'
            EXEC dbo.CA_GenPatterns_Hekaton  @Iterations = @NewPatternsInBatch, @Session_ID = @@SPID; 
   

        SET @BatchDurationMS = DATEDIFF(millisecond,@BatchStartTime, GETDATE()); 

        EXEC dbo.CA_TimingResultsSave @Session_ID = @@SPID, @BenchmarkStartTime = @BenchmarkStartTime, @BatchStartTime = @BatchStartTime, 
            @BatchDurationMS = @BatchDurationMS, @NewPatternsInBatch = @NewPatternsInBatch, @StressLevel = @StressLevel, 
            @BenchmarkPerspective = @BenchmarkPerspective, @GeneratorSucceeded = 1, @ServerName = @@SERVERNAME, 
            @Description1 = @Description1, @Description2 = @Description2, @Description3 = @Description3; 
    
    END TRY
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC error_handler_sp
        RETURN 55555
    END CATCH     
END;
GO
   
   
---------------------------------------------------------------------------------------------------------------
-- Benchmarking procedure
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'CA_Benchmark')
EXEC ('CREATE PROC dbo.CA_Benchmark AS SELECT ''stub version, to be replaced''')
GO 
            
ALTER PROCEDURE dbo.CA_Benchmark
    @Batches INT = 0, @CPU_Benchmark BIT = 0, @IO_Benchmark BIT = 0, @Hek_Benchmark BIT = 0,
	@NewPatternsInBatch INT = 0, @DisplayPatterns BIT = 0, @Initialize BIT = 1, @StressLevel TINYINT = 3, 
	@Description1 VARCHAR(50) = NULL, @Description2 VARCHAR(50) = NULL, @Description3 VARCHAR(50) = NULL
AS
BEGIN
     
    SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY
        DECLARE @CurrentBatch INT = 0;
		DECLARE @SessionID INT = @@SPID;
		DECLARE @BenchmarkStartTime DATETIME = GETDATE();
		DECLARE @DefaultHeartBeat BIT = 0;

        -- Default to all
        IF @CPU_Benchmark = 0 AND @IO_Benchmark = 0 AND @Hek_Benchmark = 0
        BEGIN
		    SET @CPU_Benchmark = 1;
			SET @IO_Benchmark = 1;
			SET @Hek_Benchmark = 1;
			SET @Batches = 1;
			SET @DefaultHeartBeat = 1;
        END
   
        -- Validation
        IF @StressLevel NOT IN (1,2,3) 
        BEGIN
            RAISERROR('Valid stress Levels are 1 (gentle) 2 (mederate) and 3 (severe).', 16, 1);
            RETURN;
        END

        -- Initialise for display when batches = 0
        EXECUTE dbo.CA_InitPatterns @StressLevel = @StressLevel;
        EXECUTE dbo.CA_InitPatterns_Hekaton @StressLevel, @SessionID;
    
        WHILE @CurrentBatch < @Batches
        BEGIN
   
            -- Benchmark CPU
            IF @CPU_BenchMark = 1
            BEGIN 
                IF @Initialize = 1
                    EXECUTE dbo.CA_InitPatterns @StressLevel = @StressLevel;

                IF @DefaultHeartBeat = 1
                BEGIN
                    SET @StressLevel = 3
                    SET @NewPatternsInBatch = 1
                END

					  
                EXECUTE dbo.CA_GenPatterns 
				    @BenchmarkStartTime = @BenchmarkStartTime, @NewPatternsInBatch = @NewPatternsInBatch, @BenchmarkPerspective = 'CPU', @StressLevel = @StressLevel,
				    @Description1 = @Description1, @Description2 = @Description2, @Description3 = @Description3
            END

            -- Benchmark IO
            IF @IO_BenchMark = 1
            BEGIN 
                IF @Initialize = 1
                    EXECUTE dbo.CA_InitPatterns @StressLevel = @StressLevel;
  
                IF @DefaultHeartBeat = 1
                BEGIN
                    SET @StressLevel = 3
                    SET @NewPatternsInBatch = 25
                END

                EXECUTE dbo.CA_GenPatterns 
				    @BenchmarkStartTime = @BenchmarkStartTime, @NewPatternsInBatch = @NewPatternsInBatch, @BenchmarkPerspective = 'IO', @StressLevel = @StressLevel,
				    @Description1 = @Description1, @Description2 = @Description2, @Description3 = @Description3
            END

            -- Benchmark Hekaton
            IF @HEK_BenchMark = 1
            BEGIN 
                IF @Initialize = 1
                    EXECUTE dbo.CA_InitPatterns_Hekaton @StressLevel, @SessionID;
  
				IF @DefaultHeartBeat = 1
                BEGIN
                    SET @StressLevel = 3
					SET @NewPatternsInBatch = 100
                END

                EXECUTE dbo.CA_GenPatterns 
				    @BenchmarkStartTime = @BenchmarkStartTime, @NewPatternsInBatch = @NewPatternsInBatch, @BenchmarkPerspective = 'HEK', @StressLevel = @StressLevel,
				    @Description1 = @Description1, @Description2 = @Description2, @Description3 = @Description3
            END
			     
            SET @CurrentBatch = @CurrentBatch + 1
   
        END

        IF @DisplayPatterns = 1 
        BEGIN

            IF @Hek_Benchmark <> 0
			    EXECUTE dbo.CA_DspPatterns_Hekaton 

           IF  @Hek_Benchmark = 0 
               EXECUTE dbo.CA_DspPatterns_SQL 

           SELECT 
              BenchmarkStartTime		  
             ,BatchStartTime
             ,BatchDurationMS
             ,NewPatternsInBatch
             ,StressLevel
             ,BenchmarkPerspective
             ,Description1
             ,Description2
             ,Description3
           FROM dbo.CA_BenchmarkResults
           WHERE BenchmarkStartTime = @BenchmarkStartTime 

		END

    END TRY
    BEGIN CATCH
        IF @@trancount > 0 ROLLBACK TRANSACTION
        EXEC error_handler_sp
        RETURN 55555
    END CATCH  
     
END --Procedure
GO

%d bloggers like this: