I love HDR photos, well some HDR photos. Like Max said, most people go over the top with HDR. Most of the time for me, all I am aiming for is to correct a major flaw in camera technology and give me a realistic, properly exposed photo. But, I have to admit sometimes you can push the buttons and come up with something cool too. Cameras only see a vary narrow range of exposures at one time. No match for a human's Mark I eyeball. We can see the details in the shadows at the same time we can see the details in the clouds, instead of a dark blob or blown-out highlights. HDRs like contrasty scenes best or that is where I think you get the most out of HDR. It is also good to use a higher f-stop (>f-16) for a greater depth of field (whole scene in focus).
I have used the Photomatix app for several years and it is a great program. I am now using the Photomatix plug-in for Aperture (Apple's Lightroom) now that it does everything that the standalone app does. I think it is important to point out that Lightroom and Aperture have two big benefits over some of the other photo editing software being talked about. Both are also meant to manage your library of photos. Think iTunes for your photos. But even better, both work on the concept of non-destructive editing. You put your photo into Lightroom/Aperture and when you make tweaks to it, it creates a new 'version' with the tweaks. They don't actually save the versions but saves a list of the tweaks that you made instead. You can play with multiple versions off of one master, great to see what a little tweak might do here or there. And the 'versions' take up literally no space on your computer and the original is always that, the original.
Here's a 'natural' HDR and and hypernatural HDR from the same photos.