Artistically, "grain" (which is really a film term rather than digital) is sometimes a valuable tool in getting the look and feel you want in an image. A little can be useful, too much is generally useless
Not many stock sites will accept that though (none I am using, at least) - they want the images clean. I haven't run into a lot of noise issues recently. My D70 seems to produce very little; the D200 produces more, however, it's new and I'm still working it out.
Noise can be the result of a lot of things: with a P&S camera it can be related to the sensor. With a dslr most of the time it winds up being shooter error. If you have the proper settings for the proper lighting conditions noise shouldn't be a huge issue. Though there's other things too - high heat and high humidity can affect the D70 (and perhaps other dslrs).
Whenever I've gotten noise it's generally because i didn't use the right settings for the situation. The D70 shoots only ISO200 and up. But I don't always leave it at ISO200. Sometimes using the wrong ISO can cause more noise than not. I shot two images at the beach early one morning as the sun rose - using a tripod and the same fstop/shutter speed and focal distance; the only change I made between the two shots was the ISO - one at ISO 200 one at ISO 450....guess which one had the noise issues (ISO 200). Go figure.
I find less noise in my raw images than in images I shoot in jpg.
I've used Neat Image - it's a good tool, but it's better if you can avoid it. You can lose a lot of details with this. Shoot it right and you probably won't need to use it. It's been quite a while since I've had to resort to this (actually, i shot some snaps at our christmas dinner with a little coolpix in bad light - and I used it for them, but they are just family treasures)
Better program if you shoot in raw is RawShooter Essentials (by Pixmantec - they have a highly function free version - no kidding) which is a raw converter better than anything else I've tried - has some great tweaks for raw files including controlling the noise and bringing out details; adjusting while balance and sharpening or not. A word of caution: at this point RSE doesn't support D200 raw files....bah.
