UPDATE STATISTICS

bbrown

Any ideas on ways to reduce the run time required for "UPDATE STATISTICS". Client is increasing their weekly operating hours and the maintenance windows are shrinking.

Marije_Brummel

On which tables should you do this? Most tables do not change that much in an ERP system.

I would rely on indexmaintenance to update the statistics.

bbrown

That's in line with what I'm thinking. That doing a generic "Update Statistics" doing too much work. Much of which has no value. Could you expand on "I would rely on indexmaintenance to update the statistics"? I am also doing an index maintenance task with the following criteria:

Frag < 10% - Skip
Frag >10<30 - REORGANIZE
Frag >= 30 - REBUILD

If I look at the job history I'll see mosting REORGANIZE with REBUILD being very rare. The table that are the most concern are GL and Value Entry. Would I be better off with a more selective "Update Statistics" job?

stryk

How do you perform this "Update Statistics"? With the MP task, or some script? How big is the database?

I'd recommend to use the "sp_updatestats" in combination with "sp_createstats 'indexonly'" as this - in my experience - gives sufficient stats maintenance within an acceptable runtime ...

bbrown

stryk wrote:

How do you perform this "Update Statistics"? With the MP task, or some script? How big is the database?

I'd recommend to use the "sp_updatestats" in combination with "sp_createstats 'indexonly'" as this - in my experience - gives sufficient stats maintenance within an acceptable runtime ...

The database is about 350 GB (used). I switched last night form using a "Update Statistics" script to using sp_updatestats. Runtime went from 90 minutes to 15 minutes. The script was apparently doing too much work. Likely updatign stats on indexes that reallydid not need it.

Thanks for the replies.