The only hard limit one would hit would be disk space. The volume I'm on still has 335 Gigs free, so that's enough if I wanted to fill my space in the next hour. However, it would take a much longer time to fill up that much space and their admins would notice this before it became a problem.
I hope you read the whole page you mentioned because Jeff from DreamHost replied that they will try to warn you if you're hitting the SQL server too hard, but if it's an emergency, they'll disable your activity and then contact you shortly thereafter. In cases I've seen about accounts being disabled for technical reasons, DreamHost has made efforts to accommodate the user, whether it be moving them to a less used server, moving them to a dedicated server, or letting them download their data if they need to find a new host.
In the end, DreamHost does their best to live up to their end of the bargain. It's not a problem if you're the average customer. If you're not average, but in a good way, they'll help you out. If you're not average, but in a bad way, they'll probably be more sympathetic than I'd be.
You'll find that most customers are very happy here, and a noisy few aren't.