I would like to know as well. Had a tornado come near my house and phone was in Do Not Disturb mode. The alert showed up silently and I can find no way to make it play an audible alert that overrides DND. So now I have to sleep with my phone buzzing with notifications during the night.
I had an Amber alert come through with sound and all when DND was off so I know the audible alert works normally.
The Garmin watch paired with it plays sound alerts for Find My Phone even when in DND so perhaps it's a different sound channel.
But it's a pretty serious issue and I don't know if that's a Pixel, an ASOP, or GOS problem. I assumed it would just like all the other phones I've ever had but apparently not. Regardless, this could cause people to die if they trust their phone to alert them and it won't.