The term can can be used in a couple of situations with different connotations:
1. When someone or something is very loud when they/it does not need to be. Similar to needlessly loud.
2. To describe a thing that naturally has a very, very high decibel level.
1a. Why do you have the TV on so stupid loud? You can hear it from across the damn street!
2. Holy shit, lightning struck the tree in our yard last night and the thunder was stupid loud!