Comparing SQL Server hardware and configuration settings related performance differences using disk and CPU related performance counters and DMV information can be difficult when activity levels differ massively, on different servers running different workloads. This ‘Cellular Automation’ (CA) application that when run in isolation places a precise workload that can be repeated, tuned, measured and compared. It includes various ‘Game of Life Pattern Generators’, one that uses Geometry and places a CPU load, another that uses set based queries that are IO bound and a third ‘in memory OLTP’ version that uses in-memory tables and compiled stored procedures. All three are implementations of ‘Conway’s Game of Life’, and a precise way to benchmark and compare the relative performance of different SQL Servers. Data is inserted at the start of each benchmark then automated using a fixed set of rules described in the wiki.
Benchmarking Process Overview
The following link explains the rules implemented by this application http://en.wikipedia.org/wiki/Conway’s_Game_of_Life . It’s a Cellular Automation application with a virtual grid of z , y coordinates pre-populated with different patterns, 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.
The benchmarking procedure creates one of three initial patterns which are then ‘automated’ according to the rules above, the table below shows all parameters available:
| Parameter Name | Type | Description | Default |
| @Batches | INT | Number of calls to the benchmarking procedures | 0 |
| @CPU_Benchmark | BIT | Execute the CPU bound benchmark procedure | 0 |
| @IO_Benchmark | BIT | Execute the SQL Server IO bound benchmark procedure | 0 |
| @Hek_Benchmark | BIT | Execute the Hekaton in-memory, compiled benchmark procedure | 0 |
| @NewPatternsInBatch | INT | Number of new patterns (automations) generated by each call | 0 |
| @DisplayPatterns | BIT | Show final result pattern | 0 |
| @Initialize | BIT | Clear previous results for session before execution | 1 |
| @StressLevel | TINYINT | 1 = small oscillating initial pattern, 2 = intermediate oscillating initial pattern, 3 = Large and expanding initial pattern | 1 |
| @Description1 | VARCHAR | For later analysis | |
| @Description2 | VARCHAR | For later analysis | |
| @Description3 | VARCHAR | For later analysis |
Setup and Execution
Run the setup scripts below in SQLCMD mode to create the CA Benchmark database, then call the CA_Benchmark procedure, set the ‘Database Name’, ‘Data File’ and ‘Log File’ variables before execution.
-
SQL Server 2005 compatible setup – https://paulbrewer.wordpress.com/ca_sqlbenchmark/
-
SQL Server 2014 (Hekaton) compatible setup – https://paulbrewer.wordpress.com/ca_sqlhekaton/
CPU Benchmarking Simulation using Geometry
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 automation rules 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.
To simulate this example, execute the T-SQL below against the CA database (top left pattern).
EXECUTE dbo.CA_Benchmark @IO_Benchmark = 1, @DisplayPatterns = 1 ,@StressLevel = 2, @Batches = 1, @NewPatternsInBatch = 1; EXECUTE dbo.CA_Benchmark @IO_Benchmark = 1, @DisplayPatterns = 1 ,@StressLevel = 2, @Batches = 1, @NewPatternsInBatch = 2;
OLAP Bias Workload Simulation
The screenshot below shows the initial patterns created at Stress Level 3.
The screen shot below shows the patterns after 60 automations, 2 new ‘Gosper Glider Guns’ patterns (see Wiki) are created every 30th automation which increases the CPU, IO and memory cost. The more new patterns are generated, the more like an OLAP workload the automation benchmark gradually becomes.
To simulate this example, execute the T-SQL below against the CA database.
EXECUTE dbo.CA_Benchmark @IO_Benchmark = 1, @DisplayPatterns = 1 ,@StressLevel = 3, @Batches = 1, @NewPatternsInBatch = 1; EXECUTE dbo.CA_Benchmark @IO_Benchmark = 1, @DisplayPatterns = 1 ,@StressLevel = 3, @Batches = 1, @NewPatternsInBatch = 60;References
- 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.mssqltips.com/sqlservertip/4210/extracting-showplan-xml-from-sql-server-extended-events/ – Extended Event session measured for observer overhead
- http://www.statmethods.net/management/subset.html
- The Hekaton Cellular Automation database setup script, ran before executing the scripts below.
- https://www.simple-talk.com/sql/database-administration/hekaton-in-1000-words/
- https://msdn.microsoft.com/en-gb/library/dn170449.aspx
- SQL Azure DTU Calculator – http://dtucalculator.azurewebsites.net/
- SQL Azure Performance – https://cbailiss.wordpress.com/2015/01/31/azure-sql-database-v12-ga-performance-inc-cpu-benchmaring/
License
SQL Server Cellular Automation Benchmarking is licensed under the MIT license, a popular and widely used open source license.
Copyright (c) 2017 Paul Brewer
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
Appendix
The PowerShell script below was used to perform the benchmark tests in this article, OSTRESS is part of the RML tools for SQL Server (http://www.microsoft.com/en-gb/download/details.aspx?id=4511). Each line in the script was highlighted and run individually as running in a single batch caused unpredictable results:
cd "C:\Program Files\Microsoft Corporation\RMLUtils" Set-Location . cls ######################## # Hekaton / SQL # Two concurrent requests # OLTP ./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log1" ./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log2" # OLAP ./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1,@Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log3" ./ostress -SVM01 -dCA_SQLHekaton -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'Two Concurrent Requests', @Initialize = 1;" -o"C:\temp\log4" ######################## # Hekaton / SQL # One concurrent request # OLTP ./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log1" ./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log2" # OLAP ./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1,@Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log3" ./ostress -SVM01 -dCA_SQLHekaton -n1 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @Hek_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'One Concurrent Request', @Initialize = 1;" -o"C:\temp\log4" ######################## # SQL # MAXDOP=0 ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'MAXDOP=0', @Initialize = 1;" -o"C:\temp\log1" ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'MAXDOP=0', @Initialize = 1;" -o"C:\temp\log1" # MAXDOP=1 ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'MAXDOP=1', @Initialize = 1;" -o"C:\temp\log1" ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'MAXDOP=1', @Initialize = 1;" -o"C:\temp\log1" ######################## # SQL # XE OFF ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'XE OFF', @Initialize = 1;" -o"C:\temp\log1" ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'XE OFF', @Initialize = 1;" -o"C:\temp\log1" # XE ON ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 1000, @NewPatternsInBatch = 1, @StressLevel = 1, @Description1 = 'OLTP', @Description2 = 'XE ON', @Initialize = 1;" -o"C:\temp\log1" ./ostress -SVM01 -dCA_SQLAzure -n2 -r1 -Q"EXECUTE [dbo].[CA_Benchmark] @IO_Benchmark = 1, @Batches = 10, @NewPatternsInBatch = 100, @StressLevel = 3, @Description1 = 'OLAP', @Description2 = 'XE ON', @Initialize = 1;" -o"C:\temp\log1"All results were collected using the T-SQL script below, saved as csv files and passed to the R scripts.
SELECT CONVERT(VARCHAR(16),[BatchStartTime],120) AS StartTime ,[BatchDurationMS] ,[BenchmarkPerspective] ,Description1 ,Description2 ,Description3 FROM [dbo].[CA_BenchmarkResults] ORDER BY StartTime DESCCategories: Performance Benchmarking
Paul Brewer Blog 