How to Find Outdated Statistics

The query optimizer needs SQL Server statistics to develop a successful and cost-effective execution plan. These statistics give the query optimizer information about the distribution of column values, which aids SQL Server in calculating the number of rows.  Regular updates should have been sent to the query optimizer. Inaccurate statistics could lead the query optimizer into using costly operators like index scan rather than index seek, which would lead to high CPU, memory, and IO concerns in SQL Server. We might also encounter deadlocks and blocking that eventually affect the underlying queries and resources.

The data distribution or histogram value is changed by DML operations like insert, update, and delete that we regularly execute. These procedures could result in statistics being out of date, which could affect how effectively the query optimizer works. Auto Update Statistics true is the default setting in the SQL Server database.

The query optimizer updates the SQL Server update statistics when they become outdated.

It is better to do index maintenance in advance to avoid outdated statistics. When the optimizer generates an insufficient plan, we understand that it is outdated. Hence, even though some tables may not have had recent statistical updates, we will still get good plans. Moreover, some tables may require statistics to be updated more frequently than once per day.

In actuality, outdated statistics don't exist in SQL Server. The numbers may become outdated and no longer be as useful as they are right now. Also, it is important to comprehend what older means.

Script to find Outdated Index Statistics Information:

SELECT DISTINCT

OBJECT_NAME(s.[object_id]) AS TableName,c.name AS ColumnName,s.name AS StatName,

STATS_DATE(s.[object_id], s.stats_id) AS LastUpdated,

DATEDIFF(d,STATS_DATE(s.[object_id], s.stats_id),getdate()) DaysOld,

dsp.modification_counter,s.auto_created,s.user_created,s.no_recompute,

s.[object_id], s.stats_id,sc.stats_column_id,sc.column_id

FROM sys.stats s

JOIN sys.stats_columns sc

ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id

JOIN sys.columns c ON c.[object_id] = sc.[object_id] AND c.column_id = sc.column_id

JOIN sys.partitions par ON par.[object_id] = s.[object_id]

JOIN sys.objects obj ON par.[object_id] = obj.[object_id]

CROSS APPLY sys.dm_db_stats_properties(sc.[object_id], s.stats_id) AS dsp

WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1

AND (s.auto_created = 1 OR s.user_created = 1)

ORDER BY DaysOld;

Result:

How to fix outdated statistics , run below query each day or create maintenance plan

EXEC sp_updatestats;

GO