It really depends on the site and how much resources you end up using. As bobocat said, at 90mb memory, we start poking you to be a better neighbor. The VPS and Dedi boxes don’t have the same limit, but if you go above the limit you DO have (which you can tweak somewhat with a slider), then you get emails saying you’re getting a bit big for your britches.
Rarely is a website set-it-and-forget-it. It needs a lot of care and nurturing and attention.