It depends on the game, but instant death/petrification is always irritating. Time to do a rundown!
Pokemon: Every status effect. God knows I'll inflict a status effect on an enemy and watch as it does nothing to hinder them, but I'll be screwed over every time. And don't bother curing it; you'll just be inflicted by the status effect again the next turn! In Black and White, I had a Whimsicott who had speed priority on stun spore and leech seed, though.
Shin Megami Tensei: Charm effects. Berserk can also be very, very bad depending on the enemy I'm facing. However, losing control of your main character is absolutely devastating. I have killed myself once via Berserk as the enemy had reflect phys. Boy was I pissed.
Baten Kaitos: Burning quickly racked up devastating damage to your characters as it ticked down in real time. Confusion made it impossible to line up combos, and you'd better believe the final boss could inflict every status effect in the game, with these two statuses being very common. And there was no on-demand cures, so you'd have to wait for curative items to appear in your deck. Thankfully the sequel turfed most of the status effects.
Final Fantasy: Zombie is the worst, but I actually find status effects in FF games pretty easy to cure. FF8 made status inflicting easy by junctioning statuses to weapons, too. Bind in the Tactics games is quite a hindrance but again, cures are pretty abundant.
Ragnarok Online: Stun. Stun stun stun. My wizard could do jack shit in most large scale battles because dancers and gypsies everywhere would leave her locked in stun status. 100 vit made you immune, but wizards can't afford to run that kind of stat build without destroying their capability to deal damage. Stun interrupts all of your movement, disables the damage-dealing ability of any spells you were in the progress of casting (so say you've got a storm gust still on-screen... now it deals no damage and stops dealing hits), and reduces your ability to dodge hits to being non-existent. The duration is short, but the effect makes you completely helpless.