Published on

February 13, 2013

Keeping Your CPU Busy in SQL Server

Disclaimer: The following query should never be run on a production server as it can lead to job loss and potential damage to your organization. This query is intended for use on development servers for capacity planning and stress testing purposes.

When testing a server’s capacity and performance, it can be useful to keep the CPU busy. In a previous blog post, I shared a script that achieved this goal but had the unintended side effect of increasing IO to a high value. However, thanks to the suggestion of SQL Server expert Geri, I now have a script that focuses solely on increasing CPU usage.

Here is the updated script:

-- 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 modify the script to run for a longer duration by changing the parameter in the DATEADD function. For example, to keep the CPU busy 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, for any reason, your CPU usage does not reach 100%, you can try running the above query in two different connection windows simultaneously. This should result in a significant increase in CPU usage.

Remember, it is crucial to exercise caution when running resource-intensive queries and to only do so in controlled environments. Running such queries on a production server can have severe consequences. Always consult with your database administrator or IT department before performing any stress testing or capacity planning activities.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.