I think a lot of the love for it comes from “growing up” with it and having nostalgia blinders on.

I really loved SG1 when it was airing, but rewatching it nowadays I’m more in your camp -redeaming moments and really good episodes here and there, but for the most part it’s fairly mediocre and a background watch type show at most.