SQL SERVER – T-SQL Script to Keep CPU Busy

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.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

6 thoughts on “SQL SERVER – T-SQL Script to Keep CPU Busy

  1. 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…

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s