Update Stats Best Practice

Question

Post reply

Update Stats Best Practice

glugingboulder

SSCommitted

Points: 1832
More actions
November 2, 2009 at 3:37 pm

#213082

Hi All,

I am trying to figure out the do's and dont's of manually updating statistics.

1. Is it even nessessary to manually run update stats if auto update and create statistics is enabled? will it look after itself 90% of the time, the other 10% possibly requiring a custom solutions

(please note the percentages are massive generalisation)

2. What are the basic guide lines, ie fullscan on tables with a large amount of data where the sample rate is very small? turning auto stats off (with norecompute) large tables maunally running update stats daily?

3. I understand that updating stats is not a exact science and there are alot of different opinions, but surely there must be rough guidelines for table sample rates on size and records changed.

Any help, ideas's, links are appreciated

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27168 More actions · Answer 1

Generally I prefer default settings Auto update stats and let SQL Server decide when to update stats.

There is no harm in doing it manually but it may lead to overhead on the server. Hence preferabaly if performance is not hampering than no need to do it manually.

Auto update stats not only update stats but also create new stats if required.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

gary1 SSC-Insane Points: 21152 More actions · Answer 2

Hi,

I'm running Update stats Maintenance task job weekly and its taking 1 hr 15 mins and I'm not sure whether Update stats job required to run on production instance. As general guideline, I'm assuming that update stats job needs to be run weekly..

whats the need for running Update stats Manually by using Maintenance plan task, when Auto update statistics option is selected??

thanks

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

gmamata7 (11/3/2009)

Hi,

I'm running Update stats Maintenance task job weekly and its taking 1 hr 15 mins and I'm not sure whether Update stats job required to run on production instance. As general guideline, I'm assuming that update stats job needs to be run weekly..

whats the need for running Update stats Manually by using Maintenance plan task, when Auto update statistics option is selected??

thanks

Before this you would have to tell us what other jobs scheduled on the server? Re-index, re-org etc..

And Also why do you need to update the stats? Have you experienced any performance hit on the server?

Thanks,

einman33 SSCrazy Points: 2813 More actions · Answer 4

einman33

SSCrazy

Points: 2813

November 3, 2009 at 8:07 am

#1074286

Watch updating stats right after an index rebuild. An index rebuild looks at all the data to create statistics, where as the update stats out of the box uses a smaller sample size. You could run into a situation where stats are not as accurate as they could be, causing poor execution plan choices.

gary1 SSC-Insane Points: 21152 More actions · Answer 5

Before this you would have to tell us what other jobs scheduled on the server? Re-index, re-org etc..

On sunday, first rebuild indexes maintenance plan job runs (for all databases, takes 40 mins) and then Update statistics maintenance plan job runs(for all databases, takes >1 hr ).

And Also why do you need to update the stats? Have you experienced any performance hit on the server?

We do not get any complaints about slow performance, but I'm running those jobs to make all indexes are rebuild & statistics are updated

please advice to effectively manage these jobs..

thanks

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

gmamata7 (11/3/2009)

Before this you would have to tell us what other jobs scheduled on the server? Re-index, re-org etc..

On sunday, first rebuild indexes maintenance plan job runs (for all databases, takes 40 mins) and then Update statistics maintenance plan job runs(for all databases, takes >1 hr ).

That is a redundant job because every re-index job also update the statistics. So, I prefer only to have re-index.

And Also why do you need to update the stats? Have you experienced any performance hit on the server?

We do not get any complaints about slow performance, but I'm running those jobs to make all indexes are rebuild & statistics are updated

please advice to effectively manage these jobs..

thanks

Yes, it's good to be pro-active but redundant jobs might be a hog on your system. I would advise you to disable the update stats off when you have re-index job.

glugingboulder SSCommitted Points: 1832 More actions · Answer 7

Interesting, i am thinking about creating a stored procedure to updates stats if they are over 7 days old? or perhaps less.

If auto update is enabled the 7 day threshold should never be a issue.

In a maintence window, i would use full scan on all tables out of date, cannot see any draw backs except the time it takes to run. If you update the stats with resample will it affect the proc cache ie dumping all, or just tables that are used in stored procedures (recompiling procedures that touch the updated tables)?

Is there anything else i should consider?