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?

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.

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.