IN TRADITIONA PART-WRITING, second inversion chords in triads (7th chords are different) are almost always used for one of very few reasons: as a pedal point, as a passing motion, or as a cadential figure. The reason it is dissonant as a triad is because your bass voice will always have the richest overtones, rising up in an extended major chord. When the distance between the bass and two upper voices is a fourth and a sixth (a 6/4 chord, or a 2nd inversion chord aka), the overtones of the bass note conflict with the sounding voices above. That's where your dissonance comes from.
If you don't write traditional music, I'd say don't worry about it too much. It's good to learn traditional part-writing, don't get me wrong. But practically speaking, write what you want.