I think sweet spot is the aperture and (on zoom lenses) focal length that gives the best measured results. Highest resolution, lowest CA. But this also depends on the specific lens. Some zoom lenses are better on one end, others in the middle. Best aperture is usually one or two stops from wide open, but below f10. Some Pentax cameras have an MTF mode that chooses the aperture with the highest resolution for that specific lens (only works with Pentax lenses). You can find out the "sweet spot" of your lens by looking at online reviews or just by studying your photos. If you can't notice a difference, its probably not important anyway
Its just good to know that a lens at its lowest f-number or above a certain f-number, at certain focal lengths might not perform as well is it could. This explains why sometimes a photo appears a little fuzzy, even though there is no motion blur and the focus is good.
I would only worry about this if a) I was taking a photo where technical quality is important, like a product photo for a client and/or b) I was using a really bad lens with quality so low that only the "sweet spot" is acceptable. You can usually shoot raw and use lens profiles to digitally correct vignetting, distortion, and CA quite well. You can even add some sharpening and clarity, to make the photo appear more detailed and sharper.
Oh, and sometimes people refer to the sweet spot as crop sensor. Lenses tend to perform better in the middle of the frame than in the extreme edges. Since crop sensors are smaller than FF sensors, some say they only record the "sweet spot" of the lens' image circle, provided the lens projects a bigger image circle.