Side Quests is in an interesting topic. I had to think about this for 45 minutes before writing a reply:
I tend to avoid side quests that makes me waste time. If they don't have interesting characters that grab me or great stories - Those types of quests that usually pushes you out to the edges of the game world to "waste time" aka extend the time spent, to get more time consumption out of the game before completion. It's the type of quests you often find in games like Final Fantasy, and such.
I tend to not care about these. And it's not the rewards. I would rather not have the best weapon in the game, if the side quest is a 30 minutes walk, while killing tons of generic things along the way, in a desert.
So my contradiction comes into play when we're talking MMOs, because in that genre, that's just the way the game is played, as sad as it is. Let's hope GW2 and SWTOR can change the way we quest, and turn the side quests into hundreds of hours of great main quests.
So the side quests I do love? Games like DAO and ME2 stands very tall for me. Because they are about character development and storytelling. They are interesting, because they have drama. I have already spent many hours, killing/exploring/looting/lvling... I don't need an extra sub level of that. Mass Effect 2 in particularly, had this great pace between action paced combat and story.
Even in a dream scenario it would not have worked so well, if Bioware had just given you all the story without any gameplay - You need the struggles(the gameplay shooting your way through shit) before you can have the next piece of story. that's how their chain works. story set up > struggles(fighting) > more information > struggles(fighting) > character evolving(lvling up and new gear) > struggles(fighting) > conclusion to a story element... and so on.
There is also the type of side quests you saw in Majoras Mask. That game was absolutely incredible, because even without voice acting it gave you some of the most freaky, yet atmospheric NPCs I have ever seen. The mask guy in the beginning - the weird dudes at the ranch, the creppy old man and his daughter living in the hut engulfed by zombies... I mean... When I played that, despite characters not having lots of dialouge, I somehow made up the backstory in my head for lots of the characters, and that was fantastic. Majoras Mask had wonderful side quests. The creative and bizarre world made me compelled to explore, more than any Final Fantasy game I have tried.
Usually when I play a final fantasy game, I don't feel anything when I am asked to go out hunt for some legendary creature. The NPC is talking like a robot, and looks like a generic dude I saw 20 times on my way to the Inn - It's oppesite of epic and interesting, and just hurls meh, for me.
The answer to Side quests is not only, story/characters/atmosphere to make the player care - It's simply also respecting their time. Don't ask me to go half across the world map to open a jar, guarded by a stupid looking creature by tentacles. FFFFFUUUUUUUUUU