Heat keeps leaking out. The amount of stored heat is proportional to the cube of the linear size, the leak rate is proportional to the surface area (square of the linear size). Hence, the bigger the storage, the higher the efficiency.
Same why a flea can jump >10x its height, and a human cannot (square growth of strength, cubic growth of weight).
The thermal resistance is the sum of 3 (or more) terms, and while the outer terms are indeed proportional to surface area, I'm not sure if the physical constants of the system aren't designed so that the middle heat conductive terms of resistance R=ln(r2/r1)/(2pikL) is the dominant factor.
The other issue is ‘heat quality’ (or temperature deltas). It’s easier to recover a given amount of heat from very hot sources than from only luke warm sources, and smaller heat stores will have a very pronounced difference very quickly, resulting in control issues.
Basically, if you only have a small amount of sand, it’s harder to keep it warm for longer (more surface area for the mass, so more insulation required), and harder to deal with effectively (fan needs to blow much harder when it’s only warm, much less when it’s hot), etc.
When you set up a water tank, you just pump water into it to get the storage medium, and when you need to extract heat, you can pump out actual storage medium and do the heat exchange directly. With a rock storage, you either need pipes in them or move air through the rocks, which requires a much beefier overall system since it's harder to extract the heat. Does that make sense?
Rusting tanks sounds like a problem with either badly engineered mismatched piping, or badly engineered tanks. Ideally, you'd have a well-engineered tank as a closed system with a heat exchange unit next to it.
By the way, I live in a town with a wide district heating system, and there's no storage at all in the individual houses here, only a small heat exchange unit. They are building a 200.000 cubic meter water storage system in the other end, though. I don't know about this particular project, but there are other projects where the water storage is large enough and insulated well enough that they actually do store cheap solar heat from the summer through the winter - and it's cheaper than using gas, even before the current price spikes.
Hot water heaters are always rusting and then leaking. Corrosion is always a problem with water and metal, along with sedimentation and the growth of slime.
With the box of rocks, yes you'll need to duct hot air in to heat them, and duct cool air in to extract the heat and blow it through the house. I don't know that this needs a beefy system, if you've got central air there is already most of the duct work in place.
A tank of water works, too, but tanks rust, mildew, and leak.