when should you update your Statistics on all your databases? once a week?
Hi imran,
In general, I would do that once a week, and I normally ensure its a full scan. As a good practice to have your index maintenance once a week as well. By default, most database will have auto create stats and auto update stats turn on, keep in mind that auto update stats only perform based on sample, there are cases where sometime it might not pick the best execution plan (for some applications), so you might need to keep an eye on your query plan and how often did those auto update stats kicks in.
Hope this helps.
We rebuild indexes on tables every night, but only if they are fragmented. If we rebuild an index we update statistics (our large indexes are restructured, rather than rebuilt, so no automatic update of statistics). We always do a FULL SCAN.
The way I see it is that if you have a suitable maintenance window why leave the users with poor statistics (and potentially poorly performing queries) until the end of the week if you could refresh them sooner?
Also, rebuilding indexes based on fragmentation means we can do that daily, and again not wait for the "weekend" as the number that need doing each night is manageable.
On a humongous database and/or running 24/7 that might not be possible
We also set Automatic Stats Rebuild to ASync - so user does not have to wait until Stats is rebuilt. I'm not sure why SYNC is the default setting as it has caused us some severe problems in the past.
are you suppose to update the stats of every table in the database
We update stats every 30 minutes on one of our systems.
"It depends."
1 Like