Nothing much, you may find it drops a gear and gear changes become a bit more "sporty" as it tries to keep the engine in the power band but other than that it's about it. I never bother to be fair, as it just uses more fuel.
I have found in heavy traffic that sport mode uses less fuel. It is much more keen on downshifts, so is using more engine braking and therefore less fuel. For anything else I completely agree, it drinks like a fish!
It's still a lot more fun though!