SQLIOSim

Stress-testing an instance of SQL Server 2005 can be difficult. Back in the old days of SQL Server 6.0, it was fairly easy to build a test suite of SQL statements that represented a realistic server workload. But now, with 64-bit servers and extremely fast CPU and disk subsystems, it's difficult to construct a stress test that can actually stress a SQL Server instance. To help you more easily create a realistic stress test, the SQL Server Storage Engine team has introduced SQLIOSim, a stress-testing tool that can generate a load sufficient to overwork almost any hardware configuration.

SQLIOSim was created by Bob Dorr, a senior escalation engineer in Microsoft's SQL Server Support team, and the tool is now owned by the SQLOS development team and program manager, Jerome Halmans. SQLIOSim doesn't generate an I/O load by replaying SQL captured from real sessions connected to the target SQL Server. Instead, the tool generates the same type and pattern of I/O requests on a disk subsystem as SQL Server would, then verifies that the data is written exactly as SQL Server would. You can then use the information that the tool provides to determine whether your current disk subsystem is adequate for the application at hand. The key word here is simulate because SQLIOSim creates its own I/O stress load; it doesn't generate transactions that you'd actually use against a user database.

Functionality

When you download SQLIOSim, you'll get a single, compressed file containing two executables: the GUI version of the tool in the SQLIOSim.exe file and the command-line version in the SQLIOSim.com file. The compressed file also contains useful sample configuration files that you can quickly customize to fit your specific testing needs.

You have a great deal of flexibility in how you configure the SQL Server I/O stress test. For example, you could:

• test the behavior of DBCC CHECKDB while the system is in use
• see how bulk-load jobs perform on the disk subsystem
• evaluate the benefits of single versus multiple file setups for SQL Server databases
• see the difference in behavior of OLTP, mixed-use, and OLAP applications by tuning the randomness of the requests

The two versions of the program support a variety of operating parameters, such as the duration of the test run and the size of the data file, the number of CPUs on the computer, the affinity mask for the CPUs, the maximum available physical memory, the number of test cycles, and the cache hit ratio. The blog entry at http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/06/SQLIOSim-available-for-download.aspx provides full details about the various command-line switches and configuration settings available for SQLIOSim.

By default, the output of SQLIOSim is stored in an XML file called SQLIOSim. log.xml, from which you can extract the raw data and manipulate it as needed. But remember that SQLIOSim is not a performance-measurement tool. It's a stress-testing tool that can generate a load capable of overwhelming most disk subsystems, even powerful, multi-spindle RAIDs. Therefore, I recommend that during your tests, you track several specific Windows Performance Monitor counters: disk sec/read, disk sec/transfer, disk sec/write, disk bytes/sec, and queue lengths. If SQLIOSim finds any errors, they'll be reported in the log file.

SQLIOSim is a must-have utility for developers and DBAs who are building highly scalable applications for the SQL Server platform. Be sure to visit the Tool Time forum at http://sqlforums.windowsitpro.com/web/forum and post your comments and tool recommendations.