The biggest benefit to Sport mode is the increased throttle response and sensitivity. With Sport mode on, you don't have to press the pedal as hard to get the car moving. I think the best way to use Sport mode is to use it while also using manual shift mode. Sport mode with the auto sucks, especially in city driving because the transmission will stay at 3k rpm when cruising at 45-50 mph. (If you are doing canyon runs however, I can see it being beneficial to those who don't like to use manual shift mode.) However if you are driving in manual shift mode, you can simply up-shift to 5th or 6th for cruising. I always felt the throttle mapping/sensitivity on the CX-5 was a bit lacking, but Sport mode and manual shift mode changes that. I actually enjoyed driving the CX-5 today in Sport mode and manual shift mode. The first 4 gears are quite similar to the MS3 so I can use the same shift points, which is a bonus. Accelerating off a stop light with Sport mode on is now much more fun, at least for me that is.
I have a question on it though that I'm hoping someone can answer. If you are driving in Sport mode with manual shift mode, is there still any benefit to kicking up the "go fast" switch on the accelerator pedal? I mean in this setup, the car won't downshift since you are in manual shift mode. And you are already in Sport mode, so I don't know if there is still any performance to be gained by fully stepping on the accelerator pedal.