Stress Test Using sqlcmd Utility
August 17, 2020
I am writing this blog post for my own reference for future. Hopefully someone will be benefited in the process.
Recently I was working on upgrading System Center Operations Manager and configuring alert for high CPU. I wanted the alert to trigger if CPU was above a certain percent for x consecutive polls.
How do you run these scripts multiple times to reach the intended threshold? This is a trick I learned from Paul Randal (blog|twitter) and will explain in this blog post. I am aware that RML Utilities and SqlQueryStress can also be used for the same purpose. This process is relatively simple without any additional download.
I prefer to initiate the script from a different server, if possible. That way, if the target server is saturated with high CPU or IO, I can still terminate or scale down the test as required without losing control.
I save one of the scripts from the blog mentioned above posts with the name highCpuTest.sql. I will need to create three more files, preferably in the same folder.
First file content (Name: runHighCpuTest.cmd)
sqlcmd -S"BEDTSTSQL501" -"G:\TaiobStressTest\highCputTest.sql" exit
Second file (Name: add5Clients.cmd) will call the first file. You need to repeat the content as many times as you want to execute highCpuTest.sql file in parallel. In this example, I want to run it five times in parallel. If I need more, I can run multiple of five by executing this file as many times as I need.
start G:\TaiobStressTest\runHighCpuTest.cmd start G:\TaiobStressTest\runHighCpuTest.cmd start G:\TaiobStressTest\runHighCpuTest.cmd start G:\TaiobStressTest\runHighCpuTest.cmd start G:\TaiobStressTest\runHighCpuTest.cmd
Third file (Name: killWorkers.cmd) to stop the test. Executing this file will stop all instances of sqlcmd.
TASKKILL /IM sqlcmd.exe /F
This process can be used for running any .sql file multiple times in parallel for test purpose.