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.

While simulating this test in one of the test servers, I came across these two blog posts by Pinal Dave (blog|twitter).

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.