Mario emerges from a pink pipe to find himself in a short, flooded section of the castle, replete with bloopers. Bloopers, and fire. Underwater. The wacky relationship between fire and water that the player put to good use earlier in the game has suddenly been employed by the enemy to turn the tables. While this section isn't the hardest to navigate, it is fairly difficult to navigate while still keeping your Fire Mario/Super Mario powerup.
Which is very cruel, as the final room of the game awaits upon emerging from the flooded passage, and Mario's final obstacles are a trickily positioned hammer brother and a hammer-throwing, fire-breathing Bowser. Shigeru Miyamoto is a real bastard sometimes.
The streamlined simplicity of Super Mario Bros. makes it difficult in a way to pinpoint any one moment of the game as memorable. Sure, there are lots of aspects of the game that are iconic, but defeating Bowser at the end is much like defeating him the previous seven times, and the ending isn't particularly noteworthy. The game stays pretty fresh throughout, but there is very little that sticks with you beyond bits and pieces like "Oh, yeah, that part with the bridge and the goombas in 3-1 where you can get the 1-up, that was cool."
This moment, the flooded part of the final castle, sticks out in large part due to the music. The castle music and underwater music are both heard several times already throughout the game, there's nothing especially about hearing them one more time in the game. What stands out about this moment is the juxtaposition of the two tunes. Without warning, right in the middle of the final castle, the tone changes from desperate and foreboding to whimsical and happy on the strength of the music. In addition, the change from the black, gray, and red palette of the castle to the green, blue, and pink of the sea also has an effect. This section stands out in contrast to the rest of the level, and to the rest of the castle levels.
Reading about it, this moment may not seem all that special or different from any of the other set pieces in the game that one can recall. However, every time I play Super Mario Bros. and get to world 8-4, I'm always a little bit amused by the sudden contrast and the illusion of whimsy that the music imparts while I'm desperately trying to keep my fire flower. Nobody wants to try and get past that hammer brother without a fire flower.
It's not an epic moment, it's not emotional, but depending on your sense of humor, it's a bit funny, and for me certainly qualifies as a moment I always remember.