Never run this on your production server – you can just lose your job and can damage your organization.
Now I am done with the disclaimer here is the subject I would like to discuss. How to keep your CPU busy? Well, actually there should not be any need of this query for production server purpose. I needed this query for development server purpose when I was testing the server for Capacity Planning as well doing the Stress Testing.
When I quickly searched for this query, I end up on my old blog post over here Demo Script – Keeping CPU Busy. However, my earlier script had a small problem – along with the CPU it was also increasing the IO to very high value. If you are looking for the script which increases IO along with CPU my script was a good example. However, if you are looking for the example where CPU and IO both goes high – my example is a great example. However, if you are looking for example where only CPU goes high, my example was not good. However, SQL Server expert Geri has excellent suggestion where he pointed out that if I need only CPU high I should use different script.
Here is the same script by Geri.
-- Query to Keep CPU Busy for 30 Seconds
DECLARE @T DATETIME, @F BIGINT;
SET @T = GETDATE();
WHILE DATEADD(SECOND,30,@T)>GETDATE()
SET @F=POWER(2,30);
You can easily change the parameter in the DATEADD and can make it run for 60 seconds.
-- Query to Keep CPU Busy for 60 Seconds
DECLARE @T DATETIME, @F BIGINT;
SET @T = GETDATE();
WHILE DATEADD(SECOND,60,@T)>GETDATE()
SET @F=POWER(2,30);
If due to any reason your CPU does not go higher to 100%, I suggest you run above query in two different connection window and you will notice that CPU is increasing heavily.
Reference: Pinal Dave (https://blog.sqlauthority.com)
14 Comments. Leave new
That simple! incredible. Thanks
That simple?! Incredible! thanx
Hey Pinal, once again thanks for excellent post. I was actually thinking what might have churned the cpu to be high here(if only the power operation). What if I have 4 core machine and run this with maxdop 1. Will it still give me 100 prcnt… Correct me if I am analyzing it wrong… Thanks in anticipation…
Power is really a CPU expensive function. Good one.
A word of caution – if your test machine (where you will throttle the CPU to 100%) is a VM, make sure you can power it down and back on (reset) through the VM console just in case the machine becomes unresponsive :)
How to get a mail alert when my CPU utilization is high ??
Could you explain please, why the script transfers so hudge amout of data from server to client?
Thanks Pinal ,I am not sure how its keeping CPU busy but its fantastic way to put workload on SQL Server.
Thanks and Regards
Jayant Dass
Hi,
This is really nice way to hit CPU on a Server.
If I have to execute from a remove server I see network is the bottleneck. Any recommendations on how we can minimize network bandwidth?
Thanks,
Vijay
https://blog.sqlauthority.com/2016/06/01/sql-server-identifying-application-vs-network-performance-issues/
Hello Pinal, thanks for the query! Should the CPU go 100%? becuase when i run it i only get it up to 19% CPU usage.
Have you tried running same from multiple connections?
I’m not getting CPU higher than 10-20% even with running in multiple connections :-( I can’t seem to get my CPU to go up, whatever I try
looks like you have faster machine :)