sqlsql-serversql-server-2008-r2cpu-usage

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


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?


Solution

  • 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.