Welcome to Julian Kuiters Friday, July 20 2018 @ 04:39 PM AEST

SQLIOSim Tool Released

  • Contributed by:
SQL Server 2005

Microsoft have released their SQLIOSim Tool for stress testing IO subsystems. Different from the SQLIO tool which is tests IO throughput.

The tool is designed to simulate the effects SQL Server would have on disk subsystems. It includes options for testing multiple files; random OLTP style queries; read aheads; bulk inserts; checkpoints; rollbacks; backup; sort; DBCC SHRINKFILE; and DBCC CHECKDB operations.

Stress testing of IO subsystems can be useful for diagnosing potential issues before they are realised in a deployed production environment. As stated by the SQL Server Storage Engine Team:

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.

A number of sample configuration files are included for testing multiple scenarios such as multiple files, sequential writes, and hardware caching.

A Knowledgebase article on how to use SQLIOSim has been published.

Downloads for SQLIOSim:

SQLIOSim utility for x86

SQLIOSim utility for x64

SQLIOSim utility for Itanium