When should I run update [index] statistics on a table?
Few of our tables in production server have few hundred million rows( between 200mil and 800mil). We add between 200,000 and 500,000 rows every week. Less than one percent of total rows each of them have. Most of these tables have chronologically increasing index columns like date or identity values. We run update stats on weekends for more than 24 hours. For not having enough maintenance window we run update stats on different group of tables in each weekend. It takes about 4 weekends to cover all the tables.
Do I really need to run update stats on tables that may not have changed much? What is the threshold % data change or kind of criteria I can use to filter my table list so I can cover many other tables to refresh statistics?
I can create & manipulate a metadata table and run update stats in parallel on multiple tables that qualify the criteria. This way I can cover many more tables and reduce cycle time between stats run on each table.
Thank you & regards,