It's probably different for different people, but for me:
I get it when there are several competing sounds at once, it's also worse if there are too many things moving in my field of vision as well as the sounds. It just kind of makes my brain shut down, all the sounds/stimuli mash together and I get this rushing feeling and I need to either leave, stop one/several of the sounds, or I will get very agitated and shout/snap or even lash out. I can't perform tasks in that state or think and my patience is absolutely zilch.
For example it is quite likely to happen in the kitchen when I'm cooking, because of cooking sounds + I often listen to a podcast (which is fine if it's just me) and one or two small children come in to be near me and somebody (one of the kids or DS1 or DH) tries to talk to me, I will generally hit a kind of shutdown point and just go "Out! OUT! Everyone out of the kitchen I can't concentrate!" and then tell people outright to go away, which isn't very nice but it's sort of survival because it makes me very panicky like one of them might get hurt because I can't concentrate on safety.
Sometimes sounds get distorted and start buzzing rather than sounding normal so I can't hear them properly.
I get it in the bathroom as well when DH is bathing one of the smaller children, the extractor fan is on plus music that he has just too loud for my comfort level.
Or if there is a diesel engine outside for more than a few minutes it will start to make me feel run down and tired and snappy. It's something to do with that low level singular noise. Extractor fans I tend to turn off as well.
Heat AND cold both set me off as well - it's a pain in the arse because there are only about 3-4 months of the year I'm actually comfortable outside. If I'm too cold, everything hurts, I want to curl up into a little ball, I cannot think, I can barely talk, all of my being is consumed with getting to somewhere warm ASAP. If it's too hot, then I'm drowning in sweat, I feel faint, I can barely move or think again and it just generally feels intolerable.
I don't honestly understand how people can work outside or with noisy engines etc - I would not be able to cope in those environments at all.