In this guide, we will discuss “Testing Disk Subsystem Integrity for SQL Server with SQLIOSim”. The performance of a SQL Server instance is critically dependent on the underlying disk subsystem. High latency, insufficient throughput, or suboptimal storage configurations can negatively impact stability and scalability, regardless of code quality or available CPU and memory resources. Please, see “Scan Apple Calendar: Prevent Norton from scanning your Apple Calendar on iOS“, and Sync file and photos from iOS and Mac with Synology Drive“.
For this reason, proactive validation and objective analysis of I/O performance are essential activities in the design, troubleshooting, and capacity planning of SQL Server environments.
Also, see Veeam Backup and Replication Disk I/O with diskspd, and how to “Install and conduct performance testing using Apache JMeter on your Web App“. Here is How to sync your passwords across iOS and Mac devices
SQLIOSim Purpose
This technical guide is dedicated to SQLIOSim, a utility designed to simulate SQL Server–like workloads on a disk subsystem.
By generating realistic and configurable I/O patterns, SQLIOSim enables administrators and engineers to evaluate storage behavior under controlled conditions, isolate variables, and collect meaningful metrics to support informed architectural decisions. Throughout this guide, you will find:
- an overview of how SQLIOSim works and its primary use cases;
- guidance on configuring tests based on common SQL Server workloads (OLTP, DSS, tempdb, transaction logs);
- instructions on interpreting results and key performance indicators such as latency, IOPS, and throughput;
- best practices for using the tool in test, pre-production, and troubleshooting scenarios.
This guide is intended for DBAs, system engineers, and infrastructure professionals who want a deeper understanding of how storage performance affects SQL Server.
Please, see “Enable or disable Core Isolation Memory Integrity in Windows 10 and 11“, how to install WSL on Windows, and MSSQL DMA Compatibility Mode: Prepare and Migrate Safely.
SQLIOSim Copy to Target Location
The goal is to provide a practical, methodical, and repeatable approach to using SQLIOSim as a reliable support tool for performance analysis and infrastructure decision-making.
Copy the entire folder to the disk you intend to test on the target host. In this example, the folder is copied to drive P:\ on the host where the database resides and where the I/O test will be executed.
Within the SQLIOSIM folder, edit the sqliosim.cfg.ini file to update the paths for the test databases used by the simulator.
These paths must point to the disks or mount points you want to evaluate, ensuring that the simulated I/O workload is generated against the correct storage locations.
In this case, replace the path D:\SQLIOSIM with P:\SQLIOSIM\SQLIOSimX64 in all entries related to the filename parameter.
This ensures that SQLIOSim creates and accesses its test files in the correct directory on the target disk, allowing the I/O workload to be executed against the intended storage subsystem.
Therefore, run sqliosim.exe using “Run as administrator”.
Please note that executing the binary does not perform any installation or deploy additional components on the target host; the utility runs directly without modifying the system.
Click “OK” to confirm the settings shown on the screen (do not make any changes).
Please, see How to protect Azure Kubernetes Service (AKS) with Azure Backup, and Query MBAM-protected Client for non-compliance [Part 2].
SQLIOSim Stress Test
Then click ‘Start’
Next, the simulation starts, lasting 5 minutes
It should be noted that after starting, the data file and log file for the database used by the simulator. subject to the stress test were created.
The log XML file is cumulative, so you need to look for the log of the latest run. At the end of the file, there is a summary with performance indicators: sqliosim.log.xml
To read the output, refer to the following document: Please, see “understanding SQLIOSim Output“.
Please, see Connectivity to a writable domain controller from a node could not be determined because of an error, and how to Move Azure Resources between Subscriptions.
Consider the following parameters
1. Number of times IO throttled
- Description: Number of times requests were interrupted due to exceeding the allowed duration.
- Interpretation: Lower values are better.
- Reference from the document:
“Number of times a request was removed because the duration was exceeded. Low numbers are better.“
2. IO request blocks
- Description: Number of I/O requests handled concurrently.
- Interpretation: Higher values are better.
- Reference from the document:
“The more requests a system can handle concurrently, the more I/Os are issued by the tool. Characterized as ‘Concurrent IOs’.”
3. Running Average IO Duration (ms)
- Description: Average latency of operations on data files and log files (essentially, disk latency).
- Interpretation: Lower values are better.
- Data files: 0–5 ms are excellent
- Log files: 0–2 ms are acceptable
- Reference from the document:
“Low values are good. Data files: 0–5ms or so are excellent. Log files: 0–2ms are acceptable.”
4. Total IO Time (ms)
- Description: Total time taken to satisfy all test requests.
- Interpretation: Values are relative; useful for comparing drives, especially on the same system.
- Reference from the document:
“Useful for comparing drives, especially on the same system. If the values for the accumulators are similar for two drives, but one drive has significantly less Total IO Time, then the latter is more performant because it was able to service the same I/O in less time.”
I hope you found this guide on “Testing Disk Subsystem Integrity for SQL Server with SQLIOSim” very useful. Please, feel free to leave a comment below.
