Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Which database system is this? –  jkj Jan 19 '12 at 9:41
    
It is SQL Server 2005 –  koder Jan 20 '12 at 5:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.