It raises the shift points and disables the automatic upshift when you're in autostick and hit the rev limiter.
I use it quite a bit. In normal mode, the combination of shifting at low rpm plus holding onto higher gears rather than kicking down, on top of the ECM trying to hold onto MDS as long as possible causes a lot of lag/surge when changing speeds. You get on the freeway, and just as you're about to roll on more throttle in 3rd it shifts into 4th, dropping you to about 1500 rpm and bogging down, so you put your foot in it and it lags, then kicks all the way down to 2nd, shooting you forward loudly, rather than smoothly. Same thing for trying to take advantage of openings in the next lane - lag/surge, making it hard to slip into small openings confidently.
In Sport mode, the default shift point is higher, plus it seems to rise faster with more throttle. It kicks down quickly with little bit of throttle, allowing you to respond faster to holes in traffic. When driving a curvy road quickly, it keeps you in a lower gear so you can use engine braking and also get on the power more easily and smoothly coming out of the turn.
Tooling around town, or in traffic, you don't need it, so I turn it off.
I'd drive in Sport Mode all the time....... If it would shift into overdrive at 45/50 MPH under light throttle, as it is now I have to leave Sport Mode once I get up to speed on most roads around here or suffer a 4 to 5 MPG loss by cruising around in 4th. And that's just too much button pressing so 99% of the time I don't use it.