Stress Test

20 Aug 2019 11332 views 0 minutes to read Contributors

Doing a stress test on your SQL Server DATA, LOG and TEMPDB drives is a wise thing to do. This way you gain knowledge about the maximum I/O capacity. 

In case you need to troubleshoot your SQL Server's I/O throughput this will help you. I mean, if you can compare the running numbers with the maximum throughput, you will know if you have reached your maximum or not.

Components

The stress test consist of a series of tests with the sqlio.exe program. We have encapsulated the program (SQLIO.EXE) together with several .CMD files and a README.TXT into a zip file. You can download it here.

* SQLTreeo (c) 1998 till current date
* README.TXT
* RUNSQLIO.cmd
* RUNSQLIO_data.cmd
* RUNSQLIO_log.cmd
* RUNSQLIO_tempdb.cmd
* SQLIO.EXE

Each of the RUNSQLIO cmd's will perform a series of tests on a specific drive (-d[driveletter] )
RUNSQLIO_data.cmd is currently configured to run on -dI
RUNSQLIO_log.cmd is currently configured to run on -dT
RUNSQLIO_tempdb.cmd is currently configured to run on -dX

you should run RUNSQLIO.CMD (it will execute all the above scripts)

• If you have different drives, then change that parameter in the file. 
• The output for each cmd will go to a sqlioresults txt file. If you feel the need to change that filename, you are free to do so.
• Ignore the first error (if any) that comes out during execution.
• Make sure the user account has write permissions in the root of the drives.
• If you feel the need to start over, delete the result file(s) first
• After stress tests send the files to servicedesk@sqltreeo.com.
• If you have any questions, send a mail to servicedesk@sqltreeo.com.

After the stresstest files have been received, you will receive the analysis back that will show you the MB and IOPS capacity: