Update Statistics Maint.plan Job

Question

Update Statistics Maint.plan Job

anzz

SSCertifiable

Points: 5503
More actions
October 13, 2010 at 11:33 am

#147283

In our one PRD server after User DB Reindex job is done Update Statistics job will start
Update Statistics Maint.plan start around: 11:45PM
ending :11:00AM
it is taking almost like 12 hrs to complete the job.
I splited the Update Statistics Maint.plan into two.
like Update Statistics Maint.plan1 and Update Statistics Maint.plan2
which run parallely
Update Statistics Maint.plan1 is taking like 6hrs 28 min 23 sec
Update Statistics Maint.plan2 is taking like 8 hrs 20 min 35 sec.
they are like 32 user databases: total size is 756.0184936523 gigabytes
how to troubleshoot to decrease the time. what does the Update Statistics Maint.plan do ?
Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

you can divide heavy transactional DB and less transaction Db and set their job accrodingly like for "heavy" DB daily and for "less Db" weekly OR you can choose full scan or sample scan option , it will also reduce the job execution time.

Refer this link for "update statistics " details http://technet.microsoft.com/en-us/library/ms187348%28SQL.90%29.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Since it's running after an index rebuild, you only need to update column statistics, not index statistics.

Shouldn't be running that long, I've had TB size databases where the stats update takes maybe an hour. Are you hitting IO bottlenecks perhaps?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

GilaMonster (10/14/2010)
Since it's running after an index rebuild, you only need to update column statistics, not index statistics.

Will it get benefit, if we go for first "update statistics" then for "index rebuild/reorganize" ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

MindOptimizer (10/14/2010)
GilaMonster (10/14/2010)
Since it's running after an index rebuild, you only need to update column statistics, not index statistics.
Will it get benefit, if we go for first "update statistics" then for "index rebuild/reorganize" ?

No.

Either way (before or after) if the update stats does index stats as well as column stats it's wasting time as the rebuild indexes updates index stats.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

GilaMonster (10/14/2010)
MindOptimizer (10/14/2010)
GilaMonster (10/14/2010)
Since it's running after an index rebuild, you only need to update column statistics, not index statistics.
Will it get benefit, if we go for first "update statistics" then for "index rebuild/reorganize" ?
No.
Either way (before or after) if the update stats does index stats as well as column stats it's wasting time as the rebuild indexes updates index stats.

So what sequence we should keep ? first update statistics or index rebuild/reorganize ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Well there's no difference which way around providing you don't have the stats update update the index statistics.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

anzz SSCertifiable Points: 5503 More actions · Answer 7

In all our PRD servers.

Once the Full Backup finishes, I call the Check DB Integrity job from the Full Backup job.

Then the Check DB Integrity job runs and when it finishes it calls the User DB Reindex job.

The User DB Reindex job will call the Update Statistics job.

So I have one job calling the next job.

Now I have issue with Update Statistics is taking to long to complete

as mentioned in my 1st post.

@gilamonster: How do I troubleshoot hitting IO bottlenecks?

and is there any other way to troubleshoot Update Statistics.

What does the update Statistics job do internally?

@MindOptimizer: thanks for your replay. But i am doing that by spliting the job and run it parallel.

I want how to know trouble shoot do decrease the time.

Thanks for your replays

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

ravz.sqldba (10/14/2010)
Now I have issue with Update Statistics is taking to long to complete
as mentioned in my 1st post.

Since it's running after the index rebuild, make sure that (if it's a maint plan) you have the selection set for column statistics only, not all.

@GilaMonster: How do I troubleshoot hitting IO bottlenecks?

I suggest you open your favourite search engine and do some reading. It's a large topic.

What does the update Statistics job do internally?

Depends what options you're running it with. In essence, reads part or all of a table, calculates distributions of row values and updates the stored histogram.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 9

The maint plan has the "statistics only" option.

Otherwise you might be using something based on:

select 'UPDATE STATISTICS ' + quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id)) + ' ' + quotename(name) + ' with fullscan ;'

from sys.stats

where objectpropertyex(object_id,'IsMSShipped') = 0 -- only select user objects

-- statistics only

and ( auto_created = 1

or user_created = 1 )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Avoid maintenance plans!!!

I HIGHLY recommend the incredible and FREE goodness at ola.hallengren.com for all maintenance activities. Learn to use the goodness there!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service