The Insurance Information Institute has produced a pretty good summary of what to do at the rental car counter:
Properly insuring a rental car can be confusing, frustrating and downright daunting. Unfortunately, many consumers do not even think about car rental insurance until they get to the counter, which can result in costly mistakes—either wasting money by purchasing unnecessary coverage or having dangerous gaps in coverage.
If you do not travel that often, it makes sense to buy the loss damage waiver. Think of it as "trip insurance." Regardless of what happens to your rental car, you can hand them the keys and get on your plane. End of story.