Will I notice a huge difference with this upgrade?

For many things, yes you will see a big difference. My wife has a similar CPU and I'm going to give her my e6750 when I get a quad. According to the CPU charts, she'll see quite a jump. You'll see an even bigger one for CPU-bound programs. Don't expect a big FPS jump in games; the HD3850 begins to be taxed at 1680x1050 (mine certainly is).

A video card with more RAM would definitely do well for you.

Yes. Something like an 8800GT with 512MB of RAM would be a notable step up. I'm in a similar situation and may do the same. I've got a 3870 sitting on a shelf, but I'm not happy about the HD38xx drivers, particular relating to fan / cooling issues. It looks like I will be going back to nVidia, possibly the Asus model with a radial fan on it.

I tried that. The results are not consistent. Typically only one or two of the threshholds I set actually change the fan speed. If it's the 40% and 80% ones (completely ignoring the one for 60%), the fan audibly revs up and down while I'm playing. I'm thinking of redoing it at a fixed 60% and see how that goes, but still the card and/or its drivers are buggy to behave this way. I found an article at guru3d about BIOS issues on that card, so...

There is nothing wrong with the P965 Chipset so you should just get a Core 2 Duo E4600 , E6420, E6600, etc. whichever fits you budget and upgrade the video 8800 GT 512 or 8800 GTS 512 (Not the 640 or 320) or ATI 3870. Your ram etc. is good enough, I am running a 8800 GTS on 500 watts without problems. Your mobo limits you to 1066 FSB unless you can manually set it to 1333 which many but not all P965 Mobos allow this. If yours does then an E6750, E6850 will be a good upgrade. Any one of those Core 2 duos should be faster than anthing based on the P4, Mhz doesn't mean as much as it used to a 2.4 E4600 C2D or higher will out perform a P4 based processor of a higher clock easily in most cases.