September 27, 2018 at 2:19 pm
What kind of T-SQL query can I run on the server simulate high load on the server in order to make it nearly hang it or hang it (in order to stress test it, make it hot and loud, and see smoke coming out of the box) ?
I also want to make some measurements via Extended Events and one more 3-rd party software while it is smoking hot and running close to maximum capacity. The goal is to test the performance-measuring tool.
(I have everything to test tools, no advice on that needed, but not sure how to make this server really work its guts off as a result of a single SQL query.
Win Server 2012 has 4 processors and 64G RAM. SQL Server 2016 Enterprise.
Thank you in advance for your creative ideas!
Likes to play Chess
September 27, 2018 at 3:06 pm
If you don't want any fancy third party tool(something like hammerdb) to hammer your server, Eric Darling came up with a stored proc recently which should ramp up your CPU to 100%.
https://www.brentozar.com/archive/2018/09/one-hundred-percent-cpu/
September 28, 2018 at 6:39 am
I've generally used two approaches. First, Distributed Replay is free with SQL Server. It will let you replay a captured set of events (trace or extended events) against a server or just a database. It's a pain to set up, but once it's working, it just works. You can control the number of machines you're using to hammer the server, control "think time" to ensure a higher load, all sorts of stuff.
Second, it's pretty easy to use PowerShell to run a set of procedures in series or in parallel, or if you're feeling really frisky, both. These can simulate a realistic load on the machine. Just run them while 1 -ne 0 and you'll have a non-stop load generation.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply