I am looking to buy a Caldwell Lead Sled DFT shooting rest. It suggests to place four 25lb bags of lead to hold the rest down and to absorb the recoil. They say lead is the best material to absorb the most recoil, sand is next best choice.

What size lead pellets should I buy to absorb the most recoil?

Fine No. 9, larger BB or doesn't matter as long as the weight is there. Thinking of getting 2 bags of No. 8 and 2 bags of No. 4.

It won't matter if its 9's or 2's. 25lb bag is 25lb bag. Just get 4 bags of what you can find and go from there unless you are planning on using them to reload later. Sand will be much lighter in weight than lead shot. We have used 25lb steel bench weights and they work great as well.

Go to your local tire store, and get the bad wheel weights.
You can get them for a fraction of the cost of bags of shot.
And later on you can melt them down to make bullets.

JMHO

Good luck. Any tire store within a 100 miles of are taking them to the scraper for $$$$ and not giving them to anyone. This way they don't have to weigh them or anything. Just load them up and drop them off.
That and they are dirty,greasy, nasty. Wouldn't sit right on the sled for what you are needing. Not too mention if you are planning on moving it around a bunch. I melt them down for bullets, decoy weights and fishing weights. Pretty big mess to deal with and not get dirty while handling weapons and ammo.