July 2, 2015 at 9:31 am
Hi,
I need some help when we have to run update statistics?
How often do we need to run the update statistics in production databases (Assume Auto update stats are enabled in database level)?
Do we need to run update statistics in subscriber database (Transactional Replication)?
One of the other database tables data will be truncate and reload everyday (data will be load from subscriber database), still do we need to run update stats on this database also?
July 2, 2015 at 10:23 am
There is absolutely no hard and fast rule on this at all. It just depends on your data, how fast it comes in, and how much the data changes are going to affect statistics in a way that affects plan choice. Yes, you should manually update statistics for replicated databases too.
One thing to watch out for is using sp_updatestats. While this makes statistics maintenance easy, it's not necessarily more accurate. It always does a sampled scan of the statistics. If you have index maintenance that is defragmenting your indexes, you don't want to run statistics updates on those indexes since their statistics have just been rebuilt. Same thing goes with the table you drop & recreate. If you add an index at the end of that process, that index has a full scan to create it's statistics.
"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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply