"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!
Justified Trope. Aegon the Conqueror (the first king of Westeros, who commissioned the throne) wanted his successors to never forget that a king's duty is a burden, not a privilege. Hence a throne that always hurts you while you sit on it. Too bad nobody seems to get this.
Harrenhal is another example. On the face of it, it's an awesomely solid defensive construction, most of which has managed to stand the test of time — if rather badly scorched and battered. However, it's also a massive ode to hubris writ in stone. Even if it hadn't met ruin-by-unforeseen-dragons trimming its available living space a fair bit, it'd still have been an impossible castle to supply, maintain and run properly due to the sheer scale of the damned thing and thanks to number of bodies needed to keep it going, let alone anything else. The Riverlands and the Iron Isles would probably have been beggared in just a couple of generations trying to make it work at its peek. As it is, even in its partially-used state, it's still The Millstone around the neck of any poor, unsuspecting Family that gets given it as a "reward" for services rendered. The Whents are only the latest Family in a long line of Families to go under primarily due to its financial and logistical weight making them sitting ducks in the Game. Which, in combination with the burning backstory, is why the common folk (and a few not-so-common) consider the thing cursed.
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 in favor of the old standby of TIE swarms. 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. 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 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. Somewhat justified due to the "magic is an EMP" subtype of Magic Versus Technology being in play: you have to choose between the advantages of using tech and the advantages of using magic in any given area. Want to teleport to work and skip the commute? Then you'll have to deal with sharing the elevator with a bunch of enchanted paper airplanes, because email won't work.
Ursula K. Le Guin's Always Coming Home features a post-industrial society where most societies manage without advanced technology. One expansionist state decided to look up ancient weapon designs on the Internet (there are AI's maintaining a version of it - in a book published in 1985). Since their society has a religion based around condors, they make a few military planes. However, it's a Post Peak Oil world, and they find out rather quickly that it's very hard to expand when all the food has been converted into biofuel...
Kaladin's spear katas in The Way Of Kings. He himself admits that they're only designed to stretch the muscles and get you used to handling a spear, and would be totally useless in an actual fight. On the other hand, the Awesome part proves to be of some practical use, as demonstrating his Master Spearman skills and his shiny new Surgebinding powers helps cement the loyalty of his followers.
The Star Risk, Ltd. team once tried to hang onto a destroyer they bought and crewed for one of their jobs, but then one of their accountants took Freddy von Baldur aside to explain to him that they couldn't afford to maintain it if it wasn't earning any profit.