"Drive" mode is for normal driving, and will give you the best gas mileage. You should use this mode most of the time.
"S" mode stands for "sport" or "secondary" mode. It tells the transmission that you would like higher RPMs. This is useful for times when you know that you'll need a lot of power, like when you're about to pass somebody. It's also useful for slowing the car down when you're going down a steep hill.
Be careful not to leave your car in "S" mode for a long time, or your gas mileage will suffer.