What is it? Its "a random, usually unwanted, variation in brightness or color information in an image". That's the technical definition. More simple terms, its when a photo seems to have little dots or doesn't seem smooth.
What does it come from? Usually it comes from shooting at high ISOs or speeds. But it can also occur when images are underexposed.
How to prevent it? Key way to prevent it is to make sure there is enough light. Light is essential for good photography. A light source could be a flash, studio lights, or natural light (the sun). This may explain why I might ask to reschedule a session if it is a dark and dreary day.
What other options? There are noise reduction computer programs, such as Noiseware. It removes some noise. Its not perfect, but it could help.
Is it ever good? For a film lover like me...sure. I love grainy B&W films, so if I have noisy images, I will convert to black and white. For me it creates a certain mood.
What exactly does it look like? Here are two images, shot only a few minutes apart, in almost the exact same location. The noise is much more noticeable in the top image, shot at ISO 3200, as compared to the bottom image, shot at ISO 200.