"A flashy feature that has limited usability for victory."
Discworld: Death used to ride a fiery steed, but it set fire to the stable. Then he got a skeletal horse, but bits kept falling off. Then he got a Badass NormalWhite Stallion, and called it Binky.
In Snuff, Col. Makepeace retired from the Pseudopolis military's Light Dragons, which attempted to use the notoriously-explosive swamp dragons in warfare. It never worked, and is the reason his memoirs are titled Twenty-Four Years Without Eyebrows.
This is the entirety of Mustrum Ridcully's attitude towards magic. Ridcully is ludicrously powerful, but Discworld magic is unstable and dangerous to everyone in the vicinity. Also, he figures that anything he can't take down with his staff (six feet of bog-oak, with arms that box trolls for fun swinging it) isn't going to be deterred by magic either.
Bryn's Wolverine Gauntlets from Raised by Wolves. The claws are silver, and extend and retract via a quick twist of the wrist. The only time they're used in combat, the enemy figures out how they work and neutralizes them by grabbing Bryn's wrist and twisting them to the retracted position. Made more annoying by the fact that Bryn was already carrying around two perfectly good silver knives when she got the gauntlets.
In Dark Heavens, Emma is unexpectedly able to generate an ultra-rare black chi that can instantly annihilate demons, or turn them human... sometimes. Other times, it does nothing at all. Emma eventually decides that it's too unpredictable to be useful, and stops using it... for now.
In Gathering the Enchanted, it would be Calynn's legs suddenly bending backwards. This would realistically cause her to have to crawl on all fours due to the sudden change in her lower body.
In On Basilisk Station, Honor's ship was refitted with a shiny new weapon that could destroy the protective force fields on other ships. Unfortunately, it had a relatively miniscule range of about a hundred thousand kilometers, and required the removal of most of the ship's conventional missile and beam armanent, making it incredibly ineffective at typical combat distances of up to a few million kilometers. She manages to make use of it, but would have been much better off with the original Boring, but Practical armanent.
Admiral Sonja "Horrible" Hemphill was notorious for turning out this kind of military advances. Undeservedly, as it turns out, since any of her advances that weren't in this category were classified so all most people saw were her failures.
In The Elenium, the undead soldiers created by Otha. While they look like an undefeatable regiment of unkillable warriors in Scary Impractical Armor, there turn out to be two crippling problems with them: 1. Their armor really is impractical. The guys making it didn't understand armor had a purpose beyond looking scary, so they made armor that looked really scary but was restrictive, didn't deflect blades properly, and was too thin. 2. Otha is a moron. He has the power to raise the dead, but no idea what to do with it. The soldiers are set to guard the stone they're standing on, and that's all. The heroes win by just walking around them. Then because it didn't occur to Otha that his soldiers should be selective in their targets, they chuck a rock at one soldier causing it to enter the square of another, and the Disaster Dominoes set the entire regiment fighting each other.
The Aeyrie, batlike winged humanoids in Laurie J. Marks's Children of Triad trilogy, are described as such—by one of their own, no less—in the second book: Between the weight of the musculature required to get something roughly human-sized aloft, and the hollow bones and general frailty required to compensate for said weight, they're "too heavy to fly easily, yet too light to do anything else."
That might be true considering they're mammals (the very largest bats are only as big as medium-sized hawks), but Argentavis magnificens, Harpagornis moorei, and several pterosaurs including Pteranodon and most of the azhdarchids, were roughly human-sized, sometimes significantly bigger (Quetzalcoatlus northropii is the size of a small giraffe, at least in terms of dimensions), and they all flew.
In The Pale King, David Foster Wallace explains how awesome the Peoria REC looks as you approach its parking lot...except that the design of its road and structuring makes for horrendous afternoon traffic.
In essence, the baronial splendor of the REC’s grass was a testament to the idiocy and hassle of the whole thing’s planning.
Callandor in the Wheel of Time is an extremely powerful Amplifier Artifact, but it was built without the normal safeguards in place on artifacts of its type. Both times Rand uses it at full power, he ends up with temporary delusions of godhood and starts doing insane things. It's later revealed that Callandor's flaw is actually a deliberately designed trap, and proves crucial to Rand's plan for winning the Last Battle.
Discusses how one man strapped roller blades on his feet and lunged himself at zombies with a meat cleaver attached to a hockey stick. It didn't end well...
The whole in-universe basis of the Battle of Yonkers. All this awesome firepower wasn't practical in-universe against the zombies.
Also some of the pre war tech such as the MHTELS (Mobile Tactical High Energy Laser) which could incinerate a zombie, but given that it's a pain to set up and wildly inaccurate, its more impractical than useful. It does make for some pretty neat propaganda footage, though...
The LIGHT annihilation device in The History of the Galaxy series. Originally developed as part of a project to build an Antimatter drive, it was adapted into a projector that fires a stream of anti-particles at the target. The problem? They never figured out how to control the yield. The first battlefield use resulted in the loss of both fleets, as well as the weapon itself. Oh, and the enemy had more fleets at their disposal, while the side with the device was left with a grand total of 8 warships out of hundreds. The weapon is later placed on their flagship-class cruisers, taking up a good fifth of the size (a flagship cruiser is about 7 kilometers in length). It's almost never used in battle due to unpredictable results and is mostly there as a show of force. Think Death Star, but even less useful.
The Iron Throne in A Song of Ice and Fire. It's a throne for the King of the Seven Kingdoms to sit in...but it's made entirely out of swords and is reportedly extremely uncomfortable to sit on. And dangerous!
Han Solo at Star's End's Corporate Sector Riot Gun. It can fire a constant stream of energy (used for mowing down a crowd as "crowd control") that can clear a room of combatants in a hurry. However, it has very poor aiming characteristics as Han Solo found out when trying to shoot at ESP Os using its single shot mode. It can only hit effectively on "constant fire" which dramatically increases the chances of friendly casualties in a pitched battle. Contrast with the obsolete blaster carbine (no designation mentioned) in the same book that was "rugged and extremely durable", "with simple telescopic sights", with "no moving parts" and "if left against a tree in the jungle, would still be fully operable ten years from now."
In Destiny's Way, Han uses this argument as a Take That against the Empire (and the author uses it as a Take That against the Bantam books, sometimes called the "Superweapon-of-the-Month Club"). An Imperial Navy officer claims that, were Palpatine still around, the Yuuzhan Vong wouldn't stand a chance because Palpatine wouldn't pussyfoot around and deploy the Imperial Fleet at the first sign of trouble. Han argues that the Empire wouldn't do anything of the sort and instead try to beat the Yuuzhan Vong by building some Awesome, but Impractical superweapon to scare them into backing down. It didn't work on the Rebellion, and it wouldn't work on the Yuuzhan Vong.
The Lancer-class frigate (seen in Dark Force Rising and Rogue Squadron) is a 250-meter warship bristling with quad laser batteries designed as a counter to the starfighter-heavy Rebel/New Republic fleets. Unfortunately it required too many crew and proved too expensive for wide deployment, and didn't have any way to defend against capital ships, so most admirals scorned it.
Grand Admiral Thrawn does eventually find a very good use for such ships by pairing them up with the equally over expensive Interdictor cruisers and using them to ambush small convoys that normally rely on starfighters for defense.
By the time of the Legacy comics ships of the scale of the Executor-class star dreadnought are seen as this. Without a tax base the size of a galaxy, they cost too much to operate.
Offensive magic in the Harry Potter universe generally falls under this trope. While magical healing is Awesome But Practical; the Killing Curses, Forbidden Curses and even more mundane magic attacks tend to pale alongside modern firearms save for situations where the target are immune to anything else. Rowling herself did note that a muggle armed with a gun will likely take down even an experienced wizard armed with a wand.
The fact that even Avada Kedavra is only single-target means that the entire wizarding world is childishly outclassed by a technology dating to the Renaissance, namely "grenades". That could have to do with wizard culture preferring duels, though. It's the mundane aspects of the wizard world which are bizarrely less practical than what muggles use. Like kneeling down at a fireplace and sending your head through it for a conversation instead of simply using the phone. This is even lampshaded at one point concerning the use of owls in the ministry of magic, due to the problem of owl droppings. Enchanting the missiles into paper birds which fly to the right place instead is certainly a solution, but a computer system would be an even better one.