How to Find and Update Outdated Statistics in SQL Server
We can find when the last update statistics happened and how many row modifications happened since the last update statistics using sys.dm_db_stats_properties DMV and a few joins. We can refine this by Data Last updated and total rows and modified rows as needed. --Set the thresholds when to consider the statistics outdated DECLARE @hours int DECLARE @rows INT DECLARE @modified_rows int DECLARE @update_statement nvarchar(300); SET @hours=72 SET @modified_rows=1 SET @rows=1000000 SELECT Update_Query = 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N' ' + QUOTENAME(s.name)+' WITH FULLSCAN' ,Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N' ' + QUOTENAME(s.name) , ddsp.stats_id , last_updated = CONVERT(datetime2(1), ddsp.last_updated) , ddsp.rows, mod_count = ddsp.modification_counter, ddsp.unfiltered_rows FROM sys.schemas sch INNER JOIN sys.objects ...