The entirety of Resident Evil 4 is masterful. It was scary. Tense. Beautiful to look at and perfect to play. A real game changer on the Gamecube and I loved every minute of it. Every area is memorable. The opening area is amazingly well designed and paced perfectly. The castle ramps up the difficulty and complexity of the design. In particular there is an area of the castle where you have a massive room flanked by enemies on the ground and enemies shooting you with cross bows from the balcony. You have stairs that lead to an underground room and then upstairs you need to get Ashley to crank the levers so as to raise the ground to get over the water whilst enemies are trying to not only kill her but also you as well! Brilliant and tense all the way through.
It all ends on an island with fantastic outside areas and well designed inside areas with you surrounded by enemies, especially scary with Regenerators around many corners!
Take your pick of basically ANY level in Super Mario World. Game is, in my opinion, the best designed 2D platformer of all time. And I do love Super Mario Bros 3 but World beats it.
Recently replaying the 3D Mario games on Switch reminded me how brilliant Mario Galaxy is. Almost perfect 3D design for a platformer.
1st and 2nd level of Goldeneye are seared into my brain.
Mega Man 3 was always my fav Mega Man game, so it's really hard to choose a particular level, but I'd say maybe Snake Man's level, which had incredible music as well, as my stand out.
Doom is brilliant. Best 1st person shooter ever made. Best designed levels as well. Perfect.
Doom 64 is underrated and has amazing level design as well. Plus that fucking music!!!!!
Castlevania: Symphony Of The Night. Pick ANY area of the castle you like. Even the inverted castle. All of it is masterfully designed.
Metroid Prime and Super Metroid. Perfection. So hard to pick a particular area in both of these games...
The mansion in Resident Evil.
The school in Silent Hill.
I'm sure there are MANY I've forgotten but I'll leave it at that for now!