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.
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.
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. .
Override the sqliosim.cfg.ini default configuration file. An error is returned if the file is not found.
Save the resulting configuration in the configuration file. This option can be used to create the initial configuration file.
Error log file name and path. The default is sqliosim.log.xml.
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.
Duration of the main run, excluding preparation and verification phases.
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.
The SQLIOSim utility takes the values that are specified in the CONFIG section of the SQLIOSim configuration file to establish global testing behavior.
Name of the XML type log file
The maximum is 64 CPUs.
Number of CPUs on the computer
Number of logical CPUs to create
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.
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.
Stops when the first error is encountered
Number of full test cycles to perform
A value of 0 indicates an infinite number of test cycles.
Duration of a test cycle in seconds, excluding the audit pass at the end of the cycle
Simulated cache hit ratio when the SQLIOSim utility reads from disk
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
SQLIOSim is designed to allow for multiple file testing. The FileX section is represented as [File1], [File2] … for each file in the test.
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.
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.
Maximum size in MB
A file cannot grow larger than the value that is specified for the MaxSize parameter.
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.
Indicates whether the file can be shrunk or extended
See the comment for the Increment parameter.
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.
Indicates whether a file contains user or transaction log data
You should define at least one log file.
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.
Number of random access threads that are executing at the same time
The value cannot exceed the following value:
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:
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.
Minimum region size in pages
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.
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.
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.
Maximum log record size in bytes
This value cannot exceed 64000. The value must be a multiple of the on-disk sector size.
The chance of an in-memory operation that leads to rollback so that the log is not written
Sleep time after each cycle in milliseconds
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.
Number of Audit threads
See the comment for the UserCount parameter in the RandomAccess section.
Apply the AuditDelay parameter after the number of BuffersValidated cycles is completed
Number of milliseconds to wait after each DelayAfterCycles operation
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.
Number of read-ahead threads
Minimum number of pages to read per cycle
The minimum value is 0. The maximum value is limited by system memory.
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.
Apply the RADelay parameter after the specified number of cycles has completed.
Number of milliseconds to wait after each DelayAfterCycles operation.
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.
Number of Bulk Update threads
Minimum number of pages to update per cycle
Maximum number of pages to update per cycle
Apply the BUDelay parameter after the specified number of cycles is completed
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
SQLIOSim(SQL Server Simulator Stress Testツール)
Microsoft have released their SQLIOSim Tool for stress testing IO subsystems.
I constantly receive following error:
12/20/06 17:26:21 6004 System Error: 0x8009000F
Error Text: Object already exists.
Description: Failed to create random hash Simulator_Main e:\yukon\sosbranch\sql\ntdbms\storeng\util\sqliosim\simulator_main.cpp 398
The Perth SQL Server User Group was lucky enough to have Paul Randal , Principal Lead Program Manager
I/O Stress Tool 集
One of the things that’s great about my job is the fact that I get to meet people who are a lot smarter
Modifying the “HBA Queue Depth” is a performance tuning tip for servers that are connected to Storage
I hit this error while i try executing this tool. Is something wrong with my convention?
Error Text: The parameter is incorrect.
Description: Unable to get volume name for mount point \\kc3070-9\kc108b1-sqliostress\
the command line : sqliosim.com -cfg Y:\sqliosim.cfg.ini -log Y:\sqliosim.xml -dir \\kc3070-9\kc108b1-sqliostress -size 100 -d 600
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
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.
'm trying to run SQLIOSIM on NAS cluster's NTFS shared folder. Here's the command line:
sqliosim.com -dir \\c16\vol1cifs -size 512 -d 600
But it repeatedly fails to create files during the setup due to following error:
Error: 0x80070057 Error Text: Description: Unable to get volume name for mount point \\c16\vol1cifs\
vol1cifs - NTFS shared folder, that can be opened and edited on the same client where SQLIOSIM is installed, without any problem. According to SQLIOSIM description it does supports UNC path.
What I'm doing wrong?
Thanks for help. Sam
I encounter the same UNC access issue. Have you resolved it? could you please share?
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