Long and/or flashy combos tend to get this, as most games since the later editions of Street Fighter II will implement some form of combo scaling to ensure that each hit gets weaker as the combo continues, on top of the large amounts of meter consumption and highly technical execution requirements most will entail. The only exceptions to this in games since tend to be infinite combos.
In many Bleach fighting games, Soi Fon has an attack in which she can use her Suzumebachi to, as in the anime, get a two hit KO - if she hits an opponent with the attack twice in one round, they instantly die. The impractical part comes from the fact that the attack is usually slow and hard to hit with, uses up a ton of spirit pressure, or both; and that the first hit usually does no damage. At best, using this attack can serve as a sort of alternate fighting style, as using it can be an all-or-nothing strategy.
In Vampire Savior, the mummy, Anakaris, has a flashy super where he casts a slew of magic spells on the opponent and then crushes him with a gigantic sarcophagus. Definitely his flashiest super, but it's also one of the hardest to execute, and requires you to hit your opponents with an extremely slow, heavily telegraphed, blockable projectile.
In God Hand, the main character has a variety of "guard breakers", attacks that stun a blocking opponent. These range from sobats to flying kicks to haymakers to spinning backfists, but the best guard breaker throughout the entire game... is the basic, boring overhead chop. Plus it somewhat adheres to Real Life in that misuse of the powerful-but-slow moves usually gets you punished.
The biggest offender as far as God Hand is concerned is definitely Yes Man Kablaam, a ridiculously slow and uneffective punch that leaves Gene completely defenseless as he turns his head and smiles to the invisible crowd after the attack. Its only major advantage is that if the punch connects, it raises your Tension bar by a lot, but it's generally not worth it...unless you learn to dodge-cancel out of the recovery.
Instant Kills in the Guilty Gear games do exactly what the name suggests: finish the round in favor of whoever connects one. Most of them look pretty cool, too. Unfortunately, to keep them from being game breakers, they can only be used once, they're extraordinarily difficult to hit with unless your opponent isn't paying attention (you have to switch into a "sudden death" stance, complete with a glowing outline, making these better suited for Mind Games and last-chance desperation attacks), and if you attempt one and miss, you can't use any move that requires meter for the remainder of the round. And because this is Guilty Gear, which places a lot of strategy on meter management and aggression, chances that the Instant Kill you just missed will cost you the match are about 99%.
The IKs in the original Guilty Gear was a game breaker in that a successful hit not only won that round, it won the match and were easy to do, so IKs got nerfed HARD.
Guilty Gear's spiritual successor BlazBlue has this in Astral Heats: gigantic finishing moves that guarantee victory if they connect. Unfortunately, in order to do one it has to be the absolute last round of a match, with the opponent's health at 20% or less, and enough "MP" saved up for two regular specials. Since you only get one shot, it's usually much wiser to just use the two specials. Continuum Shift made it a bit more usable by making several Astrals more comboable, needing only the match point round and only 35% health, but it also now requires a Burst stock. In high-level play Burst stocks are absolutely necessary for character specific combos as well as getting out of very long combos from characters like Litchi, Noel, Taokaka, and Jin. This can, like Guilty Gear, end up majorly biting you in the ass and costing you the match.
Ragna's Devoured By Darkness Distortion Drive is this so much. It's one of the most powerful moves and regenerates a massive amount of health. However, to even use it you need to first burn 50% heat to activate Blood Kain, before burning another 50% to use the move itself. With Blood Kain's aura, never mind the activation animation, it serves as a big Harbinger Of Asskicking that tells sensible players to stay away. It does not help either that Devoured By Darkness is a grab, with the poor range that implies, and Ragna does not have a You Will Not Evade Me move to drag the enemy into reach. All these combine to ensure that it almost never shows up in serious play.
Ragna can use use it to finish a combo. If used before there's too much damage reduction, it guarantees a victory.
Any special move that requires the opponent to hit you to trigger it, as it requires nigh-meticulous timing to pull off.. A prime offender is Fei Long's Gekirinken from Street Fighter IV. One of the most awesome special sequences you'll ever see in fighting games, but the opponent has to hit you to activate it. And if it fails, you lose your Ultra gauge completely. Not to mention, like all Ultras, it begins with a long flashy ultra move animation where Fei yells "Attack me from any direction you like!", ensuring that you'll only succeed in countering newbies who don't yet know it's a counter stance, or fighters who are already in the middle of their attack animation.
The famous Za Warudo combo in JoJo's Bizarre Adventure fighting games, seen here. With the steamroller itself, anyone can see it coming a mile away, it's blockable, Dio can be punished as he jumps up to perform it, and the damage really isn't worth it. But who can resist recreating one of the most badass and infamous moments of the entire series? The Time Stop in the game can be a pain too, since you're immobile for about 3 seconds before executing them and unless you set your SC gauge at max every round, you will have limited time to actually set up your combo. However, enjoy your 66-hit combo if you pull it off.
The same also goes for Jotaro, Dio and Shadow Dio's Time Stop supers. They require a lot of energy to get the most out of and they have a huge windup time and complicated button inputs but done right, they can help you deal incredible damage.
Giorno and Kars's transformation GHAs in Jojos Bizarre Adventure All Star Battle. While both offer new moves, properties, and very increased mobility, it's far too easy for a player to shoot themselves in the foot if their opponent smacks them before the transformation is finished. To this end both characters have a move or two devoted to creating space in order to transform safely; Kars has a kick that sends the opponent to the other side of the screen, and Griono's HHA does the same while also briefly stunning the opponent afterward. Pucci averts this by his entire moveset being geared around getting to Made in Heaven, and Made in Heaven itself being completely worth the investment.
Mortal Kombat brutalities. Flashy, incredibly nasty ways to finish your foes. Also nearly impossible to pull off. They require a sequence of button presses that's near impossible to actually do. It's awe inspiring to see it pulled off though.
For that matter, uppercutting. Takes away one quarter of your opponent's life, tosses them up in the air, and they make a cool screaming sound, but it's really hard to hit anyone with it, unless it's the first battle in the one-player game. It's also impossible for most characters to combo with the move because of the forced delay on a successful hit.
For that matter, Fatalities in general. Sure, the franchise pretty much invented the concept, and they are really cool, but in most games, they and the other finishing moves provide no real tangible benefit to gameplay. The first game was an exception; you did gain bonus points for doing them, and it was also a requirement for unlocking Reptile. (For some time, that meant Sonya could not fight him, because her Fatality required the Block command, which disqualified her due to another requirement. This was fixed in a later edition of the arcade game and all home ports.) Ironically, in many other fighting games that have similar moves (like say, the Soul Series) there is a tangible benefit for doing the finishing moves.
X-Ray attacks in Mortal Kombat 9 are insanely gruesome, showing your opponent's bones breaking and organs rupturing in slow-mo from the brutal strikes. They also require 3 bars of meter, which is also used for the extremely important and useful breaker move. On top of that, many of them are slow and easily blocked or dodged, meaning that the only way to reliably land them was to use them mid-combo, where damage scaling severely reduces their damage.
Mortal Kombat X brings back Brutalities, although they're completely different from their MK3 predecessors. This time, they are performed by using a specific move to take off your opponent's last bit of health. Many also require additional conditions, such as having a certain amount of health left. Since a move which can become a Brutality may not always be the best way to KO your opponent, and even if it is useful you may not meet the additional conditions, they can be almost impossible to intentionally pull off outside of low-level play... but it's SO satisfying when you DO get one off!
The Special or Ultimate attacks from Naruto Clash of Ninja are like this. Especially in the first game the attacks generally did a fair bit of damage but cost all of your charge bar. They're also pathetically easy to dodge, making them all but useless against human opponents.
Many fighting games have super moves that do insane amounts of damage and are awesome to watch, but often require button combinations that only Mr. Fantastic can pull off. One example is Ivy from Soulcalibur, whose telekinetic attack Summon Suffering is absolutely amazing, but the human players who can pull it off can probably be counted on one hand. Not that you won't see it often, but it won't be against a human.
There is actually an Achievement for pulling this off in Soul Calibur 4.
There's also Talim's "Whirlwind Festival" throw: a multi-part, highly damaging throw that requires you to input very odd button combinations in very short windows of time as she performs the throw. Fail, and the throw's animation cuts off at the first part or so and does far less damage.
More on the 'impractical' than 'difficult' side, Taki has a whole repertoire of attacks that involve attacking from the air, complete with screen-darkening and light-burst effects, but which almost never connect because their pinpoint aiming accuracy means that an opponent can dodge them by taking a step to the side.
Another example from the Soul Series are the unblockable attacks. These attacks are powerful and unblockable, but are so painfully slow that even inexperienced players can easily sidestep or dodge them, and counterattack with impunity. Starting and then canceling these to psych out the enemy is the only real use for them.
A few unblockables have some use by having unexpected properties, like Raphael's surprisingly long-ranged unblockable lunge. Of course, once an opponent knows what to watch for, they're worthless.
Unblockable and projectile attacks in many 3D fighters tend to be this way. It's hard to carry the legacy of the Dragon Punch or Hadoken when the opponent can sidestep.
Zombie pirate Cervantes has the unblockable projectile where he shoots the enemy, but he flourishes the gun for so long that only the most idiotic of opponents won't either evade or duck it.
Several of the Final Smash attacks in Super Smash Bros.. Brawl and onwards qualify for this trope. For example, any one that requires a player to catch an opponent will often do large amounts of damage and kill at low percentages to those unfortunate enough to get caught in one and are often very flashy. But, they usually have very short range, making it easy for zoning characters to try and hit the smash ball out of you, and will go to waste if dodged, which is often easy given that the short range most of them have often forces predictable usage of them.
Special mention to Marth's (and Lucina's) final smash. It does 60% damage and an almost guaranteed kill on hit, as well as having an absolutely insane amount of movement range. But, not only is it just as easy to dodge as any other similar Final Smash, but the range is also so large that it's easy to self-destruct with it if you use it carelessly (such as using it in the air on a small stage).
Donkey Kong's Final Smash, a rhythm minigame with a soundwave that grows bigger with each beat that's hit correctly. It takes some time to get its range to anything useful and has no outstanding damage output or knockback. It was even worse in Brawl, where the player wasn't even given a button prompt, so it ended up being luck more than anything.
Lucario's Final Smash in the fourth game. In Brawl, he got an aimable Wave Motion Gun; in 4, he now uses his Mega Evolution. He gains a huge boost in power and knockback and his neutral B Aura Spheres are now much bigger and stronger. The problem is that unlike all other Final Smashes of this sort, Lucario gains no other real benefits. He doesn't gain the speed boost Wario gets with his or the size and range increase Bowser gains with his, meaning Mega Lucario is basically just regular Lucario with more knockback, and is normally pretty easy to just avoid entirely.
Lucario's Final Smash in Brawl as well. While the beam is aimable, it takes a while for it to move, and the charge up time it takes before Lucario fires gives the opponents a good amount of time to start running away. More often than not, other fighters can just go to the corner of the stage, jump out of the way when the beam's about to hit them, and before the final smash can do much damage, it'll already run out.
Then there's Captain Falcon's famous signature move, the Falcon Punch. One of the most damaging attacks in any of the games, but its charge-up time not only makes it extremely difficult to land a hit without proper planning and/or a bit of luck, but it also leaves him wide for attack. Needless to say, when someone does manage to pull it off, it can only bedescribed as awesome.
The true Awesome, But Impractical move is the reverse Falcon Punch and its stronger variation, the reverse Warlock Punch. These moves require that you turn at just the right frame to make Cpt. Falcon or Gannondorf spin around and punch the opponent. Amazing when successfully pulled off, but it takes even longer to get to the actual punch and only does slightly more damage.
Ice Climber chaingrabs border on this. Having the ability to infinite-combo anyone makes them incredibly annoying to play against (the strategy for dealing with them is "don't get grabbed and separate them whenever possible"). Actually pulling it off requires a long series of two-frame timings that vary based on the weight of the chaingrabbed character, having both Climbers close enough together that your opponent can't escape before you begin the throws, not being interrupted by an environmental hazard or other player as applicable, and, because everyone knows that one grab is death, having to deal with the aforementioned strategy just to get the grab in the first place. Granted, it's somewhat easier than it sounds. The "impractical" part comes in when you factor in the mental fortitude required to pull it off two or three times per game (they still can get kills the old-fashioned way, after all)... two or three games per set... and, depending on the size of the tournament, 7+ sets per tournament. And you could have put all the practice you did into Meta Knight instead.
The Home-Run Bat became this in Brawl. In the original and Melee, it had a slightly unwieldy swing time but was still usable much of the time and was a reliable one-hit KO, stage obstacles notwithstanding. In Brawl, it works more-or-less the same way, except the swing time is dramatically increased. It makes a highly effective thrown weapon in every single game, however.
The S-Flag will grant one more point in Time mode and one additional life in Stock if you can fulfill its conditions: Pick up the S-Flag and hold it up continuously for about 3 seconds. However, it disables most of a character's moves when they're holding it, and it's pretty unlikely you'll be able to stand in one place undisturbed for three seconds in a game as fast-paced as Smash Bros. Also, holding up an S-Flag cannot be canceled once started. When they do so, they're vulnerable to any of their opponents' moves, and if it's interrupted, they have to start all over again.
Roy's Flare Blade in Melee and 4. If fully charged, it can OHKO just about anybody, provided there isn't anything blocking their path. However, you can't hold the charge or move during the move, meaning either your opponent has to be holding still for a while or you need to read them really well. And using the move at full charge will do damage to Roy, making it even less advisable.
Some hyper combos in Tatsunoko vs. Capcom are Awesome, But Impractical. Ippatsuman has it the worst, though. His Level 3 Hyper Combo has him summon his signature robot for the player to control. However, all of its attacks are slow, don't do much damage, and can't combo past two hits. He does have 3 unblockable moves in the mode, two of them being telegraphed sword attacks and a humongous unavoidable flash that kills the opponent if it connects. The problem with the flash, however is that it takes ten seconds even hit with the move, the most time of any non-boss character inCapcom vs. Whatever history and can be interrupted by attacking the robot's head during the charging sequence. Finally, in the international release, the time the robot stays on the field decreased by a large margin and ate every bit of meter when used.
Valentine's "Forbidden Procedure: Rebirth Ex Machina" can revive an ally or damage an enemy, but the damage is not that much better than her level 1 Blockbusters, and the meter would generally be better used towards finishing your enemy since by the time you gain 5 bars and have a dead character, the fight should be nearing its end.
Double's "Megalith Array" has her turn into a Bullet Hell spewing Moai statue which can potentially deal insane damage, but she turns back if she takes even a single hit, potentially wasting your entire stock of meter if timed improperly or if your enemy is skilled enough, which makes the move Difficult, but Awesome.
Big Band's level 5 is confusing to pull off, requiring you to play the game's opening Leitmotif on his trumpet, which leaves you wide open to attack since only the first 5 honks will hit an enemy. And to make matter worse, Big Band's level 5 itself doesn't do any damage, but rather stops time for the sake of giving you time to play without being attacked, or use to unleash Satchmo Deathblow, which can be used much easier (but in turn dealing much less damage) by taunting the enemy and then hitting with his ground level 1.
In the third installment of the Final Fight franchise, the enemy named Hunter has an extremely powerful overhead strike using the baseball bat strapped to his back that takes out 80% of your health in a single hit. Impressive for a garden variety mook. However, the attack has a long delay before he unleashes it, so it's easily avoided or interrupted merely by hitting him.
Deathblows in Mutation Nation. Basically a screen-filling Smart Bomb that severely damages all enemies and knocks down the ones that manage to survive, which come in four flavors of varying power (with the most powerful one actually being able to hit some enemies twice.) However, it requires the player to stand still for almost 2 seconds to charge it, and the charge is interrupted when you're hit. And while you can hold the charge after you've finished and even move around while doing it, it doesn't last long before you'll lose it and have to charge up again. Effective against bosses and mid-bosses, but when you're dealing with large groups of enemies it's too difficult to pull it off for it to be worth it (and the lower damage ones like the Spirit Attack just aren't worth it, period.)
Some SNK fighting games have Super Moves (or Desperation Moves, whatever it calls) that requires the user to input additional commands in the Super Move. Super Moves are already hard to pull off, but adding those additional commands just make them more impractical. However, the damage and visual effect is awesome though.
Athena Asamiya'sPsychic 9 is a good example. It requires 2 special bars and you need to input a series of commands in a certain pace as she's attacking. Fail at the inputs and she'll be left wide open, but doing it right will make a long, flashy combo. It's made worse in that her other 2-bars super move, Shining Crystal Bit SDM, does not require additional inputs and does almost the same damage as Psychic 9 in one single hit.
Killer Instinct newcomer Omen possesses a 100% potential damage command grab called Demonic Despair whenever he has all of his Shadow Meter filled, and not only does it do ludicrous damage, it also looks really badass. However, it is plagued with so many problems that almost no Omen players use it. To start, Omen needs his Shadow Meter to perform the mixups essential to his offense, so using all three of his bars in one go is already a risky proposition. Also, the move has 24 frames of startup,note For reference; Jago's light Tiger Fury, one of the most punishable moves in the game, has 27 frames of recovery. and the animation doesn't resemble any normal of his, so the attack is incredibly telegraphed. Another problem; if the opponent Combo Breaks Omen's attempt to cash out the damage and finish his opponent, then they get all of that health back, since Demonic Despair deals potential damage. And to top it all off, even if you do land it, it sends the opponent flying across the screen and leaves Omen taunting in place for several seconds; more than enough time for an opponent to take a breather and assess their options. On the plus side it can be cancelled into from blocked normals, so it has its place catching opponents who like to block.
The Kaioken, Kaioken x3 and Kaioken x20. Each of these attacks boost up your stats, but they will eat away at your Stamina and prevent you from building up Ki, thus you won't be able to slap your opponents around with constant Super attacks. As well, the higher up you go in levels, the faster the Stamina drops like a rock. Saiyans have it easier as you can pick up Super Saiyan and Super Vegeta relatively early on and they're easier to maintain than the Kaioken. However, the other races have to make due with Kaioken and if you have to, just use the basic Kaioken unless you've built your character around having a plethora of Stamina.
Charge up Supers like the Spirit Bomb can easily be short circuited by moving listly to the left or just decking that person in the schnozz.
Road Roller hyper combos are as bad as they are in Jojos Bizarre Adventure Heritage For The Future. All of them require that the user have enough time to jump out of the screen to start and then actually score a hit (which can be dodged or blocked). Team S.M.R.T.'s Homer has "Road Roller D'oh!" which has its own problems: he needs to be in "Beer Riot" mode (which shaves of half of his current health), lacks his Hell Candy Bar to stun opponents, and has more reliable moves such as "Touch of Death" (Homer turns into the Grim Reaper and pokes them) that require less effort to set up.
In almost every Street Fighter game, Akuma's Raging Demon is this. A devastatingly powerful super that eats away half of your opponent's health bar, leaves them in a knockdown state, and provides you with a badass win pose◊ if you win with it... Too bad it requires a strict, awkward sequence of button presses to make it happen. And not only that, but it's nigh-impossible to set up appropriately if you aren't incredibly tight with your execution.