The most astonishing stage effect?

Like you, decades on I still am overawed by the sight of Jesus, in the original Palace Theatre Superstar, rising from the depths of the stage in cross formation, even though there was no cross to be crucified on... I thought it was breathtaking.

The first time I saw two pieces of rubbish, either side of the stage in Les Miserables, join to be come a barricade I was dumbstruck. We’re all used to it now...   The same goes for Javert’s ‘jump’ from the bridge on the Seine into the swirling river... OMG!

These are just some moments I’ll never forget.

Maybe not the most astonishing but the 'simple but effective' illusions in Hope and Nobody Cares in Groundhog Day were both pretty stunning and received rounds of applause. The black & white/colour projections at the Donmar's City of Angels was awesome considering the small space. 42nd St staircase, of course! Not sure if this really kinda fits and it's more staging, but the Swastika in The Producers during Springtime for Hitler with the giant mirror behind it will always stay with me. Of course the majority of Harry Potter & the Cursed Child!! The technology in Curious Incident... and actually the tech used in wonder.land at the National (I didn't hate that show as much as everyone else in the world, so sue me!). Although I didn't see it live, the transition in Hangmen looked pretty cool on screen. 

I wish, I wish, I WISH I could've seen the Carousel come together in the National production (I think that's the one I'm thinking of? I guess I'm the only one who can answer that question).

The War Horse transformation was also very good, yes!

Lord of the Rings had some amazing tricks.

And in the original Sunset, the way the house pulled back mimicking a fade out in film on "we'll begin at nine sharp" was quite something.
More simply though, the spotlight hitting Norma as the music swells to "With One Look" is one of the best moments in a musical.

End of Harry Potter Part I is pretty great too.

Come to think of it, I really liked the effects in Charlie and the Chocolate Factory at Drury Lane.

I loved the paper aeroplane effect as it looked very realistic from my seat. Also the costume change when Wonka first enters...did it disappear into his cane? Looked cool. And finally at the end when Wonka turns around and disappears through the trap door before his coat falls to the stage - nice ending.

The costume change as he enters is done by attaching a long elasticated rope to the back of his costume and its pulled back quickly through the door he entered in, literally ripping the costume off to reveal the one underneath. Much like how a measuring tape works when you push the button on it.

Yes! Forgot about that Charlie. Some great effects in there. The first time I saw the paper aeroplane effect was wonderful. Recreated in Bat out of Hell at the end of the Finale.
Wonka's costume change in 'It must be Believed...' was the one jacket pulled from the middle of the back, whipped down off into the dark backstage as he was up a few steps.

Most underwhleming effect has to be the replacement "Great Glass Elevator" Charlie received once the original automated, hydralic elevator completely gave up towards the end of the run. A scissor lift with some black drape wrapped round it, rolled on and off. Poor.

and I was going to say when it snowed in Frankenstein

The effects in the RSCs The Tempest, which were created by Imaginarium were quite stunning. I also seem to remember the hologram of a woman in a shower during Terry Johnson’s Hitchcock Blonde at the RC some years ago.

Despite thinking it was a terrible show, and despite it actually being quite obvious how it was done, the one that instantly comes to mind was Willy Wonka's disappearance right at the end of Charlie and the Chocolate Factory, when the coat falls to the floor. It was a really impressive moment

Others have posted about Ghost and while I thought the show was awful, there were some effects that were simply extraordinary. I saw it in the West End and except for the effects had an awful time. Circumstances found me seeing it again when it played in New York and because I knew the show was sh*t I was was able to concentrate on the effects and illusions. By doing that, I actually had a good time.