Put it this way... I have a M dual clutch transmission (the real deal paddle shifter) in my M4 and I almost always drive in auto mode because I find no joy in shifting without a third pedal. It just isn't fun... In a 184hp compact suv, its even less fun (my own opinion).
There is definitely a difference between driving in sport mode and regular if you aren't shifting gears yourself. It isn't all about 0-60, since mashing the gas to the floor will hold the gear longer in either mode. When you're cruising at, say 35mph, sport mode will keep the car in a lower gear, so the car will feel much more responsive since it doesn't need to downshift if you're trying to pass. It's a tradeoff though... In sport, the car runs at higher RPMs, so MPGs go down and overall the car is more "buzzy" as you say. Sometimes, I just want a comfortable easy going ride to get from point A to point B.
Does it make the CX-5 faster? No... maybe not from 0-60, but there is no doubt that it makes the car quicker, and more FUN to drive.