sql server update outdated statistics

 

Though the auto update statistics are ON, SQL Server wont update the statistics unless it reaches certain threshold  http://support.microsoft.com/kb/195565

If you are using SQL Server 2008 R2 or later version then the below link will be helpful. http://support.microsoft.com/kb/2754171?wa=wsignin1.0

But If you are using the 2005 or 2008 (not R2)  or don't want to enable the trace in R2 as mentioned in above link then below procedure will helps :

1- Update the statistics when the table got modified with in 24 hours delay from last statistics update
2-Updates only the tables which are modified instead all the tables in the database.

CREATE PROC PR_UPDATE_STATS AS with cte as ( select SCHEMA_NAME(b.schema_id) as tbl_schema,OBJECT_NAME(b.object_id  ) as tbl_name, STATS_DATE(s.object_id, s.stats_id) as stat_dt,last_user_update, (select MAX(last_user_dt) from (values (last_user_seek),(last_user_scan),(last_user_lookup)) as all_val(last_user_dt)) as last_seeks_lkps_scans_datetime from sys.dm_db_index_usage_stats  c  join sys.tables b on b.object_id =  c.object_id join sys.stats s on b.object_id = s.object_id ) select 'UPDATE STATISTICS '+ tbl_name  +';' as cmd, tbl_schema , tbl_name , MAX(last_user_update) as last_udpate , MAX(stat_dt) as last_stat_dt ,max(last_seeks_lkps_scans_datetime) as last_select  into #t from cte group by tbl_schema , tbl_name having datediff(dd,MAX(stat_dt),MAX(last_user_update)   ) >=1 -- stats which are outdated for 1 day   declare @cmd varchar(256) declare cur cursor for select cmd from #t open cur     fetch next from cur into @cmd while @@fetch_status =  0     begin     exec (@cmd)         fetch next from cur into @cmd     continue     end                close cur deallocate cur

 

This is a simple and straight forward solution to update the statistics based on capturing what tables are updated since the last update statistics time. But if you want to control update statistics based on threshold (no.of updates ) then you have to collect the metrics with how many updates are happening on each table through collecting sys.dm_db_index_usage_stats on daily basis and modify the stored procedure by adding a parameter where last_user_update > @threshold_limit.