Single Point Auto-focus: Fastest and most accurate auto-focus mode, great to use in most situations, including predictable action shots where you can keep the object on the point. I like to decouple the auto-focus mechanism from the half-press to avoid focusing on the background if the subject unexpectedly moves off the point. The camera is told which AF-point to use and thus does not need to spend time thinking about which focus point to use. It is the most accurate because you select which point yourself and know which point is being focused and you do not recompose. Of all the points, the center point is the fastest.

Dynamic Area: You select one focus point and it uses the points around it in case the object leaves that focus point. Used for more unpredictable / fast sports where you cant keep the object exactly on the focus point at all times. Can be slower than single-point auto-focus, but if you can't keep it on the focus point, this mode may be better.

Auto-area: You don't select anything and it picks the area. This reportedly has some face detection algorithms going on, but generally I would use this for very sporadic/fast objects without high-contrast backgrounds that can confuse the AF-system or other objects either closer to the camera or around the same distance. I might use this with a long lens and tracking objects like birds in flight, RC planes, airshow, etc. The long lens with the fast speed will make it more difficult to keep it in one area and there are not other objects to confuse the AF system.

3D-tracking: For D300 class cameras, this is an extension and improvement on dynamic area focusing for tracking moving objects. For the D90-class cameras, this is the mode you want to use with focus-recompose. There are 2 problems with focus-recomposing that can appear with very shallow depth of fields. First, when you recompose, you are usually not rotating about the correct point of rotation to keep the object in focus, but rather just rotating your head. Generally your plane of focus will be slightly behind where you intended. Second, your focus plane is generally not a plane but a curve, which will differ depending on a number of factors. 3D-tracking uses color information to select the correct AF point when recomposing to avoid such issues.