Home > Servers > Rack and Tower Servers > AMD > White Papers > Boost SQL Server OLTP performance with PowerEdge R6525 servers powered by 3rd Gen AMD EPYC 7543 processors > How we tested
We installed Windows Server 2019 with Hyper-V and Failover Clustering on each Dell EMC PowerEdge R6525 server and used Storage Spaces Direct to pool a set of PCIe 4.0 NVMe SSDs: two mixed-use 1.6TB SSDs for caching and four read-intensive 1.92TB SSDs for capacity. Each server in the cluster hosted eight Microsoft SQL Server 2019 VMs against which we ran an OLTP workload. We measured the performance of the cluster using Microsoft SQL Server 2019 and the DVD Store 3 (DS3) benchmark, which targets a transactional database and produces a metric of orders per minute.
We used a single Dell EMC Networking S5048-ON switch and created management, VM private traffic, and Storage Spaces traffic VLANs across all 48 ports using two 25GbE network connections per host.
Each SQL test VM had a 28GB RAM, 16 vCPU, 100GB operating system drive, 85GB database drive, and 30GB log drive. Each VM supported a 40GB DS3 database.
We hosted 24 client DS3 VMs on a Dell EMC PowerEdge R630 server to drive the workload. Each client had a 4GB RAM, one vCPU and 137GB operating system drive. Each client VM targeted a single SQL test VM. For the DS3 test parameters, we used 32 threads, 0 think time, 30-minute run time, and 5-minute warmup.
Below are the steps we took to configure our test environment and conduct our testing.
# =================================================================================================
# Options toggles
# =================================================================================================
# ---------------------------------------------------------------------------------------------------
# -- Toggle script sections --
# ---------------------------------------------------------------------------------------------------
# Create a 1-disk or 3-disk VM (for SQL DB/log files)
$3DiskVM = $true
# Validate DVD Store DB and Log folders are in place
$ConfigureForDVDStore = $true
# Install SQL Server
$InstallSQL = $true
# Path
$VMpath = "C:\GoldenVM"
# ----------------------------------------------------------------------------------------------------
# -- VM guest configuration --
# ----------------------------------------------------------------------------------------------------
# Hyper-V hardware version. Generation 1 or 2.
$Generation = ‘2'
# Set disk size and type. Must be greater than 25GB for the OS.
$DiskOSSize = 100*1000000000
$DiskDBSize = 85*1000000000
$DiskLogSize = 30*1000000000
# Destination disk format
$OSDiskFormat = "EagerZeroedThick" # EagerZeroedThick, Thick, Thin
$DBDiskFormat = "EagerZeroedThick" # EagerZeroedThick, Thick, Thin
$LogDiskFormat = "EagerZeroedThick" # EagerZeroedThick, Thick, Thin
# vSwitch - Please configure your vSwitch before running this script.
$VirtualSwitch = "SETSwitch"
$VirtualSwitch2 = "Internet"
# Target VM name
$VMName = ‘AMD-SQL-GOLD'
# VM CPU allocation
$VMCPU = 16
# VM RAM allocation 28GB
$VMMemory = 1024*1024*1024*16
# Path to ISO files - please copy to local directory before running script.
$WindowsDisc = <path to Windows Server 2019 ISO>
$SQLDisc = <path to SQL Server 2019 RC ISP>
# DS2 SQL backup file location - please copy to local directory before running script.
#Local file copied from "\\pt-data01.ptnet.principledtech.com\pt\Everyone\Chris B\memoria\DS3-40GB. bak"'
#DS2Backup is local hypervisor location of backup file
#DS2BackupPath is VM location to copy backup file to.
$CopyDS2Backup = $true
$DS2Backup = <Local directory with a copy of DS3-40GB.bak (This file should be generated using the benchmarking directions at the end of the methodology)>'
$DS2BackupPath = <VM directory where DS3040GB.bak will be stored>
$SQLInstallCommandLine = ‘ /qs /ACTION=Install /FEATURES=SQLEngine,Replication,FullText / INSTANCENAME=MSSQLSERVER /SECURITYMODE=SQL /SAPWD=Password1 /SQLSVCACCOUNT="NT AUTHORITY\SYSTEM" /
SQLSYSADMINACCOUNTS="Administrators" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /TCPENABLED=1 / IACCEPTSQLSERVERLICENSETERMS /UpdateEnabled=True /UpdateSource=MU'
# Wait for VM to first boot. If script errors because of slow VM load, increase this value. (Default 60)
$BootTime = 120
# --------------------------------------------------------------------------------------------------------
# -- Windows guest variables --
# --------------------------------------------------------------------------------------------------------
# -
# Initialization security definitions
# -
# Local admin
$Username = ‘Administrator'
$Password = ‘Password1'
$SecPass = ConvertTo-SecureString -AsPlainText $Password -Force
$SecCred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username, $SecPass
# Create destination VM folder
$FolderCheck = Test-Path $VMpath -PathType Container if ($FolderCheck -eq $false)
{
try {
Write-Host -ForegroundColor Green " - Creating destination VM folder $VMpath on localhost" New-Item -Path $VMpath
ItemType Directory -ErrorAction Stop | Out-Null #-Force
}
catch {
Write-Host -ForegroundColor Red "Unable to create destination VM directory ‘$VMpath'. Error was: $_"
-ErrorAction Stop
}}
else
{
Write-Host "$VMpath folder exists" -ForegroundColor DarkGreen
}
# =================================================================================================
# Create VM
# =================================================================================================
# Create VM
Write-Host -ForegroundColor Green "1. Creating OS VHDx."
if ($OSDiskFormat -eq "EagerZeroedThick" -OR $OSDiskFormat -eq "Thick") {
#Thick provisioned
$OSVHDParam = @{
Path = "$VMpath\OS.vhdx" Fixed = $True
SizeBytes = $DiskOSSize
}
} Else {
#Thin provisioned
$OSVHDParam = @{
Path = "$VMpath\OS.vhdx" Dynamic = $True SizeBytes = $DiskOSSize
}}
New-VHD @OSVHDParam function Eagerzero{
param( [string]$vhdxpath
)
Mount-VHD -Path $vhdxpath -passthru -Confirm:$false| Initialize-Disk -Passthru -PartitionStyle GPT
-Confirm:$false| New-Partition -DriveLetter Z -UseMaximumSize | Format-Volume -Confirm:$false -Force
$tozero = (fsutil volume diskfree Z:).split(" ")[13] fsutil file createnew Z:\clear $tozero
fsutil file setvaliddata Z:\clear $tozero
fsutil file setzerodata offset=0 length=$tozero Z:\Clear del Z:\Clear
Remove-Partition -DriveLetter Z -confirm:$False Dismount-VHD -Path $vhdxpath
}
if ($OSDiskFormat -eq "EagerZeroedThick") { Eagerzero "$VMpath\OS.vhdx"
}
Write-Host -ForegroundColor Green "2. Creating VM."
$NewVMParam = @{ Name = $VMName
MemoryStartupBytes = $VMMemory SwitchName = $VirtualSwitch2 VHDPath = "$VMpath\OS.vhdx" Path = $VMpath
Generation = $Generation
}
$VM = New-VM @NewVMParam
$VM = Set-VM -VMName $VMName -ProcessorCount $VMCPU
$VM = Get-VM -Name $VMName
# Mount install ISO
$VMDVDParam = @{ VMName = $VMName Path = $WindowsDisc Confirm = $False
}
Add-VMDvdDrive @VMDVDParam
# Start VM
Start-VM $VMName Start-Sleep $BootTime
Write-Host -ForegroundColor Green "3. Open Hyper-V and connect to the new VM. Run the Windows install process."
Write-Host -ForegroundColor Green "4. Once the install is complete, log in with the default user and password. "
Write-Host -ForegroundColor Green "5. You may be prompted to select if the server is discoverable on the network. Select the appropriate choice for your environment. "
Write-Host -ForegroundColor Cyan " ** If you are not using DHCP for the VM, you must statically assign an IP at this point. **"
# Wait for VM to reboot and installation to complete Pause
Stop-VM -Name $VMName -Confirm:$false
do {} until ((Get-VM $VMName).State -eq "Off")
# Dismount OS install ISO
Write-Host -ForegroundColor Green "6. Dismounting and removing virtual CD-ROMs."
$DVD = Get-VMDvdDrive -VMName $VMName
Remove-VMDvdDrive -VMDvdDrive $DVD -Confirm:$False Start-Sleep -s 5
# Power on VM
Write-Host -ForegroundColor Green "7. Starting VM." Start-VM $VMNameStart-Sleep $BootTime
# Windows Updates
Write-Host -ForegroundColor Green "8. Log in, run "-NoNewline Write-Host -ForegroundColor Yellow "sconfig.cmd"-NoNewline
Write-Host -ForegroundColor Green ", and change Windows Update Settings to " -NoNewline Write-Host -ForegroundColor Yellow "Manual (Option 5)" -NoNewline
Write-Host -ForegroundColor Green ", and then run "
Write-Host -ForegroundColor Yellow "Download and Install Updates (Option 6) " -NoNewline Write-Host -ForegroundColor Green ", and install all updates."
Write-Host -ForegroundColor Yellow "Once Complete, Enable Remote, change DNS to your DNS server, change the name, and add the machine to your domain"
Write-Host -ForegroundColor Green "9. When all updates are complete, continue this script."
Write-Host -ForegroundColor Yellow "Note that if Windows says no updates are available, the VM may not have an IP address."
# Wait for user to run Windows Updates Pause
if ($3DiskVM -ne $true)
{
# Reboot VM
Write-Host -ForegroundColor Green "Rebooting VM" ReStart-VM -VMname $VMName -RunAsync -Confirm:$false Start-Sleep $BootTime
}
# =================================================================================================
# Configure DB and Log disks
# =================================================================================================
# Create DB and Log VHDx
# If 3-disk is true, create VHDx if($3DiskVM) {
# Shutdown VM
Write-Host -ForegroundColor Green "10. Shutting down VM."
Stop-VM -Name $VMName -Confirm:$false
do {} until ((Get-VM $VMName).State -eq "Off")
Start-Sleep 15
# Create DB and Log disks
Write-Host -ForegroundColor Green "11. Creating additional DB/Log disks and attaching." if ($DBDiskFormat -eq "EagerZeroedThick" -OR $DBDiskFormat -eq "Thick") {
#Thick provisioned
$DBVHDParam = @{
Path = "$VMpath\DB.vhdx" Fixed = $True
SizeBytes = $DiskDBSize
}
} Else {
#Thin provisioned
$DBVHDParam = @{
Path = "$VMpath\DB.vhdx" Dynamic = $True SizeBytes = $DiskDBSize
}}
if ($LogDiskFormat -eq "EagerZeroedThick" -OR $LogDiskFormat -eq "Thick") {
#Thick provisioned
$LogVHDParam = @{
Path = "$VMpath\Log.vhdx" Fixed = $True
SizeBytes = $DiskLogSize
}
} Else {
#Thin provisioned
$LogVHDParam = @{
Path = "$VMpath\Log.vhdx" Dynamic = $True
SizeBytes = $DiskLogSize
}}
New-VHD @DBVHDParam New-VHD @LogVHDParam
if ($DBDiskFormat -eq "EagerZeroedThick") { Eagerzero -vhdxpath "$VMpath\DB.vhdx"
}
if ($LogDiskFormat -eq "EagerZeroedThick") { Eagerzero -vhdxpath "$VMpath\Log.vhdx"
}
$AddDBVHDParam = @{
Path = "$VMpath\DB.vhdx" ControllerType = ‘SCSI' ControllerLocation = 1 VM = $VM
}
Add-VMHardDiskDrive @AddDBVHDParam
$AddLogVHDParam = @{ Path= "$VMpath\Log.vhdx" ControllerType = ‘SCSI' ControllerLocation = 2 VM = $VM
}
Add-VMHardDiskDrive @AddLogVHDParam
# Power on VM
Write-Host -ForegroundColor Green "12. Starting VM."
Start-VM $VMName Start-Sleep $BootTime
# Bring VHDx online Start-Sleep 30
Write-Host -ForegroundColor Green "13. Setting disks Online."
$s = New-PSSession -ComputerName $VMName -Credential $SecCred
Invoke-Command -Session $s -ScriptBlock { Get-Disk | Where-Object IsOffline -Eq $True | Set-Disk
-IsOffline $False }
# Initialize DB and log drives
Write-Host -ForegroundColor Green "14. Initializing disks."
Invoke-Command -Session $s -ScriptBlock {set-disk -Number 1 -IsOffline $false} Invoke-Command -Session $s -ScriptBlock {set-disk -Number 2 -IsOffline $false} Invoke-Command -Session $s -ScriptBlock {set-disk -Number 1 -IsReadOnly $false} Invoke-Command -Session $s -ScriptBlock {set-disk -Number 2 -IsReadOnly $false} Invoke-Command -Session $s -ScriptBlock {clear-disk -Number 1 -Confirm:$false} Invoke-Command -Session $s -ScriptBlock {clear-disk -Number 2 -Confirm:$false} Invoke-Command -Session $s -ScriptBlock {Initialize-Disk 1 -PartitionStyle GPT} Invoke-Command -Session $s -ScriptBlock {Initialize-Disk 2 -PartitionStyle GPT}
# Set drive letters. G: database, H: transaction logs.
Write-Host -ForegroundColor Green "15. Setting drive letters to G and H"
Invoke-Command -Session $s -ScriptBlock {New-Partition -DiskNumber 1 -UseMaximumSize
-AssignDriveLetter}
Invoke-Command -Session $s -ScriptBlock {New-Partition -DiskNumber 2 -UseMaximumSize
-AssignDriveLetter}
Invoke-Command -Session $s -ScriptBlock {Set-Partition -DiskNumber 1 -PartitionNumber 2
-NewDriveLetter G}
Invoke-Command -Session $s -ScriptBlock {Set-Partition -DiskNumber 2 -PartitionNumber 2
-NewDriveLetter H}
# Format DB and log drives
Write-Host -ForegroundColor Green "16. Quick-formatting drives." Invoke-Command -Session $s -ScriptBlock {Format-Volume -DriveLetter G} Invoke-Command -Session $s -ScriptBlock {Format-Volume -DriveLetter H}
}
# =================================================================================================
# Configure VM for DVDStore
# =================================================================================================
if ($ConfigureForDVDStore)
{
Write-Host -ForegroundColor Green "17. Configuring DVDStore paths." Invoke-Command -Session $s -ScriptBlock {New-Item G:\db -type directory}
Invoke-Command -Session $s -ScriptBlock {New-Item H:\log -type directory}
# Copy DVDStore restore file to the C:\ drive
if ($CopyDS2Backup)
{
Write-Host -ForegroundColor Green "18. Copying DVDStore SQL backup manually."
Write-Host -ForegroundColor Yellow "Copy-Item $DS2Backup -Destination $DS2BackupPath" pause
}
}
# =================================================================================================
# Install SQL Server 2019 RC
# =================================================================================================
# Install SQL Server if ($InstallSQL)
{
Write-Host -ForegroundColor Green "19. Installing SQL Server." Write-Host -ForegroundColor Green " - Attaching DVD."
Stop-VM -Name $VMName -Confirm:$false
do {} until ((Get-VM $VMName).State -eq "Off")
# Mount ISO
$VMDVDParam = @{ VMName = $VMName Path = $SQLDisc Confirm = $False
}
Add-VMDvdDrive @VMDVDParam
Write-Host -ForegroundColor Green " - Starting VM." Start-VM -VMname $VMName
Start-Sleep $BootTime
Write-Host -ForegroundColor Green " - Start Installation Manually." Write-Host -ForegroundColor Yellow "D:\Setup.exe $SQLInstallCommandLine"
Write-Host -ForegroundColor Green "20. Continue when installation is complete."
Pause
# Remove install disc
Write-Host -ForegroundColor Green "21. Dismounting and removing virtual CD-ROMs." Start-Sleep 15
Write-Host -ForegroundColor Green " - Shutting down VM."
Stop-VM -Name $VMName -Confirm:$false
do {} until ((Get-VM $VMName).State -eq "Off")
# Dismount SQL install ISO
Write-Host -ForegroundColor Green " - Dismounting disc." Set-VMDvdDrive -VMName $VMName -Path $null -Confirm:$false Start-Sleep -s 5
Write-Host -ForegroundColor Green " - Starting VM." Start-VM -VMname $VMName
Start-Sleep $BootTime
}
# =================================================================================================
# End of script
# =================================================================================================
Write-Host -ForegroundColor Cyan "You may wish to run Windows Update at this time if you installed SQL Server."
pause
#Copy Sysprep files to local hypervisor install directory before beginning script Write-Host -ForegroundColor Green "27. Manually copy Sysprep files to VM"
Write-Host -ForegroundColor Yellow "Copy-Item \\S2DLEGINTEL01\install\Sysprep_Files\* -Destination C:\"
pause
Write-Host -ForegroundColor Green "28. Manually disable remote, remove from domain, and remove DNS." pause
Write-Host -ForegroundColor Green "29. Manually Run this file in C:\ as Administrator:" -NoNewline Write-Host -ForegroundColor Yellow "RunMeAsAdministrator.bat"
pause
Write-Host -ForegroundColor Green "30. In Hyper-V change the settings for the VM." Write-Host -ForegroundColor YTellow "Change automatic stop action to Shut down." Write-Host -ForegroundColor Red "==============="
Write-Host -ForegroundColor Red "SCRIPT COMPLETE" Write-Host -ForegroundColor Red "===============" pause
EXIT
$VMName = ‘GOLDENVM'
$ExportDirectory='<Insert appropriate directory here.>'
#Make Export
$FolderCheck = Test-Path $ExportDirectory -PathType Container if ($FolderCheck -eq $false)
{
try {
Write-Host -ForegroundColor Green " - Creating destination Export folder $ExportDirectory on localhost"
New-Item -Path $ExportDirectory -ItemType Directory -ErrorAction Stop | Out-Null #-Force Write-Host -ForegroundColor Yellow "Make sure you have done sysprep before we export." pause
Export-VM -Name $VMName -Path $ExportDirectory
}
catch {
Write-Host -ForegroundColor Red "Unable to create destination Export folder ‘$VMpath'. Error was:
$_" -ErrorAction Stop
}}
else
{
Write-Host -ForegroundColor Yellow "$ExportDirectory folder exists, assuming VM is already exported."
}
Write-Host -ForegroundColor Green "Export Complete"
#Make copies and import
$Volumes = Get-VirtualDisk | where-object {$_.FriendlyName -Match "^Volume?"}
$startno = 1
$Endno = 8
foreach ($volume in $Volumes) {
$name = $volume.FriendlyName
for ($VMno=$startno; $VMNo -le $Endno; $VMNo++) {
$destination = "C:\ClusterStorage\$name\Copy$VMno" mkdir $destination
$MyVM = Import-VM -Path (ls "$ExportDirectory\$VMName\Virtual Machines\*.vmcx").tostring() -Copy
-GenerateNewID -VhdDestinationPath $destination -VirtualMachinePath $destination Rename-VM -VM $MyVM -NewName "VM$VMno"
}
$startno=$Endno+1
$Endno=$EndNo+8
}
We generated the data using the Install.pl script included with DS3, providing the parameters for our 40GB database size and the database platform we used. We ran the Install.pl script on a utility system running Linux® to generate the database schema.
After processing the data generation, we transferred the data files and schema creation files to a Windows-based system running SQL Server 2019. We built the 40GB database in SQL Server, then performed a full backup, storing the backup file remotely for quick access.
We used that backup file to restore the database when necessary.
The only modification we made to the schema creation scripts were the specified file sizes for our database. We explicitly set the file sizes higher than necessary to ensure that no file-growth activity would affect the outputs of the test. Other than this file size modification, we created and loaded the database in accordance with the DVD Store documentation. Specifically, we followed these steps:
We created a series of batch files, SQL scripts, and shell scripts to automate the complete test cycle. DVD Store outputs an orders-per-minute metric, which is a running average calculated through the test. In this report, we report the last OPM that each target reported.
Each complete test cycle consisted of these general steps:
We used the following DVD Store 3 parameters for testing:
ds3sqlserverdriver.exe --target=<target _ IP> --ramp_rate=10 --run_time=30 --n_threads=32 --db_ size=40GB --think_time=0.00 --detailed_view=Y --warmup_time=5 --report_rate=1 --pct_newcustomers=20
--csv_output=<drivepath>