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:
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
SQLIOSim utility, x64-based version
SQLIOSim utility, Itanium-based version
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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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.
Collapse this tableExpand this table
| Parameter | Comment |
| -cfg file | Override the Sqliosim.cfg.ini default configuration file.
The SQLIOSim utility returns an error if the utility cannot find the file. |
| -save file | Save the resulting configuration in the configuration
file. You can use this option to create the initial configuration file. |
| -log file | Specify the error log file name and the error log file
path. The default file name is Sqliosim.log.xml. |
| -dir dir | Set the location to create the data (.mdf) file and the
log (.ldf) file. You can run this command multiple times. In most cases, this
location is a drive root or a volume mount point. This location can be a long
path or a UNC path. |
| -d seconds | Set the duration of the main run. This value excludes the
preparation phase and the verification phase. |
| -size MB | Set the initial size of the data file in megabytes (MB).
The file can grow up to two times the initial size. The size of the log file is
calculated as half the size of the data file. However, the log file cannot be
larger than 50 MB. |
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 Filex 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.
Collapse this tableExpand this table
| Parameter | Default value | Description | Comments |
| ErrorFile | sqliosim.log.xml | Name of the XML type log file | |
| CPUCount | Number of CPUs on the computer | Number of logical
CPUs to create | The maximum is 64 CPUs. |
| Affinity | 0 | Physical CPU affinity mask to apply for logical
CPUs | The affinity mask should be within the active CPU mask. A value of
0 means that all available CPUs will be used. |
| MaxMemoryMB | Available physical memory when the SQLIOSim utility
starts | Size of the buffer pool in MB | The value cannot exceed
the total amount of physical memory on the computer. |
| StopOnError | true | Stops the simulation when the first error
occurs | |
| TestCycles | 1 | Number of full test cycles to perform | A value of 0 indicates an infinite number of test cycles. |
| TestCycleDuration | 300 | Duration of a test cycle in seconds, excluding the audit
pass at the end of the cycle | |
| CacheHitRatio | 1000 | Simulated cache hit ratio when the SQLIOSim utility reads
from the disk | |
| MaxOutstandingIO | 0 | Maximum number of outstanding I/O operations that are
allowed process-wide | The value cannot exceed 140000. A value of 0 means that up to approximately 140,000 I/O operations are
allowed. This is the limit of the utility. |
| TargetIODuration | 100 | Duration of I/O operations, in milliseconds, that are
targeted by throttling | If the average I/O duration exceeds the target
I/O duration, the SQLIOSim utility throttles the number of outstanding I/O
operations to decrease the load and to improve I/O completion time. |
| AllowIOBursts | true | Allow for turning off throttling to post many I/O
requests | I/O bursts are enabled during the initial update, initial
checkpoint, and final checkpoint passes at the end of test cycles. The MaxOutstandingIO parameter is still honored. You can expect long I/O
warnings. |
| NoBuffering | true | Use the FILE_FLAG_NO_BUFFERING option | SQL Server opens database files by using FILE_FLAG_NO_BUFFERING == true. Some utilities and services, such as Analysis Services, use FILE_FLAG_NO_BUFFERING == false. To fully test a server, execute one test for each setting. |
| WriteThrough | true | Use the FILE_FLAG_WRITE_THROUGH option | SQL Server
opens database files by using FILE_FLAG_WRITE_THROUGH == true. However, some
utilities and services open the database files by using FILE_FLAG_WRITE_THROUGH
== false. For example, SQL Server Analysis Services opens the database files by
using FILE_FLAG_WRITE_THROUGH == false. To fully test a server, execute one
test for each setting. |
| ScatterGather | true | Use ReadScatter/WriteGather APIs | If this
parameter is set to true, the NoBuffering parameter is also set to true.
SQL Server uses scatter/gather I/Os for most I/O
requests. |
| ForceReadAhead | true | Perform a read-ahead operation even if the data is
already read | The SQLIOSim utility issues the read command even if the
data page is already in the buffer pool.
Microsoft SQL Server Support
has successfully used the true setting to expose I/O problems. |
| DeleteFilesAtStartup | true | Delete files at startup if files exist | A file may
contain multiple data streams. Only streams that are specified in the
Filex FileName entry are truncated in the file. If
the default stream is specified, all streams are deleted. |
| DeleteFilesAtShutdown | false | Delete files after the test is finished | A file
may contain multiple data streams. Only data streams that you specify in the
Filex FileName entry are truncated in the file. If
the default data stream is specified, the SQLIOSim utility deletes all data
streams. |
| StampFiles | false | Expand the file by stamping zeros | This process
may take a long time if the file is very large. If you set this parameter to false, the SQLIOSim utility extends the file by setting a valid data
marker.
SQL Server 2005 uses the instant file initialization feature
for data files. If the data file is a log file, or if instant file
initialization is not enabled, SQL Server performs zero stamping. Versions of
SQL Server earlier than SQL Server 2000 always perform zero
stamping.
You should switch the value of the StampFiles parameter during testing to make sure that both instant file
initialization and zero stamping are operating correctly. |
Filex section
The SQLIOSim utility is designed to allow for multiple file
testing. The File
x section is represented as
[File1], [File2] for each file in the test.
Collapse this tableExpand this table
| Parameter | Default value | Description | Comments |
| FileName | No default value | File
name and path | The FileName parameter can be a long path or a UNC path. It can also include a
secondary stream name and type. For example, the FileName parameter may be set to file.mdf:stream2.
Note In SQL Server 2005, DBCC operations use streams. We recommend
that you perform stream tests. |
| InitialSize | No default
value | Initial size in MB | If the existing file is
larger than the value that is specified for the InitialSize parameter, the SQLIOSim utility does not shrink the existing
file. If the existing file is smaller, the SQLIOSim utility expands the
existing file. |
| MaxSize | No default
value | Maximum size in MB | A file cannot grow
larger than the value that you specify for the MaxSize parameter. |
| Increment | 0 | Size in MB of the increment by which the file grows or
shrinks. For more information, see the "ShrinkUser section" part of this
article. | The SQLIOSim utility adjusts the Increment parameter at startup so that the following situation is
established: Increment * MaxExtents < MaxMemoryMB / NumberOfDataFiles If the result is 0, the SQLIOSim utility sets the file as
non-shrinkable. |
| Shrinkable | false | Indicates whether the file can be shrunk or
expanded | If you set the Increment parameter to 0, you set the file to be non-shrinkable. In this case, you must
set the Shrinkable parameter to false. If you set the Increment parameter to a value other than 0, you set the file to be shrinkable. In this case, you must set
the Shrinkable parameter to true. |
| Sparse | false | Indicates whether the Sparse attribute should be set on the files | For existing files,
the SQLIOSim utility does not clear the Sparse attribute when you set the Sparse parameter to false.
SQL Server 2005 uses sparse files to support snapshot
databases and the secondary DBCC streams.
We recommend that you enable
both the sparse file and the streams, and then perform a test pass.
Note If you set Sparse = true for the file settings, do not specify NoBuffering = false in the config section. If you use these two conflicting combinations, you may receive an error that resembles the following from the tool:
Error:-=====Error: 0x80070467
Error Text: While accessing the hard disk, a disk operation failed even after retries.
Description: Buffer validation failed on C:\SQLIOSim.mdx Page: 28097 |
| LogFile | false | Indicates whether a file contains user or transaction log
data | You should define at least one log file. |
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.
Collapse this tableExpand this table
| Parameter | Default value | Description | Comments |
| UserCount | -1 | Number of random access threads that are executing at the
same time | The value cannot exceed the following value: CPUCount*1023-100 The total number of all users also cannot exceed
this value. A value of 0 means that you cannot create random access users. A value of -1 means that you must use the automatic configuration of the
following value: min(CPUCount*2, 8) NoteA SQL Server system may have thousands of sessions. Most of the
sessions do not have active requests. Use the count(*) function in queries against the sys.dm_exec_requests dynamic management view (DMV) as a baseline for establishing this
test parameter value.
CPUCount here refers to the value of the CPUCount parameter in the CONFIG section.
The min(CPUCount*2, 8) value results in the smaller of the values between CPUCount*2 and 8. |
| JumpToNewRegionPercentage | 500 | The chance of a jump to a new region of the
file | The start of the region is randomly selected. The size of the
region is a random value between the value of the MinIOChainLength parameter and the value of the MaxIOChainLength parameter. |
| MinIOChainLength | 1 | Minimum region size in pages | |
| MaxIOChainLength | 100 | Maximum region size in pages | SQL Server 2005
Enterprise Edition and SQL Server 2000 Enterprise Edition can read ahead up to
1,024 pages.
The minimum value is 0. The maximum value is limited by system
memory.
Typically, random user activity causes small scanning
operations to occur. Use the values that are specified in the ReadAheadUser
section to simulate larger scanning operations. |
| RandomUserReadWriteRatio | 9000 | Percentage of pages to be updated | A random-length
chain is selected in the region and may be read. This parameter defines the
percentage of the pages to be updated and written to disk. |
| MinLogPerBuffer | 64 | Minimum log record size in bytes | The value must
be either a multiple of the on-disk sector size or a size that fits evenly into
the on-disk sector size. |
| MaxLogPerBuffer | 8192 | Maximum log record size in bytes | This value
cannot exceed 64000. The value must be a multiple of the on-disk sector size. |
| RollbackChance | 100 | The chance that an in-memory operation will occur that
causes a rollback operation to occur. | When this rollback operation
occurs, SQL Server does not write to the log file. |
| SleepAfter | 5 | Sleep time after each cycle, in
milliseconds | |
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.
Collapse this tableExpand this table
| Parameter | Default value | Description | Comments |
| UserCount | 2 | Number of Audit threads | The value cannot exceed
the following value: CPUCount*1023-100 The total number of all users also cannot exceed
this value. A value of 0 means that you cannot create random access users. A value of -1 means that you must use the automatic configuration of the
following value: min(CPUCount*2, 8) NoteA SQL Server system may have thousands of sessions. Most of the
sessions do not have active requests. Use the count(*) function in queries against the sys.dm_exec_requests DMV as a baseline for establishing this test parameter
value.
CPUCount here refers to the value of the CPUCount parameter in the CONFIG section.
The min(CPUCount*2, 8) value results in the smaller of the values between CPUCount*2 and 8. |
| BuffersValidated | 64 | | |
| DelayAfterCycles | 2 | Apply the AuditDelay parameter after the number of BuffersValidated cycles is completed | |
| AuditDelay | 200 | Number of milliseconds to wait after each DelayAfterCycles operation | |
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.
Collapse this tableExpand this table
| Parameter | Default value | Description | Comments |
| UserCount | 2 | Number of read-ahead threads | The value cannot
exceed the following value: CPUCount*1023-100 The total number of all users also cannot exceed
this value. A value of 0 means that you cannot create random access users. A value of -1 means that you must use the automatic configuration of the
following value: min(CPUCount*2, 8) NoteA SQL Server system may have thousands of sessions. Most of the
sessions do not have active requests. Use the count(*) function in queries against the sys.dm_exec_requests DMV as a baseline for establishing this test parameter
value.
CPUCount here refers to the value of the CPUCount parameter in the CONFIG section.
The min(CPUCount*2, 8) value results in the smaller of the values between CPUCount*2 and 8. |
| BuffersRAMin | 32 | Minimum number of pages to read per cycle | The
minimum value is 0. The maximum value is limited by system memory. |
| BuffersRAMax | 64 | Maximum number of pages to read per cycle | SQL
Server Enterprise editions can read up to 1,024 pages in a single request. If
you install SQL Server on a computer that has lots of CPU, memory, and disk
resources, we recommend that you increase the file size and the read-ahead
size. |
| DelayAfterCycles | 2 | Apply the RADelay parameter after the specified number of cycles is
completed | |
| RADelay | 200 | Number of milliseconds to wait after each DelayAfterCycles operation | |
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.
Collapse this tableExpand this table
| Parameter | Default value | Description | Comments |
| UserCount | -1 | Number of BULK UPDATE threads | The value cannot
exceed the following value: CPUCount*1023-100 A value of -1 means that you must use the automatic configuration of the
following value:min(CPUCount*2, 8) NoteA SQL Server system may have thousands of sessions. Most of the
sessions do not have active requests. Use the count(*) function in queries against the sys.dm_exec_requests DMV as a baseline for establishing this test parameter
value.
CPUCount here refers to the value of the CPUCount parameter in the CONFIG section.
The min(CPUCount*2, 8) value results in the smaller of the values between CPUCount*2 and 8. |
| BuffersBUMin | 64 | Minimum number of pages to update per cycle | |
| BuffersBUMax | 128 | Maximum number of pages to update per cycle | The
minimum value is 0. The maximum value is limited by system memory. |
| DelayAfterCycles | 2 | Apply the BUDelay parameter after the specified number of cycles is
completed | |
| BUDelay | 10 | Number of milliseconds to wait after each DelayAfterCycles operation | |
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.
Collapse this tableExpand this table
| Parameter | Default value | Description |
| MinShrinkInterval | 120 | Minimum interval between shrink operations, in
seconds |
| MaxShrinkInterval | 600 | Maximum interval between shrink operations, in
seconds |
| MinExtends | 1 | Minimum number of increments by which the SQLIOSim
utility will grow or shrink the file |
| MaxExtends | 20 | Maximum number of increments by which the SQLIOSim
utility will grow or shrink the file |
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.
Collapse this tableExpand this table
| Sample file | Description | Parameters that differ from the default configuration file |
| Sqliosim.hwcache.cfg.ini | Minimize reads
Files are made small to keep them fully in memory
No sequential
reads | For the AuditUser section and for the ReadAheadUser
section:
CacheHitRatio=10000
UserCount=0 |
| Sqliosim.nothrottle.cfg.ini | Remove I/O
throttling
Minimize the time to wait to increase I/O volume | TargetIODuration=1000000
AuditDelay=10
RADelay=10 |
| Sqliosim.seqwrites.cfg.ini | Minimize
reads
Files are made small to keep them fully in memory
Files
are made non-shrinkable
No sequential reads
No random
access
Bulk update in big chunks without delays | Shrinkable=FALSE
For the AuditUser section, for the ReadAheadUser
section, and for the RandomUser section:
CacheHitRatio=10000
ForceReadAhead=FALSE
BuffersBUMin=600
BuffersBUMax=1000
BUDelay=1
UserCount=0 |
| Sqliosim.sparse.cfg.ini | Use only 32 MB of
memory
Make target I/O duration large enough to enable many
outstanding I/O requests
Disable scatter/gather APIs to issue separate
I/O requests for every 8 KB page
Create a 1 GB non-shrinkable
file
Create a 1 GB non-shrinkable secondary sparse stream in the
file | MaxMemoryMB=32
TestCycles=3
TestCycleDuration=600
TargetIODuration=10000
UseScatterGather=FALSE
[File1]
FileName=sqliosim.mdx
InitialSize=1000
MaxSize=1000
Increment=10
Shrinkable=FALSE
LogFile=FALSE
Sparse=FALSE
[File2]
FileName=sqliosim.ldx
InitialSize=50
MaxSize=50
Increment=0
Shrinkable=FALSE
LogFile=TRUE
Sparse=FALSE
[File3]
FileName=sqliosim.mdx:replica
InitialSize=1000
MaxSize=1000
Increment=10
Shrinkable=FALSE
LogFile=FALSE
Sparse=TRUE |
Back to the top
