My first dSLR was a 20D back in 2004 or so (whenever they first came out). I used to shoot news back in the film-only days. The 20D's was on par or even worst than Tri-X on noise/grain. I never took the 20D above ISO 1600. But with the latest sensors, I will shoot @6400 with barely a second thought to noise. While I am still very conscience of IQ, as sensors get better, noise is certainly becoming a less significant consideration.
Typically, noise is a distraction, the greater the noise the greater the distraction and the greater the noise competites against the image in attracting the viewer's eye. Noise and acceptance of noise levels is plastic, it changes depending of the subject and subject matter. Generally, if one shoots landscapes or studio or architecture then the less noise the better. But an action image, an image with heightened drama can handle much more noise without being a distraction. Extremes examples are Nick Ut's photograph of the screaming, naked, napalmed little girl running down the dirt road is unsharp and very grainy, but is so powerful and dramatic that the lack of good IQ, of sharpness and grain do not register as significant elements of the photo. Conversely, any Ansel Adams shot of Yosemite, one is immediately impressed by the sharpness and tonal ranges, the high IQ of Adams' images are an integral element of the image. Had Adams photographs of Yosemite had a similar IQ to Ut's image ... His photographs would have failed. In contrast, had Ut's image been perfectly in focus and grainless, it would not had been significantly more successful. (But Ut's peers would have paid homage and granted him Godlike stature.)
When I was a pro, my first consideration was the client. I had to exceed the expectations of the client. IQ was a significant factor in meeting and exceeding the client's expectations. Secondary to the client was my own expectations of who I was and who I desired to be. Even if the photo was to be used as a 1 column by 2" mug shot, I still was careful to produce a high quality photo to match my expectations of myself. I discovered that my expectations of myself were usually higher than my editors/clients.
When I shot for magazines, I tended to take more time for a higher quality photo, both in the capture and in the processing because: 1) magazines had much higher publishing DPI/quality than newspapers; and 2) because typically, I had more time.
Again, that is shooting for the client's expectations.
Today, not having any clients/editors, I still keep kicking my own butt and still try to improve upon the last time I looked through the viewfinder.