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. This script to keep CPU busy is only meant for test servers.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.