Personally, I love an alarm that starts quiet and slowly builds over time so that I'm not jarred out of sleep, but ultimately, you just want to choose something that's melodic, like music.
If I hear that sound, though, like, if somebody's set an alarm throughout the day and I hear that sound just like, at a random time throughout the day, it is extraordinarily triggering for me.