How Fast and Robust Is Your Storage? - Professional SQL Server 2005 Performance Tuning - page 455

SQLIOSim is the I/O stress test tool of preference for stress testing an I/O subsystem that's going to be
used with SQL Server.
UsingSQLIOSim
The first step is to download the tool from the Microsoft download center. The starting point for this
is to find the related KB article. Currently this is 231619 and is located at http://support.microsoft
.com/kb/231619 .
After downloading the appropriate msi and installing it to your favorite location (here:
c: \ tools \ sqliosim ) the next step is to figure out how to run it, and how to get it to test the disks
you want tested.
There are actually two executables in the download: sqliosim.exe which is the GUI version and
sqliosim.com , which is the console app version. You are going to start out using the GUI version so
you can get started quickly, but will then switch to using the console app as this is the version you will
run from command files in automated testing.
If you are running this on a machine running Windows Vista, then you will find that the GUI throws up
an error when you first start it. This is because SQLIOSim is trying to be helpful by scanning your PC for
local disks and attempting to create default test files in the root of each disk on first startup. Unfortunately
Vista doesn't like anyone creating files in the root of the C drive, so you will get two error messages about
creating c: \ sqliosim.mdx and sqliosim.ldx . You can either turn off UAC in Vista and restart your PC
or just ignore the errors and add additional files elsewhere on the C drive later.
After starting the GUI application for the first time, you are presented with the default configuration
screen which on my Vista laptop with C and D drives (and after UAC prevented SQLIOSim from creating
files on C) looks like Figure 12-19. The screen you see will vary from this depending on the OS you are
running and how many disks your system has.
The first thing you want to do is change the configuration to point to the disk you want to test. In this
example, this is the D drive, at a location called d: \ mssql \ data and d: \ mssql \ log .
To do that the additional default files specified on C and E had to be deleted, and the name and settings of
the default data and log file on the D drive had to be changed. The max size of the data file was changed
to 4096 and the increment was set to 0. Futhermore, the following was changed: The size of the log file
was set to 2048MB and autogrow was turned off by setting the max size to be the same as the size.
The increment for the Log was already 0. On your system, there might be some minor differences,
but the process will be similar.
One thing to note is that SQLIOSim uses the default file extensions mdx and ldx rather than the SQL
Server defaults of mdf and ldf . If you don't like these extensions, you can change them to anything
you like. One reason for leaving them as is would be that you can't then confuse files created by SQL
Server with files created by SQLIOSim, and if you're running SQLIOSim on a system with SQL Server
already installed, you won't be able to destroy the data in any database files by running SQLIOSim on the
same files.