I have billions of history data in tables. The jobs do an insert/ append into these tables. Can anybody recommend what is the right time to refresh stats :
After each insert/append job?
At the end of all jobs?
Since there is huge data the Refresh stats takes a lot of time!
The first thing is collect only the stats required, not all stats. It will save a lot of time and provide you performance.
As stats collection after every insert will take time and prolong the batch time, I would suggest to schedule the stats collection in non business hours after all jobs.
Stats are important for source tables in the INSERT/SELECT query. Stats on the target table are not (unless that table is also a source).