There's an old photojournalist expression that goes "f/8 and be there". It essentially means to set your lens to f/8, and just shoot without worry. Most lenses have a "sweet spot", which is (as RexGig mentioned) about two or three stops from maximum aperture. On an f/2.8 prime, the sweet spot is around f/5.6. On an f/3.5-5.6 zoom, it's somewhere between f/8 and f/11. On an f/1.8 prime, it's something like f/4. However, f/8 is usually a good comprimise that (for most lenses) gives you a sharp image, allows you to get a good depth of field and still blur the background a bit, but doesn't start to dull the image from diffraction.
I believe (but take this with a grain of salt as I'm not 100% sure), that diffraction is caused by the aperture getting too small, say f/22, f/16, even f/11 on certain lenses, and causes the light to bend and get dispersed in unnatural ways across the camera's sensor. This essentially makes the image less sharp, and so unless it's totally necessary (for example, you may need a very large depth of field, or it's extremely bright and you need to keep the exposure low), you should avoid going too high.
These images (courtesy of
Luminous Landscape) show the effects of diffraction at f/8, f/11, f/22, f/32, and even f/45.
f/8
f/11
f/22
f/32
f/45
of course, that doesn't mean that you should always shoot at f/5.6 or f/8, if you WANT that blurry background and shallow depth of field, go ahead and open the lens up to f/2 or whatever it's maximum aperture is, or vice versa for a shallow depth of field. If you're indoors, it's very important to open it up all the way, it's better to get slightly less sharp shots than blurry shots that have the potential to be sharp. Be creative, because that's what makes a photo, not how sharp it is.