I personally do not use adaptive v-sync as it doesn't work well with 120hz. Even when I'm pinned at 120 FPS, it will still not turn on v-sync and I end up with tearing. I'd also rather have 90 FPS with v-sync on than 100 FPS with v-sync off.
Adaptive isn't useless at all. When it works it's a perfect solution, turning off vsync when fps drops below monitor refresh rate and turning vsync on when fps goes above refresh rate. Some games for me it works perfectly. Other games it's not consistently turning on vsync above refresh rate so I still see tearing. Nvidia still has some work to do to perfect it. It certainly isn't a marketing gimmick. All anyone has to do is read about the technology to see that it's real. Hardocp has an excellent article on it.
I personally dislike it, as I hate seeing tearing when below my monitor's refresh rate. This is especially true when using a 120hz monitor. If you dislike tearing less than I do, and have a 60hz monitor, it may have some use for you.
As far as triple-buffering goes. For the most part, triple-buffering is only an option for older OpenGL games. The control panel option only works for OpenGL, and only a few games give you the option to turn it on or off in DirectX, which the vast majority of games use. Most games have it automatically built in.
@BigMack70 i like your answers man but i think Adaptive V-Sync is actually pretty good i mean if you care about tearing or not you will use it because i mean if you don't care about tearing at all why not use it your FPS will not get hurt because when it's below 60 it turns of V-Sync so your FPS doesn't drop and when you're already have 60 FPS you'll get rid of tearing so why not use it then this tech is only irrelevant to people who can't get any game above 60 FPS and that's rare now so why not, everyone should just go for it and use it except people with with more than 60Hz monitors.