There is potentially a lot going on between these modes, and learning what they're doing can help you find what the best mode is for you.
Before we start, if you're new to all this, I recommend checking out "HDTV settings explained" so you get the lingo.
The basic rule of thumb is that the Cinema or Movie mode is going to put the TV's settings as close to what the director or content producer intended. Sports, Vivid, or Dynamic might create a "punchier" image at first glance, but this actually "adds" things to the image the director didn't intend to be there. You might like one or the other better, but it's good to know what's going on, and why certain settings may not allow your TV perform its best.
There are five main settings that get adjusted by changing the picture mode: Color temperature, backlight, motion interpolation, gamma/contrast enhancers, and edge enhancement. Each changes a different aspect of the picture.
Color temperature is the "color" of white in an image. Know how some light bulbs look bluish, while others look warmer and reddish? Same thing. The Sports and Vivid modes go for a cooler, bluish white as it appears to "pop" more to the eye. Cinema and Movie go for a warmer color temperature. Technically, the warmer color temperature is the correct one, as it's the one that was used by the content makers while recording and editing the TV show or movie. At first glance, it will appear very red, but this is likely far more accurate. Check out this article about TV color temperature, and why it matters for a full description and example images.
The backlight is an easy one to adjust and see the result. The backlight, on LCDs anyway, increases the overall brightness of the TV. It's key to know where this control is, separate from the picture modes, so you can turn it down at night. Eye fatigue from watching TV is often from excessive brightness. Check out "LED LCD backlights explained." Plasma TVs don't have a backlight, so if you're changing the picture mode on a plasma, it's likely the contrast control. Samsung plasmas (as shown in the image above) also have a control called Cell Light that merely limits the maximum light output. Neither of these are ideal, which we'll get to in the next section.
Motion interpolation is what the TV does to fill the extra frames required by the faster refresh rates of 120 and 240Hz LCDs. In Sports and Vivid mode, there's likely a lot of the Soap Opera Effect. In Cinema or Movie mode, there might be less (or none). Plasmas don't need higher refresh rates, though most modern plasmas still have motion smoothing processing that causes the Soap Opera Effect on them as well.
Gamma tweaks and other contrast enhancers are difficult processes to describe. Essentially they adjust the dark and bright areas of the image on the fly to make the TV seem like it has a better contrast ratio. These typically don't do much, and can cause some scenes to look too bright or too dark. We turn these features off when reviewing a TV.
Edge enhancement is what you can see if you turn the Sharpness control on your TV all the way up. See how everything has a sort of artificial edge? Not ideal. In fact, most TVs look their best with the Sharpness control nearly off. It might lack that artificial edge sharpness, but that edge is actually masking actual fine detail.
There are also a lot of other brand-specific settings that get adjusted to, but obviously I can't cover all of those.
Now that you've figured out which picture presets you like, I highly recommend spending a few more minutes adjusting the actual picture settings. Contrast, brightness, color, and tint, all of these could use some adjustment, no matter what TV you've bought.
I actually did a full guide about how to set up a TV by eye, which goes through each setting step-by-step without using anything other than your eyeballs and the TV shows you watch.
Going one step farther
Adjusting the settings using just your eyeballs and the TV shows you watch is great, but not perfect. To get the exact settings, you need a setup disc. I like "Disney's World of Wonder." It's easily accessible to everyone, even if this is the first time you've pressed Menu on your remote. It's hard to say how much better the settings will be with the disc versus your eyeballs, but I'd say about 10 to 20 percent better.
I reviewed the Disney WoW disc and some others in "Reviewed: Blu-ray setup discs for your HDTV."
One last step
There's one more way to get the absolute most out of your TV: calibration. This is where a trained professional comes to your house and uses specialized equipment to adjust things like color temperature and (in many cases) color accuracy.
Depending on the TV, this could be a small improvement or a big one, but it's important to remember that if you've already set your settings with a disc, all a calibrator is going to do is check these, and adjust color temperature or color accuracy. It's not going to transform a bad TV into a good one, but it will make your TV look its absolute best.
I write about that in "What is HDTV calibration."
Because it's almost always the closest to "accurate," we always recommend starting with Cinema or Movie mode. If you're used to your TV in Vivid or Sports mode, Cinema or Movie is going to look soft and red. This is your mind playing tricks, because the reality is these modes are hyper-edge-enhanced and blue. Watch the Cinema mode for a few days, and you'll see what I mean when you check Vivid again.
Obviously the best mode comes down to personal preference. Hopefully this guide at least gave you an idea of what each mode does so you can find that personal preference.
What I recommend for everyone, though, regardless of what picture preset you choose, is adjusting the picture settings and ideally getting a setup disc. Not adjusting your settings means your TV is not looking as good as it can. And that makes me sad.
Got a question for Geoff? First, check out all the other articles he's written on topics like HDMI cables, LED LCD vs. plasma, active versus passive 3D, and more. Still have a question? Send him an e-mail! He won't tell you what TV to buy, but he might use your letter in a future article. You can also send him a message on Twitter @TechWriterGeoff or Google+.