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.