Scary is very relative. Death is scary at lower levels, while only annoying at higher level. In our last session, my character was killed by my nemesis, who took the actions to attack me while dying in order to kill me. It was annoying because I was out of the combat. Afterwards I was brought back to life, so I wasn't "scared." I was more concerned by the fact that my head was cut off, leaving a nasty scar, and that my left eye is gone. These types of consequences are scary, including mission failure (hostage killed, ritual completed, etc).
Generally combat is scarier when something is unexpected, or when facing the complete known/unknown. Going into a fight assuming 2-3 opponents, only to have reinforcements arrive, makes me consider that we've gotten in over our head, and should regroup. Fighting a creature we know nothing about (because it's new or a reskin of a different monster) is scary, because we don't know what we're dealing with. Sometimes knowing what we're dealing with is even scarier though!