Basically, it makes sure that the monitor would only change the image if it can change the whole frame. If your graphics card is done painting only 5/6 of the frame, it will wait until the next refresh rate to draw it. If your graphics card is done painting the frame before it's time to refresh, it will make the graphics card idle until the refresh rate.
It works pretty simply: if there's a call to refresh buffer on the screen (openGL or DirectX API call), the driver simply freezes the game engine until it is time for monitor to refresh itself.
Depends on what kind of game you're playing, and how bad the tearing is. If I'm playing an FPS or fighting game, I'll leave it off no matter what. If I'm playing an rpg or a single player game of some kind, unless it's a really hard platformer, I'll turn it on.