Update Statistics Takes AGES

[HestonJames]

Hello Guys,

Just wanted to run this past you as I have what I think to be odd behaviour on one of my maintenance tasks. The task does all the usual things created by the maintenance plan wizard on a handfull of databases, such as check integrity, rebuild indexes, reorganize indexes, shrink, update statistics and backup.

Now I've noticed that the maintenance plan currently takes around 5 hours to execute, looking through the history logs it seems all elements of the task take around 1 to 5 minutes each to complete and yet the 'update statistics' part takes an entire 5 hours.

Is this what you would expect to see? or is something afoot with that part of the plan?

This is after a move to a new higher spec server, I'm sure the maintenance plan on the old server was identical and would only take around 25 minutes to complete so I'm sure something is wrong here.

If you need any additional information then let me know. I appreciate your thoughts.

Heston

 

[HestonJames]

Hmmm, after looking through the databases it seems that one of the tables has 299 statistics compared to all other tables which only seem to have maybe 4 or 5.

Even though this is a fairly high traffic table this to me would seem odd. I know very little about statistics, this would certainly explain why it takes so long to update them all.

Should I have all these stats on the table or are they left over from me doing some performance testing somewhere?

Cheers guys, really appreciate any help.

Heston

 

[HestonJames]

Ok, after looking at this a little more it seems these are all left from some database tuning advisor task which must have crashed out at some point, they're all prefixed with _dta_. I'll look at removing them.

 

[HestonJames]

Solution to this problem can be found here:

http://www.tek-tips.com/viewthread.cfm?qid=1555306&page=1

Heston