WordType Designs™
Driven To Distractions©
The Sound of One Hand Clapping©

A rchive Date
[ 31-12-2004 ]
Category
[ Information Technologies ]
sub-Categoy
[ Microsoft ]
      [How To Use The SQL I/O Stress Utility
      Stress A Disk Subsystem Such as SQL Server

      This article was previously published under Q231619

        Article ID
        :
        231619
        Last Review
        :
        August 25, 2004
        Revision
        :
        1.0

      IN THIS TASK


      On this Page

        SUMMARY
        REFERENCES

      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 utility was formerly named SQL70IOStress, but it has been upgraded to handle both SQL Server 7.0 and SQL Server 2000 I/O patterns. It has also been renamed SQLIOStress.

      This article provides the download location to an updated version of the SQLIOStress utility. For more information on 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 additional 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 to prevent any unauthorized changes to the file.

      Note       Microsoft strongly recommends that you perform stress tests of your I/O subsystem before you deploy SQL Server on new hardware to help ensure 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, including large and varied scatter and gather I/O requests. The simulation imposes heavy data file activity that requires high transaction log activity as well.

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

      SQLIOStress 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 additional 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 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 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 in the files with random test patterns, and your actual SQL Server data will be lost.

      64-bit availability
      Presently, there is not a true 64-bit build of SQLIOStress. As a temporary measure, SQLIOStress has been updated to execute 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, refer 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 if necessary.

      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. Check the error message carefully because this is frequently an indication 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 is made up of this 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 result in 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 alteration. 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 ensure 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 ensure that the proper 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 ensure I/O stability.
        •
        Checkpoint simulates loading up hundreds or thousands of buffers in memory (depending on the memory available on the computer). SQLIOStress performs basic page sanity checks during the read in, and then it flushes the pages to disk in the same manner as 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 ensure data quality; however, there are too many to outline in this article. The utility makes all attempts to keep high levels of I/O posted while at the same time it aggressively checks 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, you should check your 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 additional 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


      back to the top

      APPLIES TO

        •
        Microsoft SQL Server 2000 Standard Edition
        •
        Microsoft SQL Server 7.0 Standard Edition
        Keywords: 
        kbhowtomaster kbdownload KB231619
        ]


Some pages may require Adobe Acrobat Reader™


Copyright and Fair Use Information: The contents of this web site is protected by international copyright laws and may not be reproduced in any form or manner whatsoever, if for the purpose of resale or solicitation of a donation. The essays included here, may be reproduced only if: 1)They are not altered in any way; 2) reproductions must be accompanied by this copyright page ; and 3) it is given freely and without charge.
Fair use: The fair use of copyrighted work, including such use by reproduction in copies or phonorecords or by any other means specified in above sections, for purposes such as criticism, comment, news reporting, teaching (including multiple copies for classroom use), scholarship, or research, is not an infringement of copyright. In determining whether the use made of a work in any particular case is fair use the factors to be considered include : (1) the purpose and character of the use, including whether the use is of a commercial nature or is for nonprofit educational purposes; (2) the nature of the copyrighted work; (3) the amount and substantiality of the portion used in relation to the copyrighted work as a whole, and; (4) the effect of the use upon the potential market value of the copyrighted work.
Home | About Narrative? |Contact
Copyright © 2025. All Rights Reserved
HAG122125 (1998 -2026)