SQL with Manoj

 
SQL Server Query Optimizer (QO) uses Statistics to create query plans that improve query performance. The QO uses these statistics to estimate the cardinality (number of rows) in the query result.

There are times when these STATISTICS may go stale after certain INSERT, UPDATE and DELETE operations which involves large amount of rows, not qualifying for Auto-Stats Threshold.

For more about Statistics and Threshold check on MSDN Docs link
 

Identify Stale Stats:

Here is a simple query to identify TOP tables that have stale statistics, so that you can create UPDATE statistics for them explicitly:

SELECT TOP 50
	 CONCAT(sch.name, '.', obj.name) AS 'Table Name'
	,MAX(sp.last_updated)			AS 'Stats Last Updated'
	,MAX(sp.rows)					AS 'Rows'
	,MAX(sp.modification_counter)	AS 'Modification Counter'
FROM sys.stats st
JOIN sys.objects obj ON st.object_id = obj.object_id
JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
CROSS APPLY sys.dm_db_stats_properties(obj.object_id, st.stats_id) AS sp
WHERE obj.is_ms_shipped = 0 
GROUP BY CONCAT(sch.name, '.', obj.name)
ORDER BY MAX(sp.modification_counter) DESC;

UPDATE Stats:

UPDATE STATISTICS dbo.table_name
GO