As you stop a lens down (ie increase the f-number) the depth of field (depth of focus is a different thing) increases - that is easy to understand. There are, however, other effects that affect sharpness.
Some of the lens' aberrations - the faults - decrease. This makes the lens sharper, even for those points that are in perfect focus. The improvement is usually rapid as you stop down one or two stops from wide open, then much more gradual. This means that even if you have enough depth of field when the lens is wide open, it will probably be best to stop down two or three stops. This varies from lens to lens.
One of the other things that happens when you stop down is that diffraction increases. This softens the image - turning what should be points of light that make up a sharp image into patches of light that are roughly circular (they aren't circular, but they can be considered as if they are). The area of the patch grows as you stop down. This means that the points in the image that are in sharp focus become soft. The diameter of the diffraction circle (known as the Airy disc) is in direct proportion to the f-number.
It makes good sense not to stop down too much. With 35 mm format, stopping down beyond about f/8 may make diffraction noticeable - it depends a lot on the quality of the lens, the resolution of the film or sensor and the degree of final enlargement.
The smaller the format, the sooner diffraction will be noticeable. This is mainly because the smaller the format, the greater the degree to which the captured image will need to be enlarged.
If you need very deep focus, some diffraction will be acceptable. It affects the whole image, so it looks even. You don't get patches of sharp focus and patches of softer focus - that would make the softer parts more noticeable.
This all means that apart from aesthetic concerns about what is in focus there are also technical reasons to prefer certain apertures.
Best,
Helen
"This is because the f-stop number is the denominator of a fraction that is the lens's focal length divided by the actual aperture diameter."
Er, the f-number* is the focal length divided by the entrance pupil diameter (which many people call the aperture, but it isn't the actual aperture).
*The term 'f stop' is less well defined.