mibuso.com

Microsoft Business Solutions online community
It is currently Tue May 21, 2013 6:22 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 5 posts ] 
Author Message
 Post subject: [Solved] UPDATE STATISTICS
PostPosted: Sun Mar 13, 2011 10:05 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
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.

_________________
There are no bugs - only undocumented features.


Last edited by bbrown on Mon Mar 14, 2011 12:18 pm, edited 1 time in total.

Top
 Profile E-mail  
 
 Post subject: Re: UPDATE STATISTICS
PostPosted: Sun Mar 13, 2011 10:47 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 3834
Location: Olst
Country: Netherlands (nl)
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.

_________________
Mark Brummel | Freelance Dynamics NAV (Navision) Developer
Author of the book : Microsoft Dynamics NAV 2009 Application Design

MY BLOG : http://www.brummelds.com


Top
 Profile E-mail WWW  
 
 Post subject: Re: UPDATE STATISTICS
PostPosted: Sun Mar 13, 2011 11:09 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
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?

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: UPDATE STATISTICS
PostPosted: Mon Mar 14, 2011 11:32 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 639
Location: Nürnberg
Country: Germany (de)
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 ...

_________________
Jörg A. Stryk (MVP - Dynamics NAV)
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool


Top
 Profile E-mail WWW  
 
 Post subject: Re: UPDATE STATISTICS
PostPosted: Mon Mar 14, 2011 12:18 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
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.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: