The ‘Cellular Automation’ (CA) database performance benchmarking application places a precise, repeatable load on SQL Server disk and CPU resources. Queries will typically generate the same plans on each execution regardless of the hardware platform or SQL Server version. IO is benchmarked by transaction log file writes, all reads are from the buffer pool (the data read is regenerated on each execution), SQL Server Spatial methods are invoked to benchmark CPU performance.
Three platforms, a physical server, a VM and Azure:
- Virtual Machine – Hyper-V Windows Server 2016, 8GB Ram and SQL Server 2016 SP1.
- Physical Server – Windows Server 2012, 16 GB of RAM, 8 CPU’s, SSD drives with SQL Server 2014 & Hyper-V installed.
- SQL Azure database
SQL Server was benchmarked on each server with the following conditions and configurations:
- VM – With 1, 2 & 4 vCPU’s
- Physical Server – With Hyper-V running and with Hyper-V stopped.
- Azure – With the Basic Performance (5 DTU’s) and the Standard Performance (10-100 DTU’s)
The Physical Server CPU Benchmark showed a great deal of sensitivity to Hyper-V state, CPU intensive queries suffer when a virtual machine is running on the same server as SQL Server.
Performance in Azure using the basic performance option is slow from both IO & CPU perspectives.
Setup and Execution
This link is to the T-SQL setup script to create the benchmarking tables and stored procedures: https://paulbrewer.wordpress.com/ca_sqlbenchmark/
Execute the script below after setup to run a benchmark test.
USE CA_SQLBENCHMARK; GO DELETE FROM dbo.TimingResults; EXECUTE [dbo].[sp_HeartBeat] @Cycles_IO = 300 ,@Cycles_CPU = 10 ,@CycleDepth = 1 ,@TestCase = 'CA SQL Benchmark' ,@StressLevel = 3 ,@Initialize = 1; SELECT @@SERVERNAME, Generator, SUM(DATEDIFF(millisecond,StartTime, EndTime)) AS DurationMS FROM dbo.TimingResults GROUP BY Generator;
Save the query output to Excel and use for graphs.
The query plan below shows the SQL Server spatial methods call which is expensive in terms of CPU.