There will always be a certain level of traffic noise in an urban environment, and always has been. From the descriptions I have read, even pre-automobile cities were plenty noisy, with the clattering of wagon wheels and horse's hooves on cobbled streets.
Sirens today are MUCH louder than they used to be -- there has been a steady increase in their volume each decade that passes, in order to 'cut through the background level'. I think that a big part of that is intended to get the attention of vehicle drivers who are on their cell phones, or listening to their superpowered stereo systems.
On the other hand, there is a possibility that future noise levels might substantially drop. Fuel cell powered autos are basically silent, according to all reports, and I would expect that at some point in time they will constitute almost all vehicles on the street (although maybe not for decades yet). With that reduction in ambient trafic noise levels, sirens could have their volume reduced to non-earsplitting levels -- especially if by that time, they are linked to radio transmitters alerting all vehicles in a given range of their presence, which could alert their own drivers (if any). In fact I would not be surprised if all street-legal vehicles are eventually required by law to have transponders for detecting emergency vehicles, in which case sirens might be eliminated altogether. No sirens, plus fuel cell vehicles, equals very quiet urban streets -- not tomorrow, but a few decades from now.
Bill