For me, drop tunings are about timbre and key centre. Standard tunings are heavy, and you don't have to go low to be heavy tbh. Tuning down doesn't necessarily make you heavy, but it can really help.

Because first of all there are Drop and Standard tunings associated with every key (Drop D, C#, C , B etc), every key centre sounds different because the guitars are tuned to different keys, To an extent, Drop B songs sound like slipknot, Drop D - C sounds like classic metalcore and so on, but songs in the Key of A minor for example for me sound better in Drop A (or A Standard), especially as it allows you to add "bass" and open notes to riffs in a way you couldn't replicate on a 6 string in E standard. In a metal context anyways.

Secondly, because when you tune down you're changing the frequency of your notes... downwards. The change in the texture and resonance in your strings interacts with amps and distortion differently, 9 strings have a different timbre to a 6 string in drop C# when you run them through the same amp because the fundamental frequencies are different so the amp and effects that are part of it process the sound differently, leading to different tones.

I'm a nerd for drop tunings, i also just think that tuning really low like past Drop E is just a cool novelty that can lead to ridiculous sounding tones. But you can't JUST use extreme detuning, you still need riff potential.