Update Statistics Strategy after Index Maintenance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Statistics Strategy after Index Maintenance

Author

Topic

Soumya Kar
Starting Member

7 Posts

Posted - 2012-01-19 : 01:25:29

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.

Thanks.

Kristen
Test

22859 Posts

Posted - 2012-01-19 : 04:11:20

Something I'm not understanding here:

If (is that right?) you are rebuilding all indexes then surely you don't need to update statistics on any of them?

We use REORGANISE index on indexes which are large, and then do an UPDATE STATISTICS on that index, but on smaller indexes we use REBUILD INDEX (and NO update of statistics)

Soumya Kar
Starting Member

7 Posts

Posted - 2012-01-19 : 23:58:01

Kirsten, I guess I was not completely clear on the question. We are not doing a rebuild for all the indexes. We are only doing rebuilds for indexes which are 30% or more fragmented. For the others which are between 5 - 30% fragmented, we are doing reorgs. The issue is, like you rightly said, that for indexes which are rebuilt, we do not need to do update statistics. But for the ones which are reorganized and other indexes on columns whether auto-created by SQL Server or manually created, we will need to do update the statistics. I was only trying to figure out a way to filter indexes which have already been rebuilt from the update statistics list since we use a separate SP to update the statistics for all the tables in the DB. Hope I was able to make myself clear now.

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 05:17:50

"The issue is, like you rightly said, that for indexes which are rebuilt, we do not need to do update statistics."

So Update Statistics on the indexes AS you Reorg them - i.e. in the code where you do INDEX REORG then immediately do an UPDATE STATS on that index - that's what we do. Don't leave it until a separate task.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-20 : 06:03:47

You also need to update the column statistics, as they don't get touched by index rebuilds.

--
Gail Shaw
SQL Server MVP

Soumya Kar
Starting Member

7 Posts

Posted - 2012-01-23 : 01:17:32

Thanks a lot for all the responses. I agree with you Kristen on that I could have done an update statistics call right after I do a reorg. Bit, the way I see it, there are two issues with the solution:
1. Like Gail mentioned, we also need to update the auto-created/manually created statistics for the columns.
2. If we call the stats update from the defrag task, then we are looking at a very tightly coupled solution.

I did come up with a solution which achieves the above purpose but still keeps the tasks separate for future extensibility. 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources