SQL Server (CA) Simple Performance Benchmarking Example

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.

Benchmark Environment

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)

 

Benchmark Results

CA_SQLBENCHMARK_CPU_3

 

CA_SQLBENCHMARK_IO_3

Conclusions

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.

 

APPENDIX

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.

 

CPU Benchmarking

The query plan below shows the SQL Server spatial methods call which is expensive in terms of CPU.

CPU_BENCHMARK



Categories: General

%d bloggers like this: