Where Is It Best Used?
When shooting a subject with a busy background, it will cause attention to be taken away from your subject. While focusing on the subject is the first port of call, ‘blurring' that background out by using a lens wide open will provide a shallow depth of field that draws the attention back to your subject. It also looks ‘more professional' as compact cameras aren't capable of performing such results. In portrait photography too, a shallow depth of field focused to the subject's eyes provides a striking image.
The opposite tends to be true of landscape photography. Expansive vistas with layer-upon-layer of detail important to the image or subtle variations in light lend themselves well to all being in focus. In this instance, you'll want to select a small aperture, such as f/22, to provide a deep depth of field. However, some lenses when stopped down to f/32 can lose sharpness that's available at f/16 or similar, so landscape photographers need to carefully consider shots, often bracketing around a number of aperture values. With limited light this often means that a tripod is essential gear.
There are also times when the depth of field desired is not possible, due to either the restrictions of the light or of the equipment used. For example, macro lenses can magnify and provide such an exaggerated shallow depth of field that not enough of the subject is in focus.