I keep hearing how SG-1 started very weakly but got much better as it went along. Is there a fan consensus about what season SG-1 started to get good, like how nearly all fans agree TNG season 3 was when that show became great?
The short answer is no, there really isn't any consensus. Personally I've never felt there was a huge fluctuation in quality at any real point in the series, except perhaps between season 7 and season 8, which in my opinion were the best and worst seasons of the show. That isn't a p[particularly widely held view though.