The sport mode just lets the engine rev a bit higher. It up-shifts a bit later and down-shifts a bit earlier (it will actually brake the car a little bit because of the down-shifting when you're slowing down).
If you're driving moderately hard then the regular mode will adapt and will up-shift later than usual, so the difference is subtle. The response depends on the gas pedal input.
Manual mode is much more fun than either regular or sport though