good topic for a hearty debate! I agree with the general sentiment from others that it often makes little difference.
In my view, it is not possible to make any generalisations across cameras.. you have to test your own camera to find out what works best. with my old 20D, i proved to myself that ISO1600 gave the best results for faint details.
with the 40D, there is very little difference between different ISO settings. here is my test comparing same total exposure time with different ISO settings (among other things):
http://www.philhart.com/canon_test
Here was my finding at the time: "There is little difference between ISO1600 and ISO800 with the 40D, with 1600 perhaps having a slight edge. Noise increases (only slightly!) at ISO400 and ISO200 without delivering any benefit in saturation or bright area detail."
Obviously higher ISO settings have higher noise in an individual exposure, but the signal also goes up. So what matters is the signal to noise ratio and whether that ratio gets better or worse as the ISO setting increases. The differences are often subtle since the ISO amplification will necessarily increase both signal and noise by close to the same amount (there being no such thing as a free lunch) with the end result being at least partly governed by read noise and other factors.
So every camera performs differently, but I think the 40D performance would be typical of a well engineered camera. But I encourage you not to place blind faith in generalisations nor pay attention to the grainy appearance of one high ISO sub. Take a series of subs with your camera under the same conditions at different ISO settings, stack them together and make an objective assessment of what works best.
Phil