In this article, we will learn how to test our storage subsystems performance using Diskspd. The storage subsystem is one of the key performance factors for SQL Server because SQL Server storage engine stores database objects, tables, and indexes on the physical files. Therefore, the storage engine always interacts with the disk subsystem because of data processing. In this context, when a bottleneck occurs on the storage subsystems, it causes a negative impact on SQL Server performance. It would be the right approach to measure the performance of the disks to be used before the SQL Server installation based on their usage purposes. For example, OLTP databases have to complete delete, insert, and update processes in a short time but OLAP databases handle a huge amount of batch data. In this case, the storage requirement of these two database systems should differ from each other. In short, it is a best practice to test and analyze the performance of the storage subsystems according to their usage purposes so that we can eliminate the I/O problems in advance.
Firstly, we will shortly look into the essential disk measurement metrics before going into details about the Diskspd.
Basic Concepts
IOPS (input/output operations per second) indicates how many operations are completed by the disk per second and this indicator is used to evaluate the performance of the storage. The IOPS is calculated by counting the transactions performed by the disk in one second and the amount of the data is not considered for this measurement method. So, only using this metric to decide the performance of the storage will be improper.
Throughput indicates how much data can be transferred by the storage unit in a second. In general, it is measured in megabytes per second (MB/sec).
Latency is a measurement of the response time of a storage device to the request it receives and its unit is the millisecond. Particularly for databases, high latency negatively affects performance. For example, the log files of the OLTP databases that are planned to receive high modification request should be placed on the disks which have low latency.
What is Diskspd?
The DiskSpd is a disk testing tool and it is used to measure and report the capability of the disk through generating various types of fake workloads. There are various disk performance benchmark tools (IOmeter, ATTO, HDTune, etc.) but this performance testing tool offers more realistic scenarios and reliable results, for this reason, it is preferred for the storage performance benchmarks. It can be used to analyze storage subsystem performance capabilities and limits with the help of a variety of parameters. Another advantage of this tool is that the results are offered as a text or XML output. However, the best thing is that it is open-source.
As a result, storage subsystems performance capabilities are the critical factors so it should be considered before any SQL Server installation.
Usage Instructions
DiskSpd can be downloaded from this link, A Robust Storage Performance Tool and does not require any installation or framework. The zip file contains every file which we needed.
In this zipped file, we can find out three different release for the various systems:
- amd64: For 64 bit operating systems
- x86: For 32 bit operating systems
- ARM64: For 32 bit operating systems
It is a command-line tool, for this reason, we need to open a command prompt with administrative rights and then we navigate the directory where we copied the diskspd.exe. When we execute the diskspd.exe without parameter on the command prompt, it returns version details and all parameter descriptions.
As we can see, diskspd.exe can be executed a variety of parameters but in the following table, we will look at the important ones and we can also find detailed information in Command line and parameters help documentation.
|
Parameter |
Description |
|
-b |
Block size in bytes and the default value is 64KB |
|
-d |
The duration of the test and the default value is 10 seconds |
|
-o |
The number of outstanding I/O requests per target per thread and the default value is 2 |
|
-t |
Worker threads number per file |
|
-h |
Disabling hardware write caching and software caching |
|
-r |
Random I/O access and it can be overridden with the –s parameter |
|
-w |
Percentage of write requests in the test |
|
-c |
Specifies the size of the file that will be used in the test |
|
-L |
Measure the latency information |
Test Scenarios
OLTP workload scenario: This scenario is based on the OLTP workload which involves high write activities. We can start the performance test with the following parameter but at first, we will describe the details of the parameters.
diskspd -b8K –d180 -h -L –o32 –t3 -r –w75 -c5G C:\Testdata\IO.dat > resultfile.txt
-b8K: SQL Server stores data in the pages and these pages size is 8KB therefore we will set block size parameter as 8KB.
–d180: The duration of the test will be 180 second
-h: This parameter disables the caching mechanisms because SQL Server works this way.
-L: With the help of this parameter, the latency statistics will be included in the report.
-o32: The queue depth is set as 32 through this parameter
-t3: This parameter determines the number of threads.
-r: SQL Server accesses data files randomly for this reason we set this parameter as random.
-c50G: This parameter sets the sample file size that will be used in the test.
–w75: The workload of the test will be 75 percent write and the remaining will be done as reading because the OLTP workload involves more writing activity than reading.
C:\Testdata\io.dat: The sample file name that will be used in the test.
resultfile.txt: The output file of the test results.
Interpreting the test results: After executing the test, the results will be written into the resultfile.txt file and we can separate the results of the test into 4 sections to interpret. The first section of the test result shows us the input parameters and any other settings.
In the second section, we can find out the average CPU utilization details.
The Total IO section gives us all performance details about the storage performance pieces of information. 54562 is the total IOPS generated for this test and the test duration is 180 seconds so the IOPS per second will be 54562/180 = 303.09. The AvgLat column indicates the average latency and the result is 316.204. The throughput per second is 2.37 MB/s.
This storage performance is terrible and the latency does not have acceptable values so we can decide that this storage performance is not suitable for any database installation. The Read IO section gives details about the read operations and the Write IO section gives details about the write operations.
The last section of the test results shows us the latency percentile analysis of the storage performance from the minimum value up to the maximum value. This result helps to figure out how the disks behave under a load.
Logfile (LDF) workload scenario: SQL Server log mechanism uses a memory space and the transactions will be written into this buffer area before written into the log file. The capacity of this area is 60 KB and this log data will be flushes into the disk on 60 KB chunks. Another consideration about the SQL log file is, the log data will be written sequentially. In light of this information, the performance test parameter looks like the following:
diskspd –b60K –d60 -h -L –o32 –t1 -s –w100 –c1G C:\Testdata\LogIO.dat > resultLogFile.txt
Read-ahead scenario: Read-ahead mechanism is used by the SQL Storage Engine to bring continuous data pages into the buffer before the data is requested. In this mechanism, the storage engine can transfer 64 continuous data pages into the buffer. So we can set block size of the performance test as 512KB.
diskspd –b512K –d120 -h -L –o32 –t3 -si –w100 –c1G -s C:\Testdata\AheadIO.dat > resultRdAhead.txt
Conclusion
In this article, we uncovered how to test the performance of storage subsystems with DiskSpd and we also did some analysis on various scenarios, particularly for SQL Server.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023