How to use the SQLIOStress utility to stress a disk subsystem such as SQL Server (231619)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup
This article was previously published under Q231619

SUMMARY

This article outlines the SQLIOStress utility. You can use this utility to perform stress tests on disk subsystems to simulate Microsoft SQL Server 2000 and Microsoft SQL Server 7.0 read, write, checkpoint, backup, sort, and read ahead activities.

The SQLIOStress utility was formerly called the SQL70IOStress utility. The SQLIOSTRESS utility has been upgraded to handle SQL Server 7.0, SQL Server 2000, and SQL Server 2005 I/O patterns, all of which are similar to one another. The SQLIOStress utility has been testing for SQL Server 2005 I/O needs for several years.

This article contains the download location to an updated version of the SQLIOStress utility. For more information about this update, see the Readme file that is provided in the package.

The following file is available for download from the Microsoft Download Center:

DownloadDownload the SQLIOStress.exe package now.

Release Date: JUN-11-2004

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 We recommend that you perform stress tests of your I/O subsystem before you deploy SQL Server on new hardware to help maintain appropriate data integrity and security.

Overview

The SQLIOStress utility simulates the read and the write patterns of a heavily loaded server that is running SQL Server, and it uses a Write Ahead Logging (WAL) protocol that is similar to the protocol that SQL Server uses.

These patterns include heavy page insert/split simulations, inserts, updates, checkpoint stress scenarios, read ahead, sorts, hashes, and backup scan activities that include large and varied scatter and gather I/O requests. The simulation also imposes heavy data file activity that requires high transaction log activity.

The utility has over 150 separate validation and verification checks. The validation and verification checks include a scan for torn page writes, zero image comparison, checksum, LSN sequences, lost writes, stale reads, and many others.

The SQLIOStress utility does not guarantee or warrant data security or integrity. It was designed to provide base line testing of a hardware environment, and it may expose potential data integrity issues.

For more information about logging and data storage in SQL Server 7.0 and in SQL Server 2000, click the following article number to view the article in the Microsoft Knowledge Base:

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

File creation

SQLIOStress creates separate data and log files to simulate the I/O patterns that SQL Server will generate to its data file (.mdf) and its log file (.ldf). SQLIOStress does not use the SQL Server engine to perform the stress activity so that it can be used to exercise a computer before you install SQL Server.

When you run the SQLIOStress utility, make sure that you specify the same file location that you use for your SQL Server database files so that the utility exercises the same I/O path as your SQL Server database.

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

64-bit availability

Currently, there is no true 64-bit build of SQLIOStress. As a temporary measure, SQLIOStress has been updated to run under the WOW64 environment. However, WOW64 does not support scatter and gather I/O operations. When SQLIOStress detects that it is running under the WOW64 environment, the -K option is enabled to revert to standard ReadFile and WriteFile calls. SQLIOStress will not be able to drive the I/O rates as high when it is run in this configuration.

Execution time

The SQLIOStress usage parameter -? contains the formula for estimating execution time. The formula is based on timings from a Compaq DL580, 1.6 GHz, 3GB RAM, Smart SCSI 5i, 50% Read and 50% Write Cache enabled system that is performing 32 iterations with a file size of 3,072 megabytes (MB). Your system and drive speed may vary.

This estimate is logged to the SQLIOStress error log at startup, and a running estimate is updated at the start of each iteration.

SQLIOStress error log and handling

The SQLIOStress error log file is generated in the same folder as the path specified for the -F parameter unless the -U parameter is used to override the location. The SQLIOStress error log contains details about the execution, including error information. Review it carefully for ERROR and WARNING information. If you have any questions about the errors generated, see to the -? usage output or contact Microsoft SQL Server support.

The -U parameter must appear in the command line after the -F parameter to work correctly. This can be helpful on systems that are experiencing errors. If the I/O path of the .mdf file is suspect, the log may not be available to record the correct failure information. Use the -U parameter to locate the SQLIOStress error log at a stable I/O path location.

Note When SQLIOStress encounters an error, it immediately tries to shut down and to display the error log in a Notepad file.

Error 87 is generally logged during a DeviceIOControl action. Examine the error message carefully because this frequently indicates that the Diskperf.exe program has not been used to enable disk statistics. It does not disable any utility functionality, and it can be safely ignored. If you want to see more performance statistics, enable the disk performance counters, and then run SQLIOStress again.

The name of the log file consists of the following sequence:
Server_date_time_log.txt
Note If SQLIOStress produces an error, it is best to involve your hardware manufacture to help determine the root cause of the issue.

Multiple copies

You can run multiple copies of SQLIOStress as long as they reference unique testing files per instance of the utility. Some testing phases, such as checkpoint simulation, can be very memory-intensive and may create out of memory conditions when multiple copies are run. If you experience out-of-memory errors reduce the number of utility copies that are running.

Large files

SQLIOStress currently limits the file size to less than 4 gigabytes (GB). Future versions of SQLIOStress will remove this restriction.

Testing basics

Here is a list of some of the testing activities that occur:
  • Expand the file and the zero file operations to simulate SQL Server file creation and change. These mimic the SQL Server CREATE DATABASE and auto grow I/O patterns.
  • Use a strict WAL protocol to write data patterns on the pages.
  • Each page has a page header and is checked frequently to make sure that a correct page number, log sequence number (LSN) and a checksum like operation is performed on the pattern data.
  • Aggressive stale read/lost write checks read back the data page that was just written and perform page sanity checks (-H). The -H activity is always forced on the second iteration and on every fifth iteration.
  • Aggressive log checks read back the log record that was just written and perform sanity checks.
  • Log records are written as 512 byte blocks to align with sector size.
  • The validation phase reads every page in the test and does aggressive page sanity checks and uses the log records to make sure that the correct LSN and pattern was saved on page for the latest write operation.
  • Read ahead simulation reads random pages in patterns that simulate SQL Server read ahead logic. SQLIOStress also performs basic page sanity checks.
  • Stale read pass performs multiple reads and writes of the same page and also performs page sanity checks to make sure I/O stability.
  • Checkpoint simulates loading up hundreds or thousands of buffers in memory, depending on the memory available on the computer. The SQLIOStress utility performs basic page sanity checks during the read in, and then it flushes the pages to disk, just like a checkpoint does for SQL Server.
  • The random generator randomly reads or writes from 8 kilobytes (KB) to 1 megabyte (MB) in a secondary file to make sure that various block sizes are tested and exercised on the controller.
Note The disk space on the destination drive of the data file (.mdf) must contain an extra 128 MB for the random generator activity. The file named in the -F parameter plus a file name extension of ".random" is created by the I/O stress simulator and is 128 MB. It simulates random I/O patterns throughout the duration of the testing. This better mimics SQL Server sort and hash activities.

There are other actions that occur to help maintain data quality; however, there are too many to outline in this article. The utility tries to keep high levels of I/O posted while at the same time aggressively checking the data quality.

Errors

The SQLIOStress parameter -? outlines several of the common errors in more detail.

Sample output

The following section displays an excerpt from the SQLIOStress utility output during a power-outage test with a caching IDE drive under Microsoft Windows NT 4.0 Service Pack 4. Notice that the torn write shows the older LSN for the last three sectors of the 8 KB page.

Note The actual sector data is removed for clarity; the utility also dumps the sector data.
	04/20/99 16:28:26 00000154	ERROR: LSN for page 3292 is out of sequence.  Expected: 16447 and found 10049 in sector 15.  Probably a torn page.
04/20/99 16:28:26 00000154	
04/20/99 16:28:26 00000154	---------------------------------------------------------------
04/20/99 16:28:26 00000154	ERROR: Did not find expected pattern in file for page 3292.
04/20/99 16:28:26 00000154	Bytes read = 8192
04/20/99 16:28:26 00000154	Potential torn write
04/20/99 16:28:26 00000154	---------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 0      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 1      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 2      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 3      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 4      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 5      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 6      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 7      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 8      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 9      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 10      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 11      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 12      LSN: 16447
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 13      LSN: 10049
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 14      LSN: 10049
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	Sector: 15      LSN: 10049
04/20/99 16:28:26 00000154	---------------------------------------------------------------------------
04/20/99 16:28:26 00000154	
04/20/99 16:28:26 00000154	Current LSN is 16447

Troubleshooting

If you have any concerns about the stability of your data because of the caching mechanisms of the drive or the controller, turn on the Torn Page Detection database option, and then contact the manufacturers for the utilities that you must use to turn off read or write caching mechanisms.

Common errors 605, 823, 644, 3414

SQL Server performs many online data integrity checks. These are generally seen as errors 605, 823, 644, and 3414 but are not limited to these errors. If you are experiencing data integrity errors, check the system event logs, run this utility, and then contact SQL Server support for extended evaluation.

REFERENCES

For information about the parameters, run SQLIOStress with the ? parameter to obtain Help.

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

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

86903 SQL Server and caching disk controllers

234656 Using disk drive caching with SQL Server

46091 Using hard disk controller caching with SQL Server

826433 Additional SQL Server diagnostics added to detect unreported I/O problems

Modification Type:MajorLast Reviewed:12/13/2005
Keywords:kbdownload kbHOWTOmaster KB231619 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.