-->

• New McKnight Consulting Group benchmark report demonstrates double-digit performance advantage over AWS and AzureSAN JOSE, Calif., Jan. 12, 2021 (GLOBE NEWSWIRE) - Diamanti, the company that streamlines Kubernetes applications and data management for global enterprises, today announced results from a recent industry benchmark report that show Microsoft SQL Server 2019 running on Diamanti.
• SQLTest SQL Server Performance, Load, Stress and Unit Test Tool SQLTest is an easy-to-use tool to generate real-world workload for testing. It can be used on-premises as well as in the cloud.

And your stress01.sql can contain simple or complex code that causes CPU or memory pressure. With this, you can test the scalability of SQL Server for a given workload. You can record performance benchmark etc, Just an example of what stress01.sql can contain: cross join with the largest table in your database. SQLStress is a tool to stress test a Microsoft SQL Server installation. Its main purpose is to find infrastructure problems within an installation. It can also be used for hardware sizing, system tuning or benchmarking. The focus is on simplicity.

This article describes how to use the SQLIOSim utility to perform stress tests on disk subsystems to simulate SQL Server activity.

Original product version: SQL Server
Original KB number: 231619

Summary

For Microsoft SQL Server 2005, SQLIOSim was shipped as a separate download package. Starting with SQL Server 2008, SQLIOSim is included with the SQL Server product installation. When you install SQL Server, you find the SQLIOSim tool in the BINN folder of your SQL Server installation. It is recommended to use these updated versions of the tool to simulate the IO activity on the disk subsystem.

The SQLIOSim utility replaces the SQLIOStress utility. The SQLIOStress utility was formerly known as the SQL70IOStress utility.

This article also contains download information for the SQLIOSim utility.

Introduction

This article describes the SQLIOSim tool. You can use this tool to perform reliability and integrity tests on disk subsystems. These tests simulate read, write, checkpoint, backup, sort, and read-ahead activities for Microsoft SQL Server. However, if you have to perform benchmark tests and determine the I/O capacity of the storage system, you should use the Diskspd tool.

Overview

The SQLIOSim utility has been upgraded from the SQLIOStress utility. The SQLIOSim utility more accurately simulates the I/O patterns of Microsoft SQL Server.

For more information about SQL Server I/O patterns, see SQL Server I/O Basics, Chapter 2.

Note

To help maintain appropriate data integrity and security, we recommend that you perform stress tests of your I/O subsystem before you deploy SQL Server on new hardware. The SQLIOSim utility simulates the read patterns, the write patterns, and the problem identification techniques of SQL Server. To perform these tasks, the SQLIOSim utility simulates the user activity and the system activity of a SQL Server system. The SQLIOSim utility performs this simulation independent of the SQL Server engine.

The SQLIOSim utility does not guarantee or warrant data security or integrity. The utility was designed to provide baseline testing of a system environment. The SQLIOSim utility may expose potential data integrity issues.

For more information about logging and data storage, see Description of logging and data storage algorithms that extend data reliability in SQL Server.

The download package contains two executable files, SQLIOSim.com and SQLIOSim.exe. Both executable files provide identical simulation capabilities. SQLIOSim.com is a command-line tool that you can configure to run without user interaction. To do this, you can use command-line parameters, a configuration file, or a combination of both of these methods. SQLIOSim.exe is a graphical application that accepts no command-line parameters. However, SQLIOSim.exe does load default configuration data from configuration files.

SQLIOSim.com command-line parameters

SQLIOSim.com accepts a limited number of command-line parameters to control basic behavior. The configuration file for the SQLIOSim utility provides advanced behavior control. When command-line parameters and configuration file options overlap, the command-line parameters take precedence.

SQLIOSim configuration file

Sample configuration files for various tests can be downloaded from SQL Server support team’s github repo here.

You do not have to use a configuration file. If you do not use a configuration file, all parameters take default values except the data file location and the log file location. You must use one of the following methods to specify the data file location and the log file location:

• Use the command-line parameters in the SQLIOSim.com file.
• Use the Files and Configuration dialog box after you run the SQLIOSim.exe file.
• Use the Filex section of the configuration file.

Note

• If the name of the parameter indicates that the parameter is a ratio or a percentage, the value of the parameter is expressed as the percentage or the ratio, divided by 0.01. For example, the value of the CacheHitRatio parameter is 10 percent. This value is expressed as 1000 because 10, divided by 0.01, equals 1000. The maximum value of a percentage parameter is 10000.
• If the parameter type is numeric, and you assign a non-numeric value to the parameter, the SQLIOSim utility sets the parameter to 0.
• If the parameter type is Boolean, the valid values that you can assign to the parameter are true and false . Additionally, the values are case sensitive. The SQLIOSim utility ignores any invalid values.
• If a pair of parameters indicates a minimum value and a maximum value, the minimum value must not be larger than the maximum value. For example, the value of the MinIOChainLength parameter must not be larger than the value of the MaxIOChainLength parameter.
• If the parameter indicates a number of pages, the SQLIOSim utility checks the value that you assign to the parameter against the file that the SQLIOSim utility processes. The SQLIOSim utility performs this check to make sure that the number of pages does not exceed the file size.

CONFIG section

The SQLIOSim utility takes the values that you specify in the CONFIG section of the SQLIOSim configuration file to establish global testing behavior.

Sql Server Performance Tools

Filex section

The SQLIOSim utility is designed to allow for multiple file testings. The File x section is represented as [File1], [File2] for each file in the test.

RandomUser section

The SQLIOSim utility takes the values that you specify in the RandomUser section to simulate a SQL Server worker that is performing random query operations, such as Online Transaction Processing (OLTP) I/O patterns.

AuditUser section

The SQLIOSim utility takes the values that you specify in the AuditUser section to simulate DBCC activity to read and to audit the information about the page. Validation occurs even if the value of the UserCount parameter is set to 0.

Ms Sql Server Benchmark Stress Test Tools Pdf

ReadAheadUser section

The SQLIOSim utility takes the values that are specified in the ReadAheadUser section to simulate SQL Server read-ahead activity. SQL Server takes advantage of read-ahead activity to maximize asynchronous I/O capabilities and to limit query delays.

BulkUpdateUser section

The SQLIOSim utility takes the values that you specify in the BulkUpdateUser section to simulate bulk operations, such as SELECT...INTO operations and BULK INSERT operations.

ShrinkUser section

The SQLIOSim utility takes the values that you specify in the ShrinkUser section to simulate DBCC shrink operations. The SQLIOSim utility can also use the ShrinkUser section to make the file grow.

Configuration .ini file comments

The semicolon character (;) at the start of a line in the configuration .ini file causes the line to be treated as a single comment.

File creation

The SQLIOSim utility creates separate data files and log files to simulate the I/O patterns that SQL Server generates in its data file and in its log file. The SQLIOSim utility does not use the SQL Server engine to perform stress activity. Therefore, you can use the SQLIOSim utility to test a computer before you install SQL Server.

When you run the SQLIOSim utility, make sure that you specify the same file location that you use for your SQL Server database files. When you do this, the utility simulates the same I/O path as your SQL Server database.

You can enable the compress attribute or the encrypt attribute for the existing test files. You can also enable these attributes for the existing directory where the test files will be created. The corresponding options to enable these attributes are located in the Properties dialog box for a file or for a directory.

By default, the SQLIOSim utility creates test files that have the .mdx and.ldx file name extensions. Therefore, these files will not overwrite existing data and log files.

Warning

Do not specify the actual SQL Server database files for testing. The SQLIOSim utility will overwrite the data with random test patterns, and your actual SQL Server data will be lost.

SQLIOSim error log and handling

The SQLIOSim utility creates the error log file in one of the following locations:

• The location that you specify in the log startup parameter
• The location that you specify in the ErrorFile= line in the Sqliosim.cfg.ini file

The SQLIOSim.log.xml error log contains details about the execution. These details include error information. Review the log carefully for error information and for warning information.

Ms Sql Server Benchmark Stress Test Tools For Beginners

Note

If you experience an error in the SQLIOSim utility, we recommend that you ask your hardware manufacturer to help determine the root cause of the issue.

Multiple copies

The SQLIOSim utility accommodates multiple-file-level testing and multiple-user-level testing. The SQLIOSim utility does not require multiple invocations. However, the SQLIOStress utility requires multiple invocations. You can run multiple copies of the SQLIOSim utility if the following conditions are true:

• All copies reference unique testing files per instance of the utility.
• The MaxMemoryMB parameter of each instance provides for a non-overlapping memory region that is sufficient for each instance.

The sum of the MaxMemoryMB parameter for each instance must be less than or equal to the total physical memory. Some testing phases, such as checkpoint simulation, can be memory-intensive and may create out-of-memory conditions when you run multiple copies. If you experience out-of-memory errors, you can reduce the number of utility copies that are running.

Sample configuration files

In addition to the default Sqliosim.cfg.ini file, the package provides the following sample files.

Ms Sql Server Benchmark Stress Test Tools Download

References

Previously, I covered the basics of storage subsystem metrics and testing in my article Analyzing I/O Subsystem Performance for SQL Server, including an introduction of CrystalDiskMark 4.0. CrystalDiskMark was recently rewritten to use Microsoft DiskSpd for its testing, which makes it an even more valuable tool for your initial storage subsystem testing efforts. DiskSpd provides the functionality needed to generate a wide variety of disk request patterns, which can be very helpful in diagnosis and analysis of I/O performance issues with a lot more flexibility than older benchmark tools like SQLIO. It is extremely useful for synthetic storage subsystem testing when you want a greater level of control than that available in CrystalDiskMark.

Now, we are going to dive a little deeper into how to actually use Microsoft DiskSpd to test your storage subsystem without using CrystalDiskMark 4.0. In order to do this, you’ll need to download and unzip DiskSpd. To make things easier, I always copy the desired diskspd.exe executable file from the appropriate executable folder (amd64fre, armfre or x86fre) to a short, simple path like C:DiskSpd. In most cases you will want the 64-bit version of DiskSpd from the amd64fre folder.

Once you have the diskspd.exe executable file available, you will need to open a command prompt with administrative rights (by choosing “Run as Administrator”), and then navigate to the directory where you copied the diskspd.exe file.

Here are some of the command line parameters that you will want to start out with:

Table 1: Basic command line parameters for DiskSpd

You will also want to specify the test file location and the file name for the results at the end of the line. Here is an example command line:

This example command line will run a 30 second random I/O test using a 20GB test file located on the T: drive, with a 25% write and 75% read ratio, with an 8K block size. It will use eight worker threads, each with four outstanding I/Os and a write entropy value seed of 1GB. It will save the results of the test to a text file called DiskSpeedResults.txt. This is a pretty good set of parameters for a SQL Server OLTP workload.

Figure 1: Example command line for DiskSpd

Running the test starts with a default five second warm up time (before any measurements actually start), and then the actual test will run for the specified duration in seconds with a default cool down time of zero seconds. When the test finishes, DiskSpd will provide a description of the test and the detailed results. By default this will be a simple text summary in a text file using the file name that you specified, which will be in the same directory as the diskspd executable.

Here are what the results look like for this particular test run on my workstation.

Figure 2: Example DiskSpd test results

The first section of the results gives you the exact command line that was used for the test, then specifies all of the input parameters that were used for the test run (which include the default values that may not have been specified in the actual command line). Next, the test results are shown starting with the actual test time, thread count, and logical processor count. The CPU section shows the CPU utilization for each logical processor, including user and kernel time, for the test interval.

The more interesting part of the test results comes next. You get the total bytes, total I/Os, MB/second, I/O per second (IOPS), and your average latency in milliseconds. These results are broken out for each thread (four in our case), with separate sections in the results for Total IO, Read IO, and Write IO. The results for each thread should be very similar in most cases. Rather than initially focusing on the absolute values for each measurement, I like to compare the values when I run the same test on different logical drives, (after changing the location of the test file in the command line), which lets you compare the performance for each logical drive.

The last section of the test results is even more interesting. It shows a percentile analysis of the distribution of the latency test results starting from the minimum value in milliseconds going up to the maximum value in milliseconds, broken out for reads, writes, and total latency. The “nines” in the %-ile column refer to the number of nines, where 3-nines means 99.9, 4-nines means 99.99, etc. The reason why the values for the higher percentile rows are the same is because this test had a relatively low number of total operations. If you want to accurately characterize the higher percentiles, you will have to run a longer duration test that generates a higher number of separate I/O operations.

What you want to look for in these results is the point where the values make a large jump. For example, in this test we can see that 99% of the reads had a latency of 1.832 milliseconds or less.

Figure 3: Latency results distribution

As you can see, running DiskSpd is actually pretty simple once you understand what the basic parameters mean and how they are used. Not only can you run DiskSpd from an old-fashioned command line, you can also run it using PowerShell. DiskSpd also gives you a lot more detailed information than you get from SQLIO. The more complicated part of using DiskSpd is analyzing and interpreting the results, which is something I will cover in a future article.