SQL Server CPU, IO and Hekaton Performance Benchmarking

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.

Description Procedure Call
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.

Setup Cellular Automation for SQL Server 2014

Setup Cellular Automation for SQL Server 2008

 

References

 

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;

CA_Cycle1

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;

CA_Cycle6

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.

Mathematical Rules

In this application, Merkles are entities who’s existence at each iteration is as x and y axis coordinates, a ‘Merkle’ is a cell and a set together form a pattern.CA_Grid

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.

CA_Blinker1b

When the rules below are applied to the pattern above:

  1. Any live cell with fewer than two live neighbours dies, as if caused by under-population.
  2. Any live cell with two or three live neighbours lives on to the next generation.
  3. Any live cell with more than three live neighbours dies, as if by overcrowding.
  4. 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.

CA_Blinker2b

Appendix – Stress Level 3 Workload

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

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.

CA_StressLevel3_100



Categories: Hekaton, Performance Benchmarking

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: