In the past, I always thought of Stargate SG-1 as being on the low end of Sci-Fi shows. However, I kept going back to it. I have watched the entire Stargate SG-1 series at least three times, and at least a couple of times for the spin-off series. (Stargate Atlantis is really good.) I came to realize that this is a really good show. It is mostly the characters that kept me coming back, but there are also some surprisingly excellent story arcs.
The show is significantly better than the movie it is based on. Some of the early episodes in season 1 are not the best, but then the series gets consistently good. There are very few bad episodes in the entire ten-season run.
I had a similar experience with Star Trek Deep Space Nine. It has the best set of characters of any Star Trek Series. These characters feel like long-lost friends. Some of the episodes are kind of hit or miss, but more hits than misses. The series is less serial than other shows, and that hurts it a little bit.
In passing, I should mention Babylon-5. I don't think that the characters are as good as Deep Space Nine, although still good. It looks lower budget, but it has some really impressive story arcs. The overall story is more complicated than most shows. Season 1 was a little rough.