Since Index Rebuilds will update statistics data for the concerned indexes with full scan, so it is not advisable to update statistics on those indexes using the Update Statistics with default sample size. This will worsen the statistics data captured. In that case, when we are doing a statistics update operation on all the tables in the database, is it a correct strategy to eliminate those indexes from the update process for which the indexes are already up to date(based on the assumption that we are running the Index rebuilds before the statistics update). For example, lets say that I am using the following cursor to get a list of all indexes in the DB for which update is required:
SET @index_names = CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR
SELECT NAME ,indid ,rowmodctr
FROM sys.sysindexes
WHERE id = @table_id
AND indid > 0
ORDER BY indid
and use the following condition to check if we need to update statistics:
IF ((@ind_rowmodctr <> 0))
But this will update statistics for all the indexes irrespective of whether an index already has its statistics updated due to a rebuild.
Now assuming that we are running the Index Rebuild task and the statistics update task on the same day and in that order, can we use the following filter to eliminate the indexes which has already been rebuilt:
SELECT NAME AS index_name
,CONVERT(VARCHAR(10), STATS_DATE(object_id, index_id), 111) AS statistics_update_date
FROM sys.indexes
WHERE object_id = OBJECT_ID('CM_Project')
AND CONVERT(VARCHAR(10), STATS_DATE(object_id, index_id), 111) <> CONVERT(VARCHAR(10), GETDATE(), 111)
If this not a correct/optimal way to achieve this, can you please suggest a standard way to get this done.
