SQL Server Performance

Update Statistics Strategy after Index Maintenance Activity

Discussion in 'ALL SQL SERVER QUESTIONS' started by snkar, Jan 19, 2012.

  1. snkar New Member

    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:
    Code:
    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:
    Code:
    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:

    Code:
    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.
  2. FrankKalis Moderator

    Welcome to the forum!
    Good question! Since STATS_DATE() returns the date for the most recent update, I would think that it also returns the date from a stats update that was initiated by the Query Optimizer. So, there might be chances that between the index rebuild and the update stats tasks such an update kicks in and might fool your check. Or, what happens if you cross midnight between both tasks? I guess you're on the safer side, if you keep your own track in a special table of when which index was most recently rebuild and then use this in your filter. So, maybe something like this:

    empty index table
    index rebuild task
    • do index rebuild
    • record that operation in index table

    update statistics task
    • do update statistics for everything not present in index table
  3. snkar New Member

    Thanks a lot Frank for the response. I completely agree with you on the points that you mentioned about the date check problem. I did come up with a solution which is somewhat similar to the one that you have suggested. I have two SPs -- one for defragmentation and the other for stats update. I decided to call the stats update SP from the defragmentation SP passing it the list of indexes, in the form of xml, which has been rebuilt during that run. In the stats update SP, I just filter out those records before going for the update. The Stats Update SP has also been coded with the RebuildIndexList input parameter as optional so that, in case required, it can be called independently. Hope the solution should be ok. If you find any apparent issues, do let me know. Since I am not very experienced in SQL Server, I am not very sure about the pros or cons of any particular approach.
  4. FrankKalis Moderator

    You might find minor issues along the way, but the general approach sounds good to me.

Share This Page