Trying to create an SQL Query that will max all CPUs to 100%

Question

As part of a stress tests I'm doing I'm trying to figure out if there is an SQL query (quite specifically SQL Server query) that will max all CPUs usage to 100% or close enough.

Suggestions anyone?

Conquian

Not sure why you'd want to do this but an infinite recursive query using a with CTE may come close... – Snooker

Answer 1

SELECT SUM(CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id) + CONVERT(BIGINT, o4.object_id))
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4

Here's a parallel version:

USE master

SELECT MyInt = CONVERT(BIGINT, o1.object_id) + CONVERT(BIGINT, o2.object_id) + CONVERT(BIGINT, o3.object_id)
INTO #temp
FROM sys.objects o1
JOIN sys.objects o2 ON o1.object_id < o2.object_id
JOIN sys.objects o3 ON o1.object_id < o3.object_id

SELECT SUM(CONVERT(BIGINT, o1.MyInt) + CONVERT(BIGINT, o2.MyInt))
FROM #temp o1
JOIN #temp o2 ON o1.MyInt < o2.MyInt

For some reason I cannot get the optimizer to parallelize the first query. So I just materialize some huge tables (~400k rows) and loop join them.

Chigger

You might want to throw in an ORDER BY ... to really make the CPU work ;) – Graehme

Would you care explain the query and why it max the cpu to 100? Thank you – Conquian

Also would it work on dual core as well? i.e. maxing both to 100% CPU? – Conquian

This generates a huge set of rows (N^4 rows where N = count(sys.objects)) and counts it. The optimizer cannot simplify this, so it runs for an enormous amount of time. It only maxes out one CPU for me. Do you want all CPUs? I'd have to work on that. – Chigger

@a_horse_with_no_name that might well lead to tempdb filling up. Better just stress the CPU and have zero memory grant. – Chigger

Yes, I'm trying to max 2 CPU's but right now I'm getting 50% CPU usage total on sqlserver.exe. see here: i.sstatic.net/I6RcJ.png and here: i.sstatic.net/lmhvN.png – Conquian

You're the greatest! Wonder why the first query max the CPU to EXACTLY 50%... but still nice job! :) – Conquian

It maxed out a single core on your 2 core machine. – Chigger

Not according to this screenshot: i.stack.imgur.com/I6RcJ.png, but that might be because the cores are virtual cpu (vCPU) of Amazon EC2. – Conquian

The OS can put threads on whatever CPU core it wants. There is one thread here that is always runnable. It just happens to be spread out. – Chigger

I ran six of the first query and got sql express to use 4 cores worth of power. 2nd query only got 2 percent of the CPU. But first is very usefull. Thankyou! – Mailemailed

@Mailemailed did you wait for the batch to complete? The first face is indeed IO bound. After that you get CPU-only load. – Chigger

Ahh k. I didnt give it a chance and I wasn't watching IOs. Tried again - got a few seconds of 100 percent on my SSD, then got one full core in sqlservr. Not parallel? – Mailemailed

It's parallel for me but no guarantees. You can play with the query. Maybe add another join. Apply the usual parallel plan debugging techniques which can be found on the web. There are a few main causes for unexpected serial plans. – Chigger

Great answer, while the sum() could lead to overflow, can add sin making it as sum(sin(CONVERT(BIGINT, o1.MyInt))). – Montague

@YangLiu considering overflow is a good point but this query uses BIGINT so that there is no overflow. The SUM works on BIGINTs. – Chigger

Answer 2

I've talked at length in How to analyse SQL Server performance about why practically your query never 'executes': is always waiting on something (IO, locks).

To create a workload that drives 100% CPU, even on one core, is no small feat. You need to make sure your query always execute and never waits. Never blocks for IO (all data must be in memory), never blocks for locks (no contention), never blocks for memory (no grant). You should look as scans of hot in-memory data. An artificial, totally bogus, workload that achieves this would probably self-join a medium size table many times.

Now if you want to do this with a realistic workload, including various operations, then good luck. Achieving 100% CPU is basically the benchmarks golden standard. You need super performant IO subsystem to eliminate all waits and you need very fancy test driver to be able to feed the workload fast enough, without creating contention.

Hett

Try @Chigger answer and see if that you get 100% CPU Usage without the need of "super performant IO subsystem to eliminate all waits and you need very fancy test driver to be able to feed the workload fast enough, without creating contention" – Conquian

Answer 3

I think the better way for keeping CPU busy is using POWER function in all compilers

DECLARE @T DATETIME, @F BIGINT;
SET @T = GETDATE();
WHILE DATEADD(SECOND,60,@T)>GETDATE()
SET @F=POWER(2,30);

You can make several query run at same time depends your CPU capacity

Take a look at this link below (Original article)

https://blog.sqlauthority.com/2013/02/22/sql-server-t-sql-script-to-keep-cpu-busy

Templetempler

Note that this method uses massive amounts of network bandwidth (as mentioned in comments on the original article). It saturated my 100 Mb link. – Vender

Answer 4

The idea comes from usr, to makes it a little more general, no SQL Server peculiar things used, so you can use it for any kind of DB/DM. Also there is no write, which will avoid the I/O affect.

Need to select a big use or system table "table1" at first.

If this table has record N, following query produces O(N^4) complexity which optimizer cannot improve it.

with t1 as 
(
    select 
        d1.f1 as i1, d2.f1 as i2, d3.f1 as i3, d3.f2 as f2
    from 
        table1 d1
    inner join 
        table1 d2 on d1.f1 < d2.f1
    inner join 
        table1 d3 on d2.f1 < d3.f1
    order by 
        f2
)
select 
    avg(sin(o1.i1)) + avg(cos(o2.i1))
from 
    t1 o1 
join 
    t1 o2 on o1.i1 < o2.i1

Montague

Answer 5

This is very old yet interesting question, but this link (from Brent Ozar) is the best solution compared with all other solutions here listed

https://www.brentozar.com/archive/2018/09/one-hundred-percent-cpu/

CREATE OR ALTER PROCEDURE dbo._keep_it_100
AS
BEGIN

WITH e1(n) AS 
    (
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL 
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL
    SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL 
    ),                          
e2(n) AS (SELECT TOP 2147483647 NEWID() FROM e1 a, e1 b, e1 c, e1 d, e1 e, e1 f, e1 g, e1 h, e1 i, e1 j)
SELECT MAX(ca.n)
FROM e2
CROSS APPLY
(
    SELECT TOP 2147483647 *
    FROM (
            SELECT TOP 2147483647 *
            FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
            UNION ALL SELECT * FROM e2
         ) AS x
    WHERE x.n = e2.n
  ORDER BY x.n
) AS ca
OPTION(MAXDOP 0, LOOP JOIN, QUERYTRACEON 8649);

END;

Laynelayney