One Hundred Percent CPU

Last Updated 6 years ago

Raise Your Hand If

You’ve ever wanted to play a prank on your co-workers, but just didn’t have a any ideas that didn’t involve exploding Hot Pockets.

Now you have something even less safe than molten cheese squirts!

A stored procedure that pushes CPUs to 100%.

All of’em.

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;

CREATE OR ALTER PROCEDURE dbo._keep_it_100

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

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

) AS x

WHERE x.n = e2.n

ORDER BY x.n

) AS ca

OPTION(MAXDOP 0, LOOP JOIN, QUERYTRACEON 8649);

END;

Probably Don’t Do This In Production

EXEC dbo._keep_it_100

1	EXEC dbo._keep_it_100

We got this tested on 48 cores, and it was pretty sweet.

Special thanks to Forrest for testing this out on his dev server for me.

At least I hope it was.

Thanks for reading!

Brent says: wanna test your monitoring or alerting processes and see how quickly folks get alerted, start troubleshooting, and get to a root cause? Run this in your QA environment and see how fast folks react.

Free Training Classes for Those Who Give Back: Announcing our 2019 Scholarships

A Simple Stored Procedure Pattern To Avoid

15 Comments. Leave new

Chad Estes
September 18, 2018 11:19 am

Why do I get the feeling that y’all come up with these things to be devious/nefarious and then look for a logical application after-the-fact to explain why you did it in the first place?

Reply
- Roberto
  January 30, 2019 7:04 am
  
  exactly….lol
  
  Reply
Cole Kelley
September 18, 2018 11:21 am

Thank you for making me laugh today (really)! I don’t know if it’s funny because I know what its doing, or because of what it does. Probably both.

Reply
Kevin Fries
September 18, 2018 11:41 am

Get thee behind me, Satan! And push!

Reply
Wayne Rossi
September 18, 2018 12:07 pm

There’s a “Bad Idea Jeans” category on this blog for a reason, Erik.

Reply
- Brent Ozar
  September 18, 2018 1:32 pm
  
  HAHAHA, nice.
  
  Reply
Peter
September 18, 2018 5:03 pm

Was this that sample code they ran at PASS Summit a couple years back? 😉

Reply
Howard
September 18, 2018 5:41 pm

Can’t get the average CPU much above 50% while running this.

Reply
- Erik Darling
  September 18, 2018 5:46 pm
  
  Oh, interesting. This has been run on a lot of different servers now. How are you measuring that?
  
  Reply
  - Howard
    September 18, 2018 6:11 pm
    
    I used Resource Monitor. First try was on SQL Server 2012 and it barely pushed 45% CPU. I just installed SQL Server 2017 on the same server and ran it there and it immediately hit 100% CPU.
    
    Reply
    - Howard
      September 18, 2018 6:25 pm
      
      Ok the plans are very different between 2012 and 2017. On SQL Server 2017 it immediately hits 100% cpu but if I switch the compatibility level to 110 and run it then SQL Server 2017 behaves like the SQL Server 2012 with only medium cpu use.
      
      Fun experiment.
      
      Reply
      - Erik Darling
        September 19, 2018 6:42 am
        
        Howard — interesting! If you can alter it to hit 100% CPU in the lower compat level, I’ll make Brent give you something!
        
        Thanks!
      - Jeff Moden
        January 30, 2019 10:42 am
        
        Heh… bloody “improved” Cardinality Estimator strikes again.
        
        Heh… most people don’t need such code, though. They’ve got users and processes that just come by such levels all by themselves.
Robert Rice
September 19, 2018 10:38 am

Excellent… muahahahahahahaaaaa

Reply
Carlos Garcia
July 23, 2020 4:05 pm

Interesting,

* 2008 compatibility level I get a litle bit more than 80 % CPU usage on a quad core server

* 2014 compatibility mode, it gets to 100 % immediatly

* 2017 compatibility mode, it gets to 100 % immediatly

Reply