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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
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; |
Probably Don’t Do This In Production
|
1 |
EXEC dbo._keep_it_100 |
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.
15 Comments. Leave new
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?
exactly….lol
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.
Get thee behind me, Satan! And push!
There’s a “Bad Idea Jeans” category on this blog for a reason, Erik.
HAHAHA, nice.
Was this that sample code they ran at PASS Summit a couple years back? 😉
Can’t get the average CPU much above 50% while running this.
Oh, interesting. This has been run on a lot of different servers now. How are you measuring that?
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.
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.
Howard — interesting! If you can alter it to hit 100% CPU in the lower compat level, I’ll make Brent give you something!
Thanks!
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.
Excellent… muahahahahahahaaaaa
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