As you may all know, having up to date index statistics is the key to make sure the query optimizer can choose the most optimal execution plan for any query.
Here is DMV query that you can use against a database to see if there are statistics that have not been updated and have had good deal of data modifications in the underlying tables.
SELECT TOP 100 OBJ.NAME, STAT.NAME, SCHEMA_NAME(OBJ.SCHEMA_ID) SCHEMA_NAME, OBJ.CREATE_DATE, OBJ.MODIFY_DATE, C.NAME AS COLUMN_NAME, SP.* FROM SYS.OBJECTS AS OBJ INNER JOIN SYS.STATS AS STAT ON STAT.OBJECT_ID = OBJ.OBJECT_ID INNER JOIN SYS.STATS_COLUMNS AS SC ON STAT.OBJECT_ID = SC.OBJECT_ID AND STAT.STATS_ID = SC.STATS_ID INNER JOIN SYS.COLUMNS AS C ON SC.OBJECT_ID = C.OBJECT_ID AND C.COLUMN_ID = SC.COLUMN_ID
CROSS APPLY SYS.DM_DB_STATS_PROPERTIES(STAT.OBJECT_ID, STAT.STATS_ID) AS SP
WHERE MODIFICATION_COUNTER > 10000 AND OBJECTPROPERTY(OBJ.OBJECT_ID, 'ISMSSHIPPED') != 1 AND LAST_UPDATED > GETDATE() - 30 ORDER BY MODIFICATION_COUNTER DESC;
Note: Now just because a statistics is out of date does not mean it’s affecting the query performance. You may have statistics that are there but are no longer being used by the optimizer, maybe because they were auto created in the past or its part of an unused or duplicate index. Those stats can be candidates for cleanup.