August 28, 2008 at 3:20 am
I have a huge database it’s a critical 24/7 system. I have problems with the indexing some the tables are huge and take an hour to hours to reindex as this is 2000 the users can not work due to locking. We can not go to 2005 at the moment and I can not change the database design. So the only thing I can think of is to frag the large tables instead of indexing. I will then also have to update the stats. But obviously I can not update 100% of the stats.
So how would a Frag and a 10% stats update compare to the DBREINDEX(tablename,’’,90)?
I know index updates the stats but by how much?
Would an update of 15% of stats once a week be better or worse than an 5% update 3 times a week?
Any thoughts please let me know.
Many thanks
August 28, 2008 at 5:56 am
If you can't do a full scan with update stats, use the RESAMPLE option. It means that the DB engine will adapt the % sampled to ensure it gets enough data. Why can't you do a fullscan? Too much IO impact?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2008 at 6:26 am
one table take 2.5 hours just to do a 20% update stats, when it runs even though its a quite period we almost always get user complaints aboyt performance.
Does a dbreindex do a full stats update?
Many t hanks
August 28, 2008 at 10:57 am
DBCC DBREINDEX will do a stats update with full scan as it rebuilds the index.
DBCC INDEXDEFRAG will not update the statistics at all.
I would suggest that you do different stats updates depending on how often the table is updates and how accurate your queries need the stats to be. Don't bother updating the stats if there's been an auto update a few hours earlier (as indicated by the STATS_DATE function). Don't bother updating the stats of a table that you know never changes. If you know that a table has certain values that occur rarely in the column but must be reflected in the stats, then update that with higher sample than other tables with smooth data distribution.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply