This forum post explains VSync in as much detail as you could ever want.
The gist of it is that VSync stops screen tearing.
Screen tearing occurs because the frame buffer is half filled with the next frame when it is written to the screen.
VSync introduces a back buffer inbetween the video card and the frame buffer which stores the next frame and then waits for the screen to refresh before copying itself to the frame buffer. The refresh signal is known as a VSync pulse (from analog). This stops the tearing from happening but can lead to a significant reduction in video performance in certain situations. These situations arise when framerate drops below refresh rate. In these situations your performance can drop by up to 50% in the worst case.
Triple Buffered VSync improves upon double buffered by adding another buffer and results in a much smaller performance penalty. In fact you will see frames as soon as the card can draw them (still timed with refreshes) because the card never has to sit idle, it always has a buffer it can write to. The only penalty is a small loss of VRAM to hold the buffers and the extra time taken to copy them out to the frame buffer. This is negligible.
Only worry about VSync if you are getting screen tearing. Don't worry about enabling it on a triple buffered system. If it is only double buffered then it's a personal choice, a tradeoff between refresh rate and tearing.
For the more interested:
Technically VSync does not require a second buffer. In situations where rendering the screen takes only a fraction of the time between refreshes VSync pulses can be used to time the writing of new information to the screenbuffer. If render time < refresh interval.
When you play a game the time taken to render is much greater. Writing the buffer on the VSync pulse would result in tearing whenever render time > refresh interval. The second buffer prevents this screen tearing from taking place but means that you can be looking at the same frame over several refreshes. It also means that once the back buffer is full there is no place for the card to write the next render until the VSync happens. This means that in practice VSync in a video game always introduces extra buffers.
Adding the third buffer gives the card a place to write the next render once the back buffer is full and awaiting the refresh.
This knowledge comes from programming display drivers for embedded systems.