Sue is most common in fan fiction.* You know, where the author's character is best friends with Luke Skywalker, Jayne Cobb, and Jean-Luc Picard. I don't think there's anything wrong with Mary in these contexts, but if you're trying to get published, you want to do away with Sue.
I don't think real Mary Sues appear in fiction as often as some say they do, but they do happen.
How do you avoid this? I mean, I [try to] make a living out of writing cool characters who do awesome things. And basically every character draws from myself in some way. How do I keep my super-cool pirates/ninjas/mech pilots from becoming wish fulfillment?
Here are some ideas:
- Give them a flaw. Not an adorable non-flaw like "clumsiness," but a real flaw like "hell-bent on revenge and too proud to admit it."
- Support their awesomeness. Why are they the youngest, most clever assassin in history? Did they train harder than everyone else? Were they kidnapped at birth and brutally trained to be a killer by a father figure who never loved them?
- Make them fail. It's even better if it's their flaws that cause them to fail.
- Don't let them be the best at everything. Have other characters be better than them at some things, both friends and enemies.
- Give them likable enemies. Not just spiteful, ugly step-sisters, but characters whose opinions the reader can respect.
I don't think Mary Sue appears as much as the internet thinks she does, but it is something to watch out for. If you think you've got a Mary Sue, you need to cruelly examine everything about them and everything they do. Mess them up, make them fail, and ask why they are the way they are.
Who's Mary Sue in the end? It's you (and also Steven Seagal).
* The term 'Mary Sue' was coined by Paula Smith in 1973, when she wrote a parody Star Trek fan-fic starring Lieutenant Mary Sue, the youngest and most-loved Lieutenant in the fleet. You can read it here (page 25). It's kinda hilarious.