Any rocket infantry in any Command & Conquer game. Individually weak and vulnerable, but just strong enough against tanks to put a dent in yout army.
More specifically, Flamethrower infantry in Tiberian Dawn. These little shits were fast and as such would dodge attempts to squish them, they were damn cheap to train, and did ungodly amounts of damage to almost every unit/structure in the game. In the later GDI missions, the AI would spam these bastards on a frequent basis, and often melt a few of your lighter vehicles (and typically, these would be your most effective anti-infantry vehicles) during the imminent scuffle. No wonder they were nerfed in Red Alert.
In CNC3 and Kane's Wrath, the Scrin basic infantry unit is the Buzzer. These are the cheapest infantry and train quickly, but are absurdly lightly armored. However, they can kill another infantry unit nearly instantly and can clear garrisoned buildings.
In Red Alert 2 and 3 there's the Terror Drone. Nasty little buggers that can tear a vehicle up inside out. Their easy to kill but can destroy a battalion of vehicles left unattended. The only way to get rid of them is with service depots, repair drones or destroy the infected vehicle before the Terror Drone does.
Initial scouting probes are the most annoying worker units to deal for the enemy since they can also start building assimilators and pylons in opponents' bases easily.
And let's not forget the Mutalisk harassing. There's a reason stacking air units became an Ascended Glitch.
The worst was definitely Lurkers, especially if you were playing Terran. They would attack you by hiding underground, where you couldn't hit them, and doing high damage to everything near them. The only way to kill one was to use a detector unit, Terran has two detectors. One being the Comsat Station add-on to the Command Center allows you detect an area anywhere on the map for 50 energy. The second being the Science Vessel, which is an air born support unit, after you research Irridate it is your best friend against submerged lurkers and is advisable to have one or two Science Vessels in every large army that you field against Zerg
The Queen, though it has no attack, has 3 abilities that are frustrating. Its parasite ability lets the enemy see what the unit sees, its ensnare ability covers units in green slime and slows them down, it also reveals cloaked units caught in its blast, and it has the Spawn Broodling ability which kills the target unit and spawns 2 Broodlings.
Battalion Wars has Gunships. In both games, you generally get only Anti-Air Vets to kill them with. If you get Fighters or Anti-Air Vehicles in a mission with enemy Gunships, enjoy the time you get with this, but do not assume your ground forces are safe. If you want to manually destroy Gunships with the Anti-Air units, you have to focus your vision on the skies instead of worrying about anything on the ground, like freaking Heavy Tanks. Mercifully, units that have been commanded to move to a location with the Y button in the first game will be active in attacking (most noteworthy in Road to Xylvania, which despite four infinitely respawning Gunships can be done with a Perfect S-Rank without the Battlestation), but this is not possible in the second game, which is part of why it is worse about Gunships, because you'd want your units in Follow Mode where the Gunships will end up shooting your following foot soldiers. The other part of the reason? A higher percentage of maps with Gunships give you only AA Vets—which do lousy damage against them for the fact that they lack defensive power to begin with—to deal with the buggers, and if you have anything else that can really do so, apply one of the following:
Shipyards Ablaze, where you'd have to learn how to control the Fighters or else they could wander into the AA Vets' range
A level where you're using Frigates, which could just as easily have to deal with submarines. (Mercifully, Frigates are fast enough to evade the battleship fire, but this results in Character Select Forcing because of the AI's stupidity.)
A level with Anti-Air Vehicles... and either you get only one of them for the enemy force to destroy, the enemy force gets a Battlestation that can easily destroy the vehicle, or both.
The first part of Under Siege, which for similar reasons to Road to Xylvania with Y button spamming is easy. (Under Siege is Co-Op's That One Level for a No Casualties Run but for different reasons relating to the second and third parts.)
Late enough into Apocalypse that further Gunships will only try to threaten the objectives.
One of the messed up Assault maps.
The Dawn of War II campaign has the Tyranid units Rippers, Hormagaunts and Termagaunts. Pathetically weak, but their large squad size and tendency for half a dozen squads to attack at once makes them a constant annoyance, while their tendency to be meat shields for more powerful units makes them potentially deadly. Fortunately at higher levels they can be killed in about one shot, with Avitus slaughtering entire swarms in seconds.
The Imperial Guard, one of the most common tactics in Dawn of War is to Zerg Rush with dozens of IG Guardsmen.
Speaking of the first Dawn of War, the Harlequin. Up to three can be built at any time and their Dance of Death ability makes them turn into the She-Fu equivalent of a Hyper Destructive Bouncing Ball, stunlocking entire infantry squads. Their other ability, Harlequin's Kiss, instakills the targeted soldier and deals splash damage to everyone nearby. The only thing that keeps them from becoming Demonic Spiders is the rather low health... with commander-class armor. In other words: they can still be taken down by an absolute Zerg Rush of ranged infantry - or simply just send in a tank. Or even better, as many tanks as the Arbitrary Headcount Limit allows and follow up by destroying the Eldar HQ before it respawns.
The Chrysalids from UFO: Aftermath. They can't actually harm you, yet they are among the worst things to meet in the entire game. Why? Because their only attack is a stun, that has longer range than some sniper rifles and a high enough duration and rate of fire to be able to single-handedly lock down your entire squad if you let them. And that's when they're alone. Should you run into three or more you might as well just give up and load.
Europa Universalis has any poor country with a lot of winter in it. Tibet and some of the russian steppe-nations in particular. Sure, they can only raise a few thousand men. Too bad you still need at least 5000 men to besiege their fortresses and they can only support 2000... You can easily lose hundreds of thousands of men besieging piddly tax-base 1 or 2 provinces. Especially in winter.
In AirMech, a MOBA game, The AI controlled airmech will fly away from units they're supporting the push just as you're coming down on ground mode to get them.
The Arm light tanks and bots in Total Annihilation. Their primary weapon does a decent amount of damage for its cost and techlevel, and while as the game advances their threat level gets closer and closer to zero, they remain an annoying nuisance for a while after game start and in the first minutes can even overwhelm a Core player that hasn't yet brought up fixed defenses.
On the Core side, you had the Can and it's more powerful counterpart, the Sumo. Both do reasonable damage, but they move slowly and can absorb surprisingly large amounts of damage. Short range is all that stopped them from being much worse.
Crusader Kings II has Viking raiders, introduced with The Old Gods. Small bands of raiders can disembark into any province along the coast of the sea or a major river, steal gold from one of your counties, then hop back on their boats and sail home (or, more vexingly, to another lightly-defended province) once you've assembled an army that can actually deal with them.