A lot of good responses here along with the requisite few crazy ones, LOL.
I've seen the .40 used in LE since its inception and I have attended or reviewed more than a few after-action reports and postmortems involving its use. It is not Thor's Hammer, but it is enough. I'll use it with confidence as long as my issued ammo is 165 grains or heavier and constructed for lots of penetration.
My personal handguns are a .45 ACP and .45 Colt, if that tells you anything. I do have a .38 snub that I can hit pretty well with and my plan for that one is 2-3 on the centerline followed by one more on the bridge of the nose, if they don't produce results as needed.
Don't get too hung up on what the police or FBI use. Keep in mind that for ages, they both used .38 RLN. Even that works OK if placed properly and there's a lesson in that.
Copshops are just as prone to fad-driven selections as anyone else. In the case of the .40 S&W, it just happened to work out pretty good. There was, however, no 'need' for it. The Feds tend to make a selection based on their own agenda and then 'test' until that selection is justified.