As with most things political, rhetoric and reality don't often align here.
The theory presented in your quoted segment is that sunset provisions ensure that weak or inefficient agencies die early deaths through a sort of 'survival of the fittest' theory. There's a whole arsenal of accountability tools for ensuring that policies are doing what they're supposed to do, and many are less complicated than setting a timer and hoping the ticking clock will generate enough urgency to revisit the matter. I am not aware of sunset provisions being used in this way.
There's two main uses for this structure that are common, however:
The first is when whatever policy intervention you're developing is meant to be temporary by design. In my own field, this is stuff like renewable energy subsidies and tax credits - the idea is that once stimulated to begin installations, technological learning will kick in and bring down the price of solar panels (a very common effect for new technologies), eliminating the need for the subsidy. You could promise to repeal the tax credit down the road, but that's just giving future-you more work to do. Since you can always repeal/amend the law later if the subsidy goes totally bonkers, or needs more time, it's not super critical that you get the exact timing down out of the gate.
Sunset provisions are also commonly used to make bills more palatable to unpersuaded legislators, and oftentimes opposition legislators. It's a much smaller ask to say "let's try this program for five years and if we don't like it, it automatically dies." It gives legislators who represent districts or constituencies where the policy is unpopular a rhetorical way out of the corner that they would otherwise vote themselves into.
