DBCC Updateusage versus update statistics/sp_updatestatistics
-
Good morning. I've read the Microsoft web site about the two definitions, but I'm still a bit unclear. Update statistics says that it updates statistics on key values, whereas Update usage updates space information. Which one should be used after re-indexing? Thanks.
Chris
-
They actually do different things, I always do both after index rebuilding, just to be sure. It's never hurt my databases. dbcc updateusage is quite hotly debated, I always figure if you run it and it updates then it can't be a bad thing!!! ( It updates the system tables with table data )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Executing a sp_updatestats after a reindex might not be a good idea. When you rebuild an index SQL Server updates the statistics for that index with a full scan (since he has to read the whole index structure anyway). When you execute sp_updatestats you replace the most accurate statistics with statistics based on a default sample set.
-
Hey Chris:
Neither of DBCC commands is necessary after a re-index, I’m assuming your talking about a DBCC DBREINDEX. See Create Index which automatically re-computes out of date statistics. I do not used the DBCC Updateusage, with create index. I’ve not seen any value. In earlier versions of SQL Server 6.x, there may have been some performance gains with DBCC UpdateUsages.
Dan Pitta
-
I'm not convinced a reindex does a full scan ( and if you have auto stats set on your database this too would reset your stats to 10% so I suspect the full scan is not correct ) and it depends very much upon whether all your tables have clustered indexes on what effect a dbcc re-index actually has. There are also stats on tables , apart from indexes, update stats will get all these. I know the sp_updatestats is essential , seperate to reindexing, as I've resolved many performance problems adding this command to a database maint job. A classroom database may not benefit but real world databases rarely match any ideals.
As to dbcc updateusage, well keeping the system tables in step with the details of your row counts and index pages might not have too much impact - the official word is that it keeps the space usage up to date so if you use spaceused you should get correct results. Again I've found advantages to using this through 6, 6.5 , 7.0 and 2000 and will continue to use this in 2005.
I'd actually say that sp_updatestats is more improtant than an index rebuild.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Colin,
If you do a reindex the stats for that index are rebuilt with a fullscan. You can easily check this with DBCC SHOW_STATISTICS. The auto stats resets to the default sample set only after they have fired so at least you have fullscan stats until 500 + 20% of the rows change (column based in SQL Server 2005).
Obviously not all the stats are updated for that table, just the index stats of the index you rebuild (also non-clustered). This being said, nothing prevents you from running the update stats before you reindex to have at least the most accurate stats on the indexed fields.
I wouldn't call updatestats more important than a reindex. Having an OLTP system with massive page splits can kill your performance because of page fullness. So at least in an OLTP environment having the right fill factor and a regular reindex is mandatory.
HTH
-
We perhaps need to be precise about what we are talking about:- a dbcc dbreindex with the table passed will not only rebuild all the indexes but will also update all user created stats and auto created stats.
Passed index names only rebuild that index and a rebuild of a clustered index ( named ) does not rebuild any of the secondary indexes in no case will any of the stats will be updated.
So .. if you're blanket dbreindexing all your tables ( not by index name ) then you're right you don't need stats updated.
As to the scan %age I'm not 100% sure of this currently, so I apologise there.
As to page splits and stats - well I disagree as generally most data will be in cache whereas page splits etc. affect physical storage, so in this case the stats are far more imprortant than the physical storage, there's going to be movements in cache to be sure but page splits and fragmentation affect the physical storage, a select from a fragmented table does not leave the data in cache fragmented - this is one of the major performance enhancements of adding memory to a server.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Colin,
As I said before it all depends of course 🙂
I agree most pages will be in the cache but I have seen systems where the clustered index is on guid fields. This leads to terrible fragmentation and page splits (even though a page is in cache it has to be written to disk some point in time) and this causes much grief if you don't reindex on a regular basis. Obviously this all depends on your choice of indexes. If you choose an ever increasing key as a clustering key you will have far less problems with this (which I would recommend anyway for most systems).
Kind regards
-
ah! stupid idea using guids for a clustered index!! sort of fixed in 2005.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply