Disk and CPU related performance counters and DMV information can be difficult to interpret and compare when activity levels differ massively. This Cellular Automation (CA) application places a precise workload that can be repeated, measured and observed. It includes various ‘Pattern Generators’, one that uses Geometry and places a CPU load, another that uses set based queries that are IO bound and another ‘Hekaton’ version that uses in-memory tables and stored procedures. All 3 are mathematically provable implementations of ‘Conway’s Game of Life’, and an effective way to benchmark and compare the relative performance of different SQL Servers.
Benchmarking Process Overview
Step 1 – Initialization – An ‘Initialize’ procedure clears down any historic patterns associated with the calling session then populates a ‘Merkle’ table with the coordinates for an initial set of new patterns. Differing initial pattern complexity levels can be set using a ‘Stress Level’ parameter:
|Initial Patterns||Procedure Call|
|A simple 3 Merkle pattern that oscillates between 2 cycles||EXECUTE dbo.CA_InitPatterns @StressLevel = 1;|
|4 Oscillating patterns including a complex 3 cycle ‘Pulsar’||EXECUTE dbo.CA_InitPatterns @StressLevel = 1;|
|4 Oscillating patterns and 2 ‘Gosper Glider Gun’ patterns described in the appendix||EXECUTE dbo.CA_InitPatterns @StressLevel = 3;|
Step 2 -Stress Testing – The ‘Pattern Generator’ procedures create the number of new patterns specified in a parameter, they use different techniques that stress different areas of a SQL instance. The Merkle table holds all patterns generated for a specific session, multiple calls can be made to the pattern generators from different sessions, each session maintains its own state.
|IO Pattern Generator – Set based queries cause IO stress||EXECUTE dbo.CA_GenPatterns @NewPatterns = 100, @Generator = ‘IO’|
|CPU Generator – Geometry data type intersects method causes CPU stress||EXECUTE dbo.CA_GenPatterns @NewPatterns = 100, @Generator = ‘CPU|
|Hekaton Pattern Generator – In-memory tables and stored procedure cause Hekaton Stress||EXECUTE dbo.CA_GenPatterns @NewPatterns = 100, @Generator = ‘HEK’|
|Load Test – Runs x iterations (Batches) of the Pattern Generators||EXECUTE @RC = [dbo].[CA_LoadTestExecute]|
|Load Test Results – Aggregates, summaries and presents stress test results||EXECUTE [dbo].[CA_LoadTestResults]|
Step 3 – Results Verification – The ‘Display Pattern’ procedure shows the highest generation of patterns for the current session. The geometry data type is used to visualize the patterns, to confirm the final pattern and check for successful completion.
|Display Patterns||Procedure Call|
|Hekaton In-memory Merkles||EXECUTE dbo.CA_DspPatterns_SQL;|
|Disk based table Merkles||EXECUTE dbo.CA_DspPatterns_Hekaton;|
There’s no learning curve involved in using this application and no client tools to install on your network, simply run one of the T-SQL setup scripts below.
- http://en.wikipedia.org/wiki/Conway’s_Game_of_Life – This Wikipedia link explains all the principles and patterns used in this article.
- https://www.simple-talk.com/sql/sql-training/the-sql-of-the-game-of-life/ – This ‘simple talk sql training’ article prompted this article.
- http://blog.sqlauthority.com/2015/04/29/sql-server-script-knowing-data-and-log-files-are-on-the-same-drive/ – Get default data folder for Hekaton file.
Cellular Automation Database Application
After running the CA Setup script, execute the ‘Initialize 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.
USE CellularAutomation GO EXECUTE dbo.CA_InitPatterns @StressLevel = 2; EXECUTE dbo.CA_DspPatterns_SQL;
Now run the Pattern Generator stored procedure requesting the generation of 5 new patterns as below, each iteration is the result from applying Conway’s rules to the previous pattern. After 5 iterations the patterns differ to those at the start immediately after initialization, the oscillation cycle results in new patterns matching the initial pattern every 6th iteration.
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|
This is the proof that this application meets all of Conway’s mathematical rules.
With the ‘CPU’ intensive pattern generator, these are converted to geometric polygons, cell sizes are increased by 20% and the geometric ‘Intersect’ method is used to count adjacent Merkles. This is illustrated in the ‘Toad’ pattern shown below in its base state/starting pattern.
When the rules below are applied to the pattern above:
- 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.
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 oscillates between 2 states.
Appendix – Stress Level 3 Workload
After 100 iterations, 6 new glider patterns have been created as shown below, 2 new gliders are created every 30th iteration which increases the CPU, IO and memory cost with each iteration. The more new patterns are generated, the more like an OLAP workload new pattern generation becomes.