Share via

SQLIOSim available for download

  • Article

 

There has already been a great deal of excitement over the release of SQLIOSim, and I know everyone is hungry for more details.

First the final cut of SQLIOSim is available for download at the Microsoft Download center.

https://download.microsoft.com/download/3/8/0/3804cb1c-a911-4d12-8525-e5780197e0b5/SQLIOSimX86.exe

https://download.microsoft.com/download/6/5/2/65286f65-bff2-42b8-b0c9-87f117855069/sqliosimX64.exe

https://download.microsoft.com/download/2/c/f/2cf8fb0a-a943-456b-9cf5-68ce426180a1/SQLIOSimIA64.exe

 Inside the package you'll find 2 executable files, SQLIOSim.exe and SQLIOSim.com. The simulation functionality of these executables is identical, however SQLIOSim.exe is for those of you who prefer things like menu bars and dialog boxes. SQLIOSim.com on the other hand provides the raw power of a command line interface, good for use in automated testing environments.

There are many components involved with reading and writing data to files. Starting from an application (SQL Server or SQLIOSim) the IO request is handed over to the Operating system via an API call. Once in the hands of the OS the request will travel through levels of filter drivers installed by things like antivirus software, backup utilities and finally find its way to a driver that will hand the actual data over to a disk controller, and eventually find its way to a disk or array of disks. There may be caching on the disks, and in the case of high end arrays there may also be logic to determine whether or not to service the request immediately or defer. If even one of these pieces get it wrong the results for your data would be disastrous.

Wouldn’t you rather know there is a problem before you entrust your data to such a complex process?

SQLIOSim is designed to generate exactly the same type and patterns of IO requests at a disk subsystem as SQL Server would, and verify the written data exactly as SQL Server would.

Users rarely want to see data that is physically adjacent to data that was just read, and there’s no telling what kind of query might show up from a novice user in a decision support scenario. SQLIOSim can replicate these kinds of requests, and via use of a config file you can tune just how random the requests should be.

Want to see how your system will behave when that scheduled a DBCC CHECKDB check runs? No problem, just add the AuditUser section to the config file.

Have bulk load jobs? Well just add the BulkUpdateUser section.

In the download package you’ll find a sqliosim.cfg.zip file that contains several sample configuration files that can be customized to fit your specific needs.

I’ve included documentation on command line parameters and information on the config file as well.

One important point is that this is a correctness and stress tool, not a performance measurement tool. Use this to verify your IO subsystem is functioning correctly under heavy loads, if you want to measure throughput use the SQLIO utility.

Enjoy!

SQLIOSim.com Command-Line Parameters

SQLIOSim.com accepts a limited number of command-line options to control basic behavior. Advanced behavior control is available through the SQLIOSim configuration file. When command-line parameters and configuration file options overlap, the command-line parameters take precedence. .

Parameter

Comment

-cfg <file>

Override the sqliosim.cfg.ini default configuration file. An error is returned if the file is not found.

-save <file>

Save the resulting configuration in the configuration file. This option can be used to create the initial configuration file.

-log <file>

Error log file name and path. The default is sqliosim.log.xml.

-dir <dir>

Location to create the data and log files. This command may be repeated multiple times. In most cases, this will be a drive root or a volume mount point. It can be a long path or a UNC path.

-d <seconds>

Duration of the main run, excluding preparation and verification phases.

-size <MB>

Initial data file size in MB. The file can grow up to two times the initial size. The size of the log file is calculated as half of the data file size, but no more than 50 MB.

CONFIG Section

The SQLIOSim utility takes the values that are specified in the CONFIG section of the SQLIOSim configuration file to establish global testing behavior.

Parameter

Default Value

Description

Comments

ErrorFile

sqliosim.log.xml

Name of the XML type log file

The maximum is 64 CPUs.

CPUCount

Number of CPUs on the computer

Number of logical CPUs to create

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 at the start of the SQLIOSim utility

Size of the buffer pool in MB

The value cannot exceed the total amount of physical memory on the computer.

StopOnError

true

Stops when the first error is encountered

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 disk

MaxOutstandingIO

0

Maximum number of outstanding I/O operations allowed process-wide

The value cannot exceed 140000. A value of 0 means that no limit exists, up to approximately 140000.

TargetIODuration

100

Duration of I/O operations in milliseconds targeted by throttling

If the average I/O duration exceeds the target I/O duration, the number of outstanding I/O operations is throttled to decrease the load and 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. Long I/O warnings can be expected.

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 buffering. To fully test a server, use both FILE_FLAG_NO_BUFFERING == true and FILE_FLAG_NO_BUFFERING == false to execute separate tests.

WriteThrough

true

Use the FILE_FLAG_WRITE_THROUGH option

SQL Server opens database files by using FILE_FLAG_WRITE_THROUGH == true. Some utilities and services, such as Analysis Services, use buffering. To fully test a server, use both FILE_FLAG_WRITE_THROUGH == true and FILE_FLAG_WRITE_THROUGH == false to execute separate tests.

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 a large part of I/O requests.

ForceReadAhead

true

Perform a read-ahead operation even if the data is already read

The read is issued 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 streams specified in the FileX FileName entry are truncated in the file. If the default stream is specified, all streams are deleted.

StampFiles

false

Expand file by stamping zeros

This process may take a long time if the file is very large. If StampFiles=false, the file is extended 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, zero stamping is performed. 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

SQLIOSim is designed to allow for multiple file testing. The FileX section is represented as [File1], [File2] … for each file in the test.

Parameter

Default Value

Description

Comments

FileName

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 Streams are used by DBCC operations in SQL Server 2005. Stream tests are a recommended practice.

InitialSize

Initial size in MB

If the existing file is larger than the value that is specified for the InitialSize parameter, the file is not shrunk. If the existing file is smaller, the existing file is expanded.

MaxSize

Maximum size in MB

A file cannot grow larger than the value that is specified for the MaxSize parameter.

Increment

0

Size in MB of the increment by which the file is grown or shrunk. For more information, see the "ShrinkUser section" part of this article.

The Increment parameter is adjusted at startup so that the following situation is established:

Increment * MaxExtents < MaxMemoryMB / NumberOfDataFiles

If the result is 0, the file is set as non-shrinkable.

Shrinkable

false

Indicates whether the file can be shrunk or extended

See the comment for the Increment parameter.

Sparse

false

Indicates whether the Sparse attribute should be set on the files

For existing files, the Sparse attribute is not cleared when you set the Sparse attribute to false.SQL Server 2005 uses sparse files to support snapshot databases in addition to the secondary DBCC streams.We recommend that you enable both the sparse file and the streams, and then perform a test pass.

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 are specified 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.

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 random access users should not be created. A value of -1 means that the automatic configuration of the following value should be used:

min(CPUCount*2, 8)

Note

  • A SQL Server system may have thousands of sessions. Most of the sessions do not have active requests. Use count(*) in queries against sys.dm_exec_requests as a baseline for establishing this test parameter value.
  • CPUCount here refers to the value of the CPUCount parameter in the CONFIG section.
  • min(CPUCount*2, 8) results in the smaller value 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, and the length is a random value between the MinIOChainLength parameter and the MaxIOChainLength parameter.

MinIOChainLength

1

Minimum region size in pages

MaxIOChainLength

100

Maximum region size in pages

SQL Server 2000 and 2005 can read ahead up to 1024 pages in Enterprise Edition.The minimum value is 0. The maximum value is limited by system memory.Random user activity generally results in small scanning operations. Use the ReadAhead user 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 should be either a multiple of the on-disk sector size or a size that fits evenly into the 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 of an in-memory operation that leads to rollback so that the log is not written

SleepAfter

5

Sleep time after each cycle in milliseconds

AuditUser Section

The SQLIOSim utility takes the values that are specified 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.

Parameter

Default Value

Description

Comments

UserCount

2

Number of Audit threads

See the comment for the UserCount parameter in the RandomAccess section.

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.

Parameter

Default Value

Description

Comments

UserCount

2

Number of read-ahead threads

See the comment for the UserCount parameter in the RandomAccess section.

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 1024 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 and read-ahead sizes.

DelayAfterCycles

2

Apply the RADelay parameter after the specified number of cycles has completed.

RADelay

200

Number of milliseconds to wait after each DelayAfterCycles operation.

BulkUpdateUser Section

The SQLIOSim utility takes the values that are specified in the BulkUpdateUser section to simulate bulk operations, such as select into operations and bulk insert operations.

Parameter

Default Value

Description

Comments

UserCount

-1

Number of Bulk Update threads

See the comment for the UserCount parameter in the RandomAccess section.

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

Comments

  • Anonymous
    October 07, 2006
    Only just seen this via the SQL Server Storage blog here http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx

  • Anonymous
    October 09, 2006
    SQLIOSim(SQL Server Simulator Stress Testツール)

  • Anonymous
    November 03, 2006
    Microsoft have released their SQLIOSim Tool for stress testing IO subsystems.

  • Anonymous
    December 20, 2006
    The comment has been removed

    • Anonymous
      August 14, 2017
      The comment has been removed

  • Anonymous
    May 12, 2007
    The Perth SQL Server User Group was lucky enough to have Paul Randal , Principal Lead Program Manager

  • Anonymous
    June 28, 2007
    I/O Stress Tool 集

  • Anonymous
    June 28, 2007
    One of the things that’s great about my job is the fact that I get to meet people who are a lot smarter

  • Anonymous
    January 11, 2009
    Modifying the “HBA Queue Depth” is a performance tuning tip for servers that are connected to Storage

  • Anonymous
    January 13, 2010
    The comment has been removed

  • Anonymous
    October 06, 2010
    Hi, Does anyone know why I am not getting any values for my reads.  I am using the default config files. Reads = 0, Scatter Reads = 0, Writes = 5464, Gather Writes = 0, Total IO Time (ms) = 628954 Thank you, Christian

  • Anonymous
    November 04, 2010
    Hi Christan, Because the 2nd part of the test is simulating the write process to the log file. ;-) You should get read data on the 1st part of the test. Cheers Andy

  • Anonymous
    September 04, 2014
    The comment has been removed

  • Anonymous
    September 18, 2014
    Hi Samuel, I encounter the same UNC access issue. Have you resolved it? could you please share? Thanks,

  • Anonymous
    July 15, 2015
    Thanks for this Article with so much detail information and the ongoing work on the SQLIOSim Tool. The Tool works fine even in a Virtualization environment - whats still important to design a well working server. .. but its still not an easy solution, for someone that dosen´t does this every day and a german translation would be fine. Only can find this howto setup server for well sql performance:  technet.servermeile.com/grundlagen-der-raid-konfiguration for

  • Anonymous
    May 22, 2016
    Major online search engine such as Google made use of precise search method to offer specific search engine result to the web users.

  • Anonymous
    May 27, 2016
    I am curious to find out what blog platform you're working with?I'm having some smll security problems with my latest blog and I would ljke to find something more risk-free.Do you have any recommendations?