I love a bad idea
I mean, just look at me. I’m covered in them! So when Brent wanted to randomly make his server act crappy, I wrote a script for it.
Usual caveats here. Don’t run this in production. I’m not responsible for anything you do after you hit copy and paste. In fact, I’m not responsible for anything before or in between, either.
You are your own fault.
What does this thing do?
It randomly generates values and changes some important configuration settings.
- Max Degree of Parallelism
- Cost Threshold
- Max Memory
- Database compatibility level
This was written for SQL Server 2016, on a box that had 384 GB of RAM. If your specs don’t line up, you may have to change the seed values here. I’m not putting any more development into this thing to automatically detect SQL version or memory in the server, because this was a one-off joke script to see how bad things could get.
How bad did they get? The server crashed multiple times.
Umpire sits
Here’s the script. I highly don’t recommend setting it up as an agent job that runs every 10 minutes.
Thanks for reading!
|
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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
USE master; GO ALTER PROCEDURE dbo.sp_ChaosSloth AS BEGIN SET NOCOUNT ON --Declare stuff DECLARE @SQL NVARCHAR(MAX) = N'' DECLARE @MAXDOP INT = 0 DECLARE @MAXMEM INT = 0 DECLARE @CTP INT = 0 DECLARE @COMPAT INT = 0 DECLARE @isDoubleDown BIT = 0 DECLARE @nl NVARCHAR(10) = CHAR(10) + CHAR(13) DECLARE @COMPAT_LEVELS TABLE (LVL INT) --Valid compat levels INSERT @COMPAT_LEVELS SELECT 100 INSERT @COMPAT_LEVELS SELECT 110 INSERT @COMPAT_LEVELS SELECT 120 INSERT @COMPAT_LEVELS SELECT 130 --Set up logging table IF OBJECT_ID('DBAMetrics.dbo.ChaosSloth') IS NULL CREATE TABLE DBAMetrics.dbo.ChaosSloth ( RunID BIGINT IDENTITY(1,1), RunTime DATETIME2(7) DEFAULT SYSUTCDATETIME(), MaxDopSet INT, CostThresholdSet INT, MaxMemorySet INT, CompatLevelSet INT, isDoubleDownSet BIT ) --Set random values SELECT @MAXDOP = ABS(CHECKSUM(NEWID()) % 18), @CTP = ABS(CHECKSUM(NEWID()) % 200), @MAXMEM = ABS(CHECKSUM(NEWID()) % 358400) + 16384, @COMPAT = LVL FROM @COMPAT_LEVELS ORDER BY NEWID() --Always double down on 11 SELECT @MAXDOP = CASE WHEN @CTP % 11 = 0 THEN 0 ELSE @MAXDOP END, @CTP = CASE WHEN @CTP % 11 = 0 THEN 0 ELSE @CTP END, @MAXMEM = CASE WHEN @CTP % 11 = 0 THEN 16384 ELSE @MAXMEM END, @COMPAT = CASE WHEN @CTP % 11 = 0 THEN 100 ELSE @COMPAT END, @isDoubleDown = CASE WHEN @CTP % 11 = 0 THEN 1 ELSE @isDoubleDown END --Break stuff INSERT DBAMetrics.dbo.ChaosSloth (MaxDopSet, CostThresholdSet, MaxMemorySet, CompatLevelSet, isDoubleDownSet) SELECT @MAXDOP, @CTP, @MAXMEM, @COMPAT, @isDoubleDown --MAXDOP SET @SQL = N'EXEC sys.sp_configure @configname = ''max degree of parallelism'', @configvalue = ' + CONVERT(NVARCHAR(100), @MAXDOP) SET @SQL += @nl + N'RECONFIGURE WITH OVERRIDE' PRINT @SQL EXEC(@SQL) --CTP SET @SQL = N'EXEC sys.sp_configure @configname = ''cost threshold for parallelism'', @configvalue = ' + CONVERT(NVARCHAR(100), @CTP) SET @SQL += @nl + N'RECONFIGURE WITH OVERRIDE' PRINT @SQL EXEC(@SQL) --Max Memory SET @SQL = N'EXEC sys.sp_configure @configname = ''max server memory (MB)'', @configvalue = ' + CONVERT(NVARCHAR(100), @MAXMEM) SET @SQL += @nl + N'RECONFIGURE WITH OVERRIDE' PRINT @SQL EXEC(@SQL) --Compat level SET @SQL = N'ALTER DATABASE StackOverflow SET COMPATIBILITY_LEVEL = ' + CONVERT(NVARCHAR(100), @COMPAT) + N' WITH NO_WAIT;' PRINT @SQL EXEC(@SQL) END |
Brent says: the name comes from Netflix’s Chaos Monkey, part of their open source Simian Army set of tools that will randomly break things in their AWS environment. They figure if they randomly take things down, it’ll force developers and admins to build a more reliable, tolerant infrastructure.
7 Comments. Leave new
Yikes! You could put an eye out with that thing.
Someone WILL inevitably poke their eye out with this in production. Be ready for the consult calls. Heh.
I promise, this was not designed as a ‘lead generating’ tool.
Oh! This brings back memories of similar practical jokes from my early days as a coder.
I like the clean new look of the website by the way.
Nice work guys.
Simian Army reminds me of the opening scene of Toy Story 3 where Hamm punches the “Death by Monkeys” button and drops a literal barrel of them. The nuclear cloud of them is an awesome extra touch.
“the name comes from Netflix’s Chaos Monkey, part of their open source Simian Army set of tools that will randomly break things in their AWS environment. They figure if they randomly take things down, it’ll force developers and admins to build a more reliable, tolerant infrastructure.”
Me likey that method that method of ensuring reliability VERY much 😀