July 9, 2017 at 5:50 am
Morning All,
I am hoping someone could clarify something regarding statistics and indexes.
I've found a job on a server (legacy, we just took over and absorbed into our estate) that performance a reindex (rebuild and reorganise depending on fragmentation level) and then the second step is an update statistics using the default scan rate, in this case 5%.
I was under the impression that a reindex performed a update statistics with full scan anyway. So is this step redundant, in fact, is it detrimental. Replacing good statistics with inferior ones immediately after?
Also, I cannot find online if there is a different in how statistic are rebuilt if one performed a rebuild or a reorganise -- do they treat statistics the same way?
Is it possible to identify what scan rate was used the last time statistics were recreated?
Cheers
Alex
July 9, 2017 at 6:42 am
alex.sqldba - Sunday, July 9, 2017 5:50 AMMorning All,I am hoping someone could clarify something regarding statistics and indexes.
I've found a job on a server (legacy, we just took over and absorbed into our estate) that performance a reindex (rebuild and reorganise depending on fragmentation level) and then the second step is an update statistics using the default scan rate, in this case 5%.
I was under the impression that a reindex performed a update statistics with full scan anyway. So is this step redundant, in fact, is it detrimental. Replacing good statistics with inferior ones immediately after?
Also, I cannot find online if there is a different in how statistic are rebuilt if one performed a rebuild or a reorganise -- do they treat statistics the same way?
Is it possible to identify what scan rate was used the last time statistics were recreated?
Cheers
Alex
Have a look at this article, Rebuilding Indexes vs. Updating Statistics by Benjamin Nevarez.
😎
July 9, 2017 at 7:50 am
The article Eirikur referenced explains the effect rebuild and reorganize on statistics. Nice find, Eirikur.
If you want to query how many rows were sampled during the last statistics update, use the sys.dm_db_stats_properties DMV.
July 9, 2017 at 8:04 am
Guys that's brilliant, thanks a lot.
Both really great links!
July 9, 2017 at 10:25 am
alex.sqldba - Sunday, July 9, 2017 5:50 AMI was under the impression that a reindex performed a update statistics with full scan anyway. So is this step redundant, in fact, is it detrimental. Replacing good statistics with inferior ones immediately after?
Partially correct
Also, I cannot find online if there is a different in how statistic are rebuilt if one performed a rebuild or a reorganise -- do they treat statistics the same way?
They do not. Rebuild updates the stats as part of its operation. Reorganise does not touch the statistics at all. So that update stats step is detrimental if the index was rebuilt, but necessary if the index was reorganised (though I'd recommend update with FULLSCAN, not a 5% sample)
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
July 9, 2017 at 10:57 am
Is there a way to selectively update statistics? Instead of performing full scan or 5 % to all the statistics, the statistics where modifications have been made, only update those stats ?
July 9, 2017 at 11:38 am
Sure. Use the sys.dm_db_stats_properties DMV to decide which stats to update.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply