I think of three well-used examples of sex. The first is Fahrenheit (or Indigo Prophecy if you happen to live in a country where your government hates freedom), where the sex scenes represent the intimate emotional connection between characters. It's not a focal point of the game, or even something you'll definitely come across, but it's there and it contributes to the game.
The second is in one of Yahtzee's rather sublime adventure games, specifically 6 Days a Sacrifice. It takes place shortly after two characters have taken shelter from a world slowly turning into hell, and decide to comfort one another by sleeping together. It solidifies the relationship between the characters and makes us care more about the NPC participant, and it contributes towards the story in a meaningful way.
Mass effect did it well too; it was the culmination of a sustained relationship between characters.
In all cases, it was good because it was what people would, and DO, do in real life.