So the theory with the Spear of Destiny, or Spear of Longinus as it is rightly called, is that whoever holds it is either invincible or their "power" increases 10-fold.
Interestingly, while Harry held the Spear he didn't do anything particularly magical with it, yet defeated all his enemies soundly in all situations. In fact despite the increasing difficulty of situations, whilst he held the Spear, he overcame all odds.
Now this isn't weird for Harry as he is constantly defying and beating far more powerful and deadly beings and overcoming extraordinary situations. But it bears thinking on that once Harry held the Spear, neither he nor his allies died or were wounded or defeated in any meaningful way.
Another interesting thing is the Odin's Spear Gungnir is sometimes equated with making the holder invincible - and Odin hung from Yggdrasil the World Tree, his side pierced by Gungnir and he passed through Death as was reborn. Modern historians find this story to be a more recent one, equated with the spread of Christianity into Scandinavia where the Vikings would have found connections between the two religions and absorbed Christian stories into Viking ones i.e. Christ's crucifixion was remodeled into a myth about Odin. Indeed the Vikings were known to worship both the Norse Gods and Christ, some even having the Cross on the back of Mjolnir (Thor's hammer).
I suspect if Dresden were to use it actively you guess correctly that he will likely kill himself in order to ascend or some such. I don't think it will be as mundane as a merely stabby object for a big villain.
