So recently, thanks to the generosity of a friend, I was able to swap my 8800 GTS (640MB) for his spare 9800 GTX (512MB). While there is a noticeable increase in gaming performance, where I REALLY noticed a difference was in HD-DVD and H.264 decoding. My brother recently purchased a Canon Vixia HD camcorder, which records 17Mb/s H.264 AVCHD encoded video. The video is beautiful, but on my Core 2 Duo E6600 (2.4GHz) & nVidia GeForce 8800 GTS combination I was unable to play it at anything near native frame rate. The CPU would sit at 100% CPU and would render a couple frames every couple seconds. Not nearly watchable.
Using the same CPU along with the 9800 GTX, however, it's an entirely different picture (as long as you use a PureVideo HD enabled player). Using Media Player Classic Homecinema (which is DXVA enabled), the video runs smooth as butter on my system, with only a couple percent CPU usage.
It is a similar story with HD-DVD, though quite not so startling. With my 8800 GTS, when playing h.264 encoded discs it would be relatively smooth, but not completely, and every fifteen minutes while playing VC1 encoded discs it would stutter for a moment. No longer. The 9800 GTX does enough of the processing that my CPU now sits around 20% usage, and the video plays stutter free and completely smooth for the h.264 encoded discs.
So, in summary, VP2 really improved HD playback over VP1 (which was in my 8800 GTS 640MB). Make sure that whatever video player you use supports PureVideo HD or DXVA to get the most benefit.Also, try out MPC Homecinema if you want to do some HD video watching with a VP2 equipped system.
Additional Reading: HD Video Decode Quality and Performance Summer '07