Killing off characters like anything has pros and cons. SG:A killed off at least two of it's leads Beckett and Weir, was it a good move that's open to debate. But in the case of Beckett the fallout was so bad they had to bring the character back (via a clone).
I actually liked the way they killed off Beckett, it was a shock when it happened and reminded me of how it felt when Daniel died on SG-1... Both of these characters had decent story lines to write them out and then back in again... Weir was just captured and we were told killed then mysteriously came back as someone else.
I think other than killing off a main character they should have developed the background players to the point that when they meet their fate we, the audience, actually care that they are gone. An example keeping with Stargate would be Sgt Riley in SGU.