stealth is very useful in pve, but only in certain cases, like if you wanted to really speed through a public dungeon without fighting stuff (aka mages guild quests) (though this works best on a NB with cloak)

stealth is also very useful if you are into thieving and stealing stuff in towns, or doing the thieves guild heists or dark brotherhood sacraments

for NPCs they work on the principle of a "vision cone" and have much better detection radius from the front than the back, if you have your stealth super good, you can even steal while being in front of an npc and they wont notice

reduce sneak cost and reduce stealth radius are the only real useful things for pve stealth though, increasing your stealth detection radius is more of a pvp thing