Would it be worth my effort to weather-proof my deck even if the wood looks like it’s old and dry-rotted?
I don’t have the answer to that. Maybe someone else will.
Most of the deck treatments on the market like Thompsons Water Seal are paraffin wax in a mineral sprits solution. The mineral spirits evaporates leaving the wax impregnated into the exterior of the wood. They do very little other than seal the existing wood.
There is a difference between splitting and cracking due to changes in relative humidity in the summer/winter and actual dry rot. Rot is when the wood becomes punky or soft. If this is the problem, it may be best to replace the deck boards.
Is the deck on the south side of the house where it gets a lot of sun? If this is the case, I would replace it with composite decking with a PVC shell. These typically have a anchoring system unique to each manufacturer.
The other alternative would be to screw down IPE(ironwood) or Garapa wood decking. These are two South American hardwoods that are impervious to rot. They are also EXTREMELY dense and difficult to work with. They are the hardest/densest woods commercially manufactured in the world. You must predrill ALL holes and screw it down with stainless steel screws.
If your existing deck just has a lot of splits and cracks you could wash it with bleach and a pressure washer. Then give it a couple weeks to dry out. I would then apply a heavy pigmented deck stain(almost like a paint). The lighter the color the more sun it will reflect.
I hope this helps.