For all online First Person Shooters then it matters a lot if this set to off as it creates a slight lag. In single player games if you can get responsive controls and your graphics card is capable of more than 60 frames per second at all times then it's usually better for some people (I don't mind the tearing at all so I rarely use V-sync but in games like Pro Evolution Soccer and a few others I will enable it).
What to do is set it to Vsync application controlled for other games that let you select V-Sync in the options as then you can change the settings in game instead of having to revert back to the desktop to change it. For the games you need to disable it for, just keep disabling Vsync.
In fact, just play with it in a few titles and see what you think. I'm just giving you an insight on how I use V-Sync

.