"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.
Most of the weapons the US Army uses at Yonkers. They use "Shock and Awe" weaponry against the zombies, which is ineffective against them, in addition to a combat system for infantry that allows you to hear and see everything your buddies are seeing. This does nothing for morale when it is stated that the aerial recon showed a horde of zombies millions strong marching out of New York when they were already having a lot of trouble against the front ranks. Also, a soldier is ambushed and torn apart outside a house, and his camera gets all of the action. This stuff, along with a plan that ignores much of the good terrain, written by armchair generals who had spent decades preparing for World War III with the Soviets, allows the army to be slaughtered. This happens all over the world, apparently—South Korea had a similar incident at Inchon.
MHTELS (Mobile Tactical High Energy Laser) and Zeus, anti-missile and anti-mine technology, respectively. Terribly inefficient for anti-zombie work, as they take forever to kill organic targets, but make for great propaganda footage, because they make heads boil apart.
Also, the guy early in the outbreak who fights the zombies by strapping on some rollerblades and attempts to mow down zombies with a meat cleaver attached to a hockey stick. He doesn't make it home, as he gets tripped by a zombie, is dragged into a sewer by his ponytail, and then gets mocked by Paris Hilton after a news camera catches it.
The tactic used by the US Armed Forces in the American reclamation is almost this: two lines of infantry, stretching the entire continent, moving in synchronicity. The line wasn't in step, for obvious reasons—differing terrain and weather, places that required a grouped offensive, et cetera. Todd Wanio discusses having to wait weeks at a time before bad weather stopped hampering them and mop-up operations in places that the line missed.
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 Reynard Cycle: The Muraille, a series of fortresses connected by a wall over a hundred miles long. It's been breached so many times that it's been completely abandoned.
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.
Invoked in The Stars My Destination: Mind-activated teleportation has made all forms of travel and communication apart from teleportation, couriers and spaceships obsolete. This means that rich people compete to have the most awesome methods of transportation to display their wealth ("I can spend huge wads of cash on things I don't need") and power ("I don't wait for other people, they wait for me!") One character arrives at a party in lavish style by driving a steam train complete with a team of navvies laying track in front of it. Another character has a manual telephone switchboard in his office, not because he needs it, but to prove he can put up the cash for an expensive piece of complex, difficult to repair electronics, and the required operators and repair staff, to do something he could do himself with minimal effort.