Microsoft KB Archive/231619

= How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem =

Article ID: 231619

Article Last Modified on 3/16/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q231619



SUMMARY
The SQLIOSim utility replaces the SQLIOStress utility. The SQLIOStress utility was formerly named the SQL70IOStress utility.

This article also contains download information for the SQLIOSim utility.



INTRODUCTION
This article describes the SQLIOSim utility. You can use this utility to perform tests on disk subsystems. These tests simulate read, write, checkpoint, backup, sort, and read-ahead activities for Microsoft SQL Server 2005.



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 2005, of SQL Server 2000, and of SQL Server 7.0. The I/O patterns of these versions of SQL Server resemble one another. The SQLIOStress utility has been used to test SQL Server 2005 I/O requirements for many years.

For more information about SQL Server I/O patterns, see Chapter 2 of Microsoft SQL Server I/O Basics, by Bob Dorr, Microsoft SQL Server Escalation. To obtain this document, visit the following Microsoft Web site:

http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

The SQLIOSim utility is offered for Itanium-based, x86-based, and x64-based systems. Use the following links to download the appropriate package for your system.

SQLIOSim utility, x86-based version
Download the SQLIOSimX86.exe package now.

SQLIOSim utility, x64-based version
Download the SqliosimX64.exe package now.

SQLIOSim utility, Itanium-based version
Download the SQLIOSimIA64.exe package now.

Release Date: 19-Oct-2006

For more information about how to download Microsoft support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to obtain Microsoft support files from online services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.

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, click the following article number to view the article in the Microsoft Knowledge Base:

230785 SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability

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
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 File  section of the configuration file.

Notes
 * 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.

File section
The SQLIOSim utility is designed to allow for multiple file testing. The File 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.

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.

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 very 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.

