Cellular Automation (CA) in SQL Server – Application Description

The following link explains the logical rules associated with this application  http://en.wikipedia.org/wiki/Conway’s_Game_of_Life . It is a Cellular Automation application with a virtual grid of z , y coordinates pre-populated, the rules below are then applied and repeated. A cell represents a coordinate or a Merkle.

  • Any live cell with fewer than two live neighbours dies, as if caused by under-population.
  • Any live cell with two or three live neighbours lives on to the next generation.
  • Any live cell with more than three live neighbours dies, as if by overcrowding.
  • Any dead cell with exactly three live neighbours becomes a live cell, as if by reproduction.

Download the SQL Server application here – https://paulbrewer.wordpress.com/ca_sqlbenchmark/

Merkles exist at each iteration is as x and y axis coordinates, the grid below shows four of the different, known patterns (see wiki link) which are used in this application and cycle in interesting ways.

After running the CA Setup script, execute the ‘Initialise Patterns’ and ‘Display Patterns’ stored procedures to create and show Merkles in their initial state, click on the ‘Spatial Results’ tab in SQL Server Management Studio to view as shown below.

EXECUTE dbo.CA_InitPatterns @StressLevel = 2;
EXECUTE dbo.CA_DspPatterns_SQL;


Run the Pattern Generator stored procedure requesting the generation of 5 new patterns as below, each iteration is the result from applying rules to the previous pattern. After 5 iterations the patterns differ to those at the start immediately after initialization, the cellular automation of cells in cycles results in new patterns, the new patterns match the initial pattern every 6th iteration (with these Stress Level 2 parameter patterns).

EXECUTE dbo.CA_GenPatterns @NewPatterns = 5, @Generator = 'IO';
EXECUTE dbo.CA_DspPatterns_SQL;


The patterns cycle through their phases and match the initial pattern again every 6th iteration as illustrated below. Run the ‘Generate Pattern’ stored procedure 1 more time to complete the 6th cycle.

EXECUTE dbo.CA_GenPatterns @NewPatterns = 1, @Generator = 'IO';
EXECUTE dbo.CA_DspPatterns_SQL;
New Pattern 3 Oscillation Cycles (Pulsar) 2 Oscillation Cycles (Blinker, Toad, Beacon)
0 – Initial Pattern Cycle 1 Cycle 1
1 Cycle 2 Cycle 2
2 Cycle 3 Cycle 1
3 Cycle 1 Cycle 2
4 Cycle 2 Cycle 1
5 Cycle 3 Cycle 2
6 Cycle 1 Cycle 1


Geometry and the SQL Server Intersects Method

With the ‘CPU benchmark’ parameter, the coordinates are converted to geometric polygons, cell sizes are increased by 20% and the geometric ‘Intersect’ method is used to count adjacent Merkles. This cell expansion is illustrated by the ‘Toad’ pattern (see wiki) shown below in its base state/starting pattern.


When the rules below are applied to the pattern above, the pattern below is generated on the 1st iteration, it returns to it’s base state/starting pattern on the next iteration, this simple Stress Level 1 pattern changes between 2 states.


Stress Level 3

The screenshot below shows the initial patterns created at Stress Level 3.


After 100 iterations, 6 new ‘glider’ patterns (see wiki) have been created as shown below, 2 new gliders are created every 30th iteration which increases the CPU, IO and memory cost incrementally with each iteration. The more new patterns are generated, the more like an OLAP workload the cellular automation becomes.



Categories: General

%d bloggers like this: