The best designers care about how the design helps you make more money or get more customers.
Should a site look good? Absolutely. It never hurts anything to look better. Can a site look ugly? Absolutely. If your business is strong enough, there's no amount of bad design that can hurt it.
The main thing though, is that more attractive designs don't necessarily convert better. People think they do, because you're turned off by ugly designs, and for sure, if you've got two sites up that share the same interface, and one is prettier than the other, the prettier one will likely win your business. But if you take those same sites, and have better copy on the ugly site, it may well convert better. If your pretty design is hard to use, it may well lose.
So yeah, if your web designer cares more about the end user or themselves than whether the design is effective, then you're paying for the wrong thing.
Speaking from a strictly results-based perspective, sometimes looking better can indeed hurt some user actions. See the recent study on slick, professional ads on Plenty of Fish vs MS Paint ads.
But I'm nitpicking. From what I've seen, in 99% of cases looking more pro helps.
Should a site look good? Absolutely. It never hurts anything to look better. Can a site look ugly? Absolutely. If your business is strong enough, there's no amount of bad design that can hurt it.
The main thing though, is that more attractive designs don't necessarily convert better. People think they do, because you're turned off by ugly designs, and for sure, if you've got two sites up that share the same interface, and one is prettier than the other, the prettier one will likely win your business. But if you take those same sites, and have better copy on the ugly site, it may well convert better. If your pretty design is hard to use, it may well lose.
So yeah, if your web designer cares more about the end user or themselves than whether the design is effective, then you're paying for the wrong thing.