They are allowed to look into the traffic only as much as they need to in order to maintain quality service. They notice stuff like botnets and piracy because both of these activities have the potential to generate abnormal amounts of traffic. Another reason they are likely to notice these things is because a third party will often notify them about the activity. They would have to monitor your connection/activity in a way that's highly unethical and possibly illegal in order to detect anything that isn't overly noisy.

That makes sense. I'd imagine though that it shows up on the ISP's radar when major DoS attacks happen. It seems like they could set up alarms for such things. Maybe it could be an opt-in thing.

DoS attacks also generate huge amounts of traffic, and so they are also detected through the normal, high-level monitoring activity that ISPs conduct.