This is based on opinion. Please don't list it on a work's trope example list.
Nightmare Fuel / Super Mario Sunshine
Now imagine this thing as big as a house. And fighting him. In the dark. Underwater. Sleep tight!
The giant eel in Noki Bay, pictured right. He's huge and you have to fight him in a dark, underwater trench. And he attacks by trying to eat you! Especially bad if you're not good with water.
Even worse is that looking at it in a model viewer reveals that it's hollow with a highly detailed esophagus and stomach area. It's unviewable in normal gameplay but it's still unnerving that they made those.
Phantamanta. It's a large, paper-thin orange shadow in the shape of a manta. Wherever it goes, it leaves a trail of goop and emits a really creepy shriek when you try to spray it. The worst part? It's an Asteroids Monster and divides into smaller faster shadows as it is destroyed. When you've finally destroyed half of the original manta, the remaining mantas stop fooling around and all converge on your position at once with incredible speed at a time when you're probably almost out of health. You may think that going in the lake would protect you from it, but it just hovers over any water! Nowhere is safe!
On a side note, the plot itself is scary, as it definitely becomes a lot darker. Mario's been sent to prison for a crime he didn't commit, instead being committed by Bowser Jr. He's forced to then face off against Shadow Mario, a criminal mastermind, to not only save the princess, but the entire island, and his good name. Making it worse, he has no power-ups this time, only F.L.U.D.D.; the Flash Liquidizing Ultra Dousing Device as his partner and weapon worn on his back. Bit dark, don't ya think?
A bit more meta, but the game uses bitmap images for generating goop on maps. The bitmap for an older version of Bianco Hill's goop shows what looks like a "Kug" (a Dummied Out enemy), a piranha plant, and... aNightmare Face with what looks like hexigonal teeth, big eyes, and a weird-looking wisp-hand sticking out. See it for yourself: here.
Try not to think too hard about the implications when Bowser calls Peach his wife. It's not true, but Peach seems...oddly complacent about it to say the least.
A few of the music and sound effects in Sunshine can be terrifying. For example, Hotel Delfino, a Hell Hotel-type ghost house stage, is frightening enough on its own, but its background music is a fairly standard-sounding tune...with bones rattling in the background.
Getting sucked into another dimension. You lose FLUDD, have only your jumps, are trapped in cheery, happy puzzle-worlds and the levels are Nintendo Hard. Hope you brought plenty of lives!
The Blooper Surfing course can be pretty spooky when you realize that there's no roof to the area. Instead, past a certain height, there is just blackness. It doesn't help that there are a lack of bright colours in the area, in contrast to most of the game, making the area feel unnatural as a result.
Use a camera editor (like that included with Dolphin) and get the camera out of bounds, and it's revealed that the stage actually hides the background to the special levels. You're not supposed to see it normally, but it's exceptionally creepy to realize that a room that's supposedly under a building is actually floating in psychedelic space.
You thought King Boo was frightening in Luigi's Mansion? Wait'll you see him here.