The most instantly memorable wasn't 40k, but Warhammer Fantasy - first game after the 8th ed Lizardmen booked dropped and Slann were nerfed down to being strong casters rather than game-breaking powerhouses. First player turn, first spell cast, the Slann fudges it with a Miscast, falls victim to a Dimensional Cascade and blows up. One of my friends watching captioned the moment as 'Fuck this, I'm going back to 7th ed!', and that just stuck with me for some reason.
For 40k, there's one every few games. My first game with my new Guard army had an abysmal turn 1 - I brought a pair of Inquisitors to get my blobs to take Orders on Ld10 rather than Ld7. Rolled two 11s for my Orders, and proceeded to fire 1500pts of army killing 2 Bikers from one unit, 1 Biker from another and a Rhino. My opponent then fails both morale checks for the Bikes and the Pinning check on the disembarked unit. From there the game devolves into me shooting him off the table as he makes the most horrendous rolls in history.
When I played Dark Angels, I had a squad of Chaos Terminators with a Chaos Lord in combat with some Tactical Marines and some Deathwing Knights (I forget who charged who, it doesn't really matter - that's how it ended up). The Chaos Lord in question was pretty tooled up - Terminator Armour, the Mark of Nurgle and a Gift of Mutation which he rolled up as +1W, the whole kaput. Can you say tank character! Anyway, he kills the Knight Master, and rolls up re-roll all failed armour saves as a Boon. Fuck. I nail the regular Terminators with Smite on the DWK, leaving just this Lord, who's now T5 with a re-rollable 2+ and 4 wounds against a squad of guys who've used up all their AP2 weapons. Fuck again. Next round, he eats the Tactical Squad sergeant, promptly turns into a Spawn and gets nailed by Power Mauls. The Dark Gods giveth, the Dark Gods taketh away.
I had a Centstar of Sicarius, Tigurius and 2 Librarians as a Conclave and 3 Grav Centurions wipe out a Lychstar leaving just Orikan the Empowered on 1 wound standing in a pile of scrap metal, then charge him to finish the unit off and take the deciding objective. Over the next two turns, Orikan kills most of the unit and the game ends with that objective contested, the Necrons taking the win.
Ezekiel and his Tactical Squad get charged by Scarab Swarms. Ezekiel casts Force, rolls double 6s, rolls a 1 for Perils, rolls an 11 for his Ld check, kills himself and 5 Tacticals.
Deathwing Squad with Thunder Hammers and Storm Shields Deep Strike down into an Eldar line. Take 27 wounds, save every single one. Belial kills or breaks to run off the table 3 seperate 10-man squads of Dire Avengers over the course of the game.
Belial charges 3 Necron Immortals with Tesla Carbines, who take off 2 wounds on Overwatch and then kill him in melee.
Sicarius performs his Sons of the Primarch ability in a unit buffed with Hammerhand, killing a Revenant Titan, then splits off from the unit. He goes through a unit of D-Cannons with the Farseer Warmaster in there, survives with 1 wound remaining, then takes fire from 20 Guardians, 2 Razorwings with Disintegrator Cannons, and a Wave Serpent with Starcannons. Survives all that, gets charged by 3 Guardians, who kill him (apparently 3-man Troops units are the bane of my characters). In the same game, Tigurius and his two homies in the Conclave split from the Centurions and fail a charge against some Dark Reapers. They get shot at, and in the absence of any kind of cover, Go to Ground as a Hail Mary stratagem. Tigurius casts Precognition at least, and proceeds to make all 5 6+ saves.