When shooting with a given lens, how do you know which aperture is optimal for your creative vision?

Let’s say you want shallow dof, is there a place where you can learn that the sweet spot for dof and sharpness for a given lens model is when it’s stopped down by 1/2 stop?  

Conversely, where would you go to learn that a given lens model goes soft after f/11?

Sure, you could create a test rig, take tons of pictures with each at every aperture and inspect them with a magnifying glass, but that seems awfully inefficient.

Thanks and I look forward to everyone’s guidance and insights…