Description

Learn the fundamentals of load testing SQL Server - from planning to execution and analysis - to boost performance on-premises or in the cloud. We’ll cover hardware, VM, and I/O stack insights, compare synthetic vs. real workloads, explore tools like DiskSpd, HammerDB, and Workload Tools, and use PowerShell, DMVs, and PerfMon for automated data collection and analysis.

Key Takeaways

• ● You need to know what’s possible with the gear you have
• Understand how data flows
• CPU as is important as the storage
• Dive deep, read the manuals
• Understand your SKU’s capabilities
• CPU – Number of Cores
• Virtual Machine IOP/Throughput Limits

My Notes

Action Items

• [ ]

Resources & Links

Slides

Source: Decks/load_testing_performance_for_sql_server.pdf

Load Testing Performance
for SQL Server
Anthony E. Nocentino
He/Him
Senior Principal Field Solution Architect
Everpure - Formerly Pure Storage
Anthony E. Nocentino
Senior Principal Field Solution Architect @ Pure Storage

Specialize in system architecture, performance, SQL Server,
Kubernetes, Containers, Microsoft Azure and VMware
email: anocentino@purestorage.com
Blog: www.nocentino.com
GitHub: https://github.com/nocentino/
Pluralsight Author: www.pluralsight.com
BlueSky: https://bsky.app/profile/nocentino.com
Agenda
● Understanding Your Hardware’s Capabilities
● Load Testing Strategies
● Interpreting Performance Data
Understanding Your Hardware’s Capabilities
Understanding Your Hardware’s Capabilities
● You need to know what’s possible with the gear you have
○ Physical Systems
○ Virtual Machines
○ Cloud
● What about higher up in the stack?
○ CPU / Transactions / Application
○ ABSOLUTELY!
Understanding the IO Stack
• Remote
• Fibre Channel
• iSCSI
• NVMe-oF
SQL Server
Physical or Virtual Machine
Data Files
Log Files
Reads
•On disks
• In files
• Local
• SCSI
• PCIe
• NVMe
Writes
•SQL Server stores data…
Understanding Your Hardware’s Capabilities
• Understand how data flows
through the machine
• CPU as is important as the storage
performance
SQL Server
Physical or Virtual Machine
Host
t
ec
n
n
o
rc
Inte
• Dive deep, read the manuals
Data Files
Log Files
e
ag
r
o
t
S
Physical Machine Considerations – Local Storage
t
u
o
b
ta
Wha A?
NUM
Physical Machine
CPU
Mem
NIC
Storage IO Path
X
X
e
PCI
e
Data
Volume(s)
Log
Volume
NVM
https://www.youtube.com/watch?v=4I8MSSUeLzY
Physical Machine Considerations – Remote Storage
XX
e
I
PC
Physical Machine
CPU
Mem
NIC
HBA or NIC
?
Storage
IO Path
Data
Volume(s)
Log
Volume
ec
s
/
b
G
2x3
c?
e
s
/
Gb
x 32
Virtual Machine Considerations
g
fi
n
o
MC
Virtual Machine
vCPU Mem vNIC
Storage
IO Path
Physical Hardware
CPU
Mem
NIC
V
Virtual Disk(s)
HBA or NIC
Storage
IO Path
or
y
Nois
ons
ti
dera
i
s
n
e co
cal
Sam s Physi
a
Remote Storage
Data Volume(s)
hb
Neig
Log Volume
https://www.youtube.com/watch?v=Klj8aeBjMSs
Cloud Considerations
• Understand your SKU’s capabilities
• CPU – Number of Cores
• Virtual Machine IOP/Throughput Limits
• Local Storage
• Remote Storage
• Disk IOP Limits/Throughput
• Local Storage
• Remote Storage
• Network Throughput
Cloud Considerations – Understanding VM SKUs
Ebdsv5 Series (NVMe)
Reads
Writes
Understanding Queuing
Database Files
dm_io_virtual_file_stats
SQL Server
Wait Stats
Hypervisor / OS
Disk Latency
Storage Interconnect
Network Latency
Storage
Volume Latency
Each layer presents a potential to inject latency
If a layer can’t support the IO demand, then queuing can occur
Load Testing Strategies and Tools
Little’s Law
• Three Key Characteristics of Process Behavior:
• Throughput (IOPS): Input/output operations per second
• Queue Time (Latency): Time data spends waiting in the queue
• Inventory (Queue Depth): Number of requests in the queue
• Influence on System Performance:
• You can change one characteristic to impact the other two
• Use Little's Law to analyze and optimize processes
https://www.youtube.com/watch?v=9yjZpBq1XBE
Finding the Maximum in a System
IOPs
X
IO Size

Throughput
IOPs
X
Latency

Avg. Queue
Depth
You Goal When You’re Designing a Load Test –
Increase IOPs until latency is unacceptable
Saturation
Latency
Kn
ee
Increase IOPs
System’s Latency
IOPs / Throughput
Make a change
• Reduce IOPs
• Add capacity
to the system
in terms of
throughput
Load Testing Strategies
• Synthetic
• Real Workloads
What Are You Testing For?
• Synthetic
• Throughput
• Latency
• Real Workloads
• Transactions per second
• Critical stored procedure
• Key Performance Indicator
• Add to cart?
• Checkout?
What Are You Testing For?
•
OLTP vs OLAP
•
•
Latency
Throughput
•
SQL Server does 8KB IOs, right? What about 64KB IOs?
•
Average IO sizes
•
•
Read and writes
You can get these from your current SQL Server
•
Log flush – 512B-60KB
•
Read-ahead – up to 512KB
•
Backups – 1MB-4MB
https://www.nocentino.com/posts/2021-12-10-sqlserver-io-size/
Load Testing Tools
DiskSpd
HammerDB
Workload Tools
DEA
SQL Server
Synthetic
Synthetic
Real Workload Real Workload
Hybrid
Only Storage
Standard
Workloads
Playback Your
Workload
Playback Your
Workload
Actual
Workload
Also
“Synthetic”
My Test Rig – Azure VM – Edbsv5 (NVMe)
IOPs
K
/sec
B
M
Virtual Machine
CPU
Mem
NIC
Remote Disk
Storage
IO Path
SSD
SSD
SSD
SSD
Temp Disk
IOPs
K
/sec
B
M
Bursts?
HammerDB
• Open-source database benchmarking and load testing tool
• Supports major databases: SQL Server, Oracle, PostgreSQL,
MySQL, MariaDB, Db2
• Implements industry-standard workloads (TPC-C, TPC-H)
• Simulates OLTP and OLAP environments
• Scales from single-user tests to thousands of virtual users
• CLI and GUI interfaces for automation and CI/CD integration
• Widely used for performance validation, capacity planning,
and regression testing
https://github.com/nocentino/hammerdb
Interpreting HammerDB Metrics - Core Metrics
●
●
●
TPM (Transactions Per Minute)
○
Total committed TPC-C transactions across all types
○
Includes Payment, Delivery, Order Status, Stock Level, and New Order
NOPM (New Orders Per Minute)
○
TPC-C compliant primary metric
○
Represents committed New Order transactions only
○
Used for cross-platform performance comparison
QPHH (Query Per Hour @ Scale Factor)
○
TPC-H composite metric
○
Derived from Power Test + Throughput Test
Interpreting HammerDB Metrics - Scaling
Analysis
●
Throughput vs VU curve (linear region -> saturation -> contention)
●
Knee-point identification
●
CPU-bound vs I/O-bound determination
●
Locking and latch contention analysis
My Test Rig – Remote Storage
c
x8
/
.
Ie 3
Physical Machine
CPU
Mem
NIC
HBA or NIC
PC
c
Storage
IO Path
2x
/se
b
G
c
Data
Volume(s)
Log
Volume
Throughput ~= 4GB/sec
Latency ~= 0.3ms
/se
b
G
6x1
/se
B
G
.8
Demo: Using Load Testing Tools
Collecting and Interpreting Performance Data
Key Performance Metrics
Latency
Throughput
IOPs
How Long a Request
Takes
Amount of data moved
Number of Requests
Transaction Log I/Os
Function of the storage
interconnect
Size Depends on the
Application
Saturation Leads to
Queuing
Type of storage used
Understanding the Timeline of an IO
Disk
Transfers/Sec
IO IO
IO
IO
IO IO IO
SQL Server
Data File
Disk Queue Length
KB
KB
KB
B
KB
KB
KB
Avg. Disk Bytes/Transfer
DMVs
Wait Stats
Disk
Bytes/Sec
KB
Interconnect
Storage
Network
Avg. Disk Sec/Transfer
Physical
Disk(s)
Collecting Data
• Perfmon
• Resource Monitor
• PowerShell
• SQL Server DMVs
• Hypervisor / Cloud
Interpreting Data
• Understanding What’s Good and What’s Bad?
• Hardware Capabilities
• You’re going to have to read the manuals!
Demo: Getting Performance Data
Demo: Identifying Queuing in an IO Stack
So…How Do YOU Design a Load Test
• It depends…
Review
● Understanding Your Hardware’s Capabilities
● Load Testing Strategies and Tools
● Interpreting Performance Data