"but every time I use it I see a 1 to 4 *seconds* of tearing. Am I doing something wrong - I'm setting the V-sync to Adaptive in the Nvidia control panel and turning off v-sync in the game."
The game's setting doesn't matter as the NV driver intercepts at render. The issue is likely what you are targeting. If you set your game up to sustain at 70fps non-synced, then when you do miss a 16.6ms render budget, it tends to miss for extended periods of time as you are dropping in to the 45-55fps range due to scene complexity. Targeting 90fps like I do, when I miss a 16.6ms render budget, it tends to only be missed for a couple frames before I'm back under that time limit.
Triple buffering is kind of cool, but not a perfect solution. When triple buffered, you end up with a weird cadence of dropped frames when you miss the 16.6ms update. It is a lot like the juddering artifact of telecine, but even less even. When dropped below 60fps with triple buffering you see things like "new, new, missed, new, missed, new, new, new, missed". You are also much more likely to actually feel
the drop in framerate.
Hit the Medium preset, turn on Adaptive and see what happens. I bet the improvement in smoothness will far counterbalance the mild loss in shadow detail.
Again, when initially finding what settings you like, turn off Vsync and target 90-100fps, not 60-75.
"It's like it takes a split second of dropping to 30 before it kicks in, and you get a noticeable judder before it gets the vsync disables."
This doesn't happen at all. The switch from synced to unsynced happens exactly within the frame that misses the render budget.
"Yeah, adaptive v-sync on nvidia cards doesn't seem nearly as graceful as it is on consoles."
A competent console developer targets 40fps, renders at 30fps, then drops vsync when the 33.3ms budget is missed. Adaptive V-sync doesn't configure your game for you, as the console developer did. If you're feeling that it isn't as graceful, it is exclusively because you are doing a poor job of configuring your game, followed by outright dismissal of the feature rather than investigating your error. Follow Daver's example.