When I first came across the setting on the Nikon D3, I dismissed it as rather a gimmick. My initial experiments were using my most common exposure mode: aperture priority, and I disliked the camera changing the ISO value as well as the shutter speed. This felt too much out of control to me.
The only time I felt comfortable using this automation was when leaving the D3 in a forest in a camera trap for several days. The camera needed to take pictures in wildly changing light conditions, requiring flexibility of ISO. By definition I could not change the ISO at the camera trap, so the camera needed to be able to do this itself.
Recently, whilst photographing gannets with friends at Bempton Cliffs in North Yorkshire in England, the issue of Auto ISO cropped up again. I felt I should probably give it another go, and the aspect that finally won me around was the discovery that Auto ISO works extremely well in manual exposure mode.
Having never previously considered this combination, being able to fix the aperture and shutter speed while the ISO value follows on automatically was something of a revelation. I like to take control of the look and feel of a photograph, and to be able to set both aperture and shutter speed, leaves me completely in charge creatively. For example, I knew I needed a certain shutter speed to avoid camera shake with a 600mm lens and a moving gannet, but I also wanted to use the aperture to control the depth of field for sharpness across the bird's head while still blurring the background.
Out of interest, under normal circumstances, exposure compensation does not work in manual mode because it has no counterpart control to modify in order to adjust the exposure: aperture and shutter speed are fixed in manual mode. However, in Auto ISO with manual mode, exposure compensation alters the ISO value, so you still get a quick way of over- and underexposing when the metering system is being confused.
ISO affects how much noise is present in the image and the total dynamic range available, but it is not a creative control. If the camera is able to automatically adjust the ISO value, it is very liberating to be able to control the true creative parameters of aperture and shutter speed while retaining the ability to balance exposure with exposure compensation.
At this point I should introduce a couple of health warnings: firstly, not all cameras have a linear noise response at intermediate ISO settings (i.e., half and third stops). ISO is essentially an amplification of the visual signal. Full stops like ISO 400 and 800 are amplified using the camera's hardware, which gives the cleanest signal. However, some cameras use software amplification for half and third stop values like 560 and 640, which introduces more noise than is simply within the light itself. Most Nikons and high-end Canons use hardware for all ISO values, but it is worth checking which amplification method your camera uses for intermediate ISO values. Take a series of photos at whole and partial ISO stops. If the camera uses a mix of amplification methods, it is likely that intermediate ISO values will produce significantly more noisy images than at full stops.
The second thing to consider is to limit the maximum ISO that Auto ISO can use. This will depend on the camera's noise performance at high ISO values and what you consider too noisy.
I am now a true convert to Auto ISO, particularly in manual exposure mode.