May 22, 2012 at 3:04 am
I have found a couple of indexes which have fragmentation at about 20% every week.
These indexes always get into a (if fragmentation <30% then Reorganise) query.
As such, this index has not been rebuilt for 1 year, since every week, the fragmentation is sitting at around 20%.
Reorganising does not update stats, so it looks like we have a reorganised index with stats that are one year old,
according to the stats_date for these indexes.
But I am not sure if I should take that value as true since the DB is set to auto update stats, and despite this, the stats date is old when querying the age.
Should I leave everything as is, or should I change the job to also include a check against stats age to determine whether to rebuild the index.
Or should I rather explicitly update stats in these cases without a rebuild.
Are there any other related issues that I am not taking into account?
On the other hand, rebuilding all tables with rowcount less than 40 million takes about 2 minutes so in this case, is it rather better to rebuild, considering the SAN is that fast?
May 22, 2012 at 4:07 am
From the sounds of things, you could just leave stuff as is. It doesn't sound like the data is so volatile that your stats are getting updated.
I don't think doing the rebuild is going to hurt you, especially since your rebuilds appear to be so fast.
The only thing you didn't suggest was the possibility of doing a full scan stats update on the tables with older statistics. The auto update stats will only fire after 600 +20% rows have changed, so it might be time to do the update manually, just to be sure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2012 at 6:08 am
Will a rebuild also update stats with fullscan or will it do sampling?
Currently I'm thinking of changing the job to do a reorganise daily based on fragmentation and an indiscriminant rebuild weekly.
I'm getting a sense that this is overkill.
May 22, 2012 at 6:16 am
MadTester (5/22/2012)
Will a rebuild also update stats with fullscan or will it do sampling?Currently I'm thinking of changing the job to do a reorganise daily based on fragmentation and an indiscriminant rebuild weekly.
I'm getting a sense that this is overkill.
Rebuild does a complete scan. It's effectively the same as creating a new index, so you get very up to date statistics.
Based on some tests I've seen from Brad McGehee, if I had to choose, I'd just rebuild every time from now on. Reorganize only provides benefits within a narrow range or when you just can't afford to rebuild.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2012 at 3:58 pm
Rebuilding indexes updates the statistics that pertain to those indexes. If you have any manually created statistics, or 'auto create stats' is enabled (default) in your database then you may also want to consider updating your column statistics, with or without fullscan depending on how of a maintenance window you have available:
UPDATE STATISTICS schema_name.table_name WITH FULLSCAN, COLUMNS;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply