What actually happens if I vary proportions of cement and sand in mortar?

This will affect the strength and longevity of your mortar. While I am not an expert here, there MUST be an optimal solution to such a problem. What mix will last as long as possible, hold strongly to the stone that surrounds it, be strong in compression, etc.?

Effectively, this is a multiple criterion optimization problem, that was long ago solved by practice to find the mix that best satisfies all the goals in combination. In fact though, if you go a wee bit in one direction, you will find that some of those goals will be better achieved, while the other goals will be harmed. This is how such a problem behaves. So the question now becomes, suppose I add some more sand (or other aggregate) to the mix? What will happen? Very likely (and again, I am just guessing at the exact effects right now as I talk off the cuff) the mix will become stronger in compression, but at some point it will erode more easily, get less sticky. Of course at some point, add too much sand and all you have is a pile of sand, which has none of the properties of mortar that you want.

Similarly, suppose I increase the proportion of cement? It makes sense that it sticks better to the surrounding stone now, but it is not as strong in compression.

Adding more water to the mix has other consequences, also optimized at the level you have been advised. So if you add water, the mix gets wetter, easier to work, stickier, but also sloppier. It won't stay in place. It may also change the cure time.

My point is that all of these parameters have been chosen to be optimal for the group of characteristics that define what mortar is and what it should do - the physical properties of the mortar. In fact, those parameters have been optimized by simple experiments by masons for years, until they established a mix that reasonably satisfies the best values at a point that is robust and stable to material variations.

Having said all of this, now I'll do a little actual research on the subject. For example, this site tells me that the ratio of sand and cement can be somewhere in the range of 1:2 up to 1:3, which changes the strength of the mix in terms of its ability to withstand compressive loads. It also mentions that adding gravel to the mix will increase the strength.

As I do more reading, I also see that the quality of the cement is a factor. With cheap stuff, you need more cement, so this probably is defined by the composition of the cement itself. (Is there lime in the mix? How much?)

I'll stop here, as there are MANY factors involved. What kind of sand do you use? Sand that is composed of perfectly round spheres, all of the same size, will be easily workable. But it won't be very strong. Sand that is "sharp" fractured with many sharp edges with varying particle size, will be less easy to mix, less easy to work, but stronger in terms of cured material properties.

A mix of aggregate sizes will change things too. Very fine sand will require more cement in the mix, as the tiny particles have more surface area for the given volume, so more cement is needed to coat the sand for good adhesion. But fine sand is easier to mix, easier to work, easier to stuff into a place. If the surface it must attach too is very irregular, it may stick better. But adding larger aggregate to a mix will increase the compressive strength, since large stone is stronger. (At some point, this turns the mortar into concrete.) Here is a quote that I find on one site:

"Mortar is a cement/sand/water (and usually lime) mixture designed for laying up masonry units like cement block, stone or brick. Mortar is "sticky" so it adheres to the block, stone or brick. Concrete is designed to stand alone."

While I am sure I have not covered all of the factors here, this should give you an idea. There are very many parameters involved. It is not only the amount of cement and sand that is important, but the exact cement formulation, the type of sand, the amount of water.

tldr; Stick to the recommended mix.

Also read and up-vote this other most excellent answer.

Now, here comes the science:

Concrete, mortar and grouts are all mixtures of Portland cement, water, and aggregates ( sand, and in the case of concrete: gravel.)

Concrete is used for structural purposes, and it's primary role is to support a load. The ideal concrete is a solid, monolithic block of rock with no cement at all. Obviously, this is not very workable. The next best thing is a mix that is mostly aggregate, finely graded to include all sizes from dust to boulders so that the amount of cement required to glue it together is minimized. The less cement, the stronger the concrete, provided that there is enough cement to hold it together.

Mortar is semi-structural. It's there to glue bricks together, but must be able to support the load over thin layers. it is primarily coarse sand, held together with Portland cement. It is Sand-vs-Sand forces that give the mortar it's strength, so I would be reluctant to reduce the sand quantity in the mix.

Grouts and thin-sets are non-structural, in that they are not required to support any real weight. They are used in tiling to prevent lateral movement, or to level a structural member (i.e. sub-floors) They are primarily fine sands and Portland cement.

Now we need to consider the cement itself. The w/c (water-cement) ratio is the single most significant factor in the final strength of the cured cement. The less water you put in, the stronger the final product (again to a certain minimum point.) So, this would tell us that for a good cement, we want a relatively dry mix.? Wrong. There are two other factors. another: Workability and Curing.

Workability: This is the ease of pouring, shaping, smoothing of the mortar. Obviously you want a mortar to be somewhat stiffer than a concrete, as concrete is poured, and mortar is spread. It has to be able to stand up on it's own. But, if you make it too stiff, you can't work it at all. The solution is to add more water. For structural members, there are adjuncts called super-plasticizers, which work to improve workability without altering the w/c ratio, but they are not useful/cost effective in a mortar. This is also why I would not consider increasing the amount of sand - you'll lose workability.

Curing: Cement never stops curing. However, we consider 28 days to be a full cure, in which the cement reaches it's nominal strength. Curing is the process by which the cement particles bind to available water particles and hardens. This means that water must be present for the full 28 days! Once the original mix has set, we now must keep the surface moist. You'll see structural pads covered with plastic - that's to reduce evaporation. You'll also see water trucks spraying down freshly poured pads to keep them wet. (High quality pre-cast is often steam cured at the factory) However, in the case of a mortar, our exposed surface is small and vertical, making it difficult to add the required water after the fact, so we must include the excess water in the mix. Of course, this reduces the strength from the w/c ratio, but increases the strength due to the cure.

As you can see, this is actually a very complex science with many factors to consider. The upshot is, that you or I shouldn't be messing with recommended mixes without a really good reason -- We don't have the experience or the knowledge to understand the consequences. -- Industry has determined that the default mixes are the best general-purpose compromise to the many conflicting factors.

A few more point to think about:

• If mortar is too hard, it will not flex and therefore will tend to crack more so separate from the bricks.
• If the mortar is harder than the brick, then the mortar will wear down slower than the brick in rain/wind, other along timer with will leave the mortar sticking out that then collects water and weakens the bricks.
• Harder render also lets less water vapour pass, so may stop/slow building breathing.

Hence sometimes a weaker flexible mortar made with lime (and no cement) is better.

I have been a practicing stone mason for fourty years and I have seen the long term affect of the 1:3 mix compared to the 1:2 mix. With proper preparation from the base under the footing and throughout the build, I recommend the 1:2 mix particularly with flagstone and surfaces that are going to experience a lot of weather. My experience is almost exclusively in stone though and I can see where using the softer mix with brick and block might be more beneficial. (To avoid cracks in the brick or block.) I will say that I have built retainer walls and flagstone structures using the 1:2 ratio over 30 years ago that have no cracks in them to this day.(01/26/16) The 1:2 ratio seems to hold up to water much better than the 1:3. One more note. The freeze / thaw cycle is a major enemy to all masonry work and water intrusion can be very detrimental.