scheduling update statistics

Question

scheduling update statistics

Jai-448139

SSC Eights!

Points: 818
More actions
January 26, 2008 at 6:17 pm

#186007

Hi i want to schedule updatestatistic command for the indexes , my system is 24/7 used. how many times a day must i schedule for updatestatistic .
is there any disadvantage to run this command frequently.
how frequently i must rebuild indexes and what is the use.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Balaji L Hall of Fame Points: 3067 More actions · Answer 1

Schedule the update statistics job on non peak hours. So it wont cause any problems during multiple jobs / batch files running at a time.

Thanks,

Balaji L

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 2

- use auto update stats in the database options. This way you can lower the frequency of your updatestatistics job

Is this a SQL2005 enterprise edition ?

also schedude online index rebuilds !

also include dbcc updateusage (db) with count_rows.

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

Balaji L Hall of Fame Points: 3067 More actions · Answer 3

Update statistics which is used to update the indexes. It will improve the perfomance of the query. If your application is like OLTP db then daily lot of rows can be updated and deleted or inserted. So we need to update the indexes. You can schedule one time a day. As mentioned earlier schedule this job at a non peak hours.

Thanks,

Balaji L

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

As the size of the database increases, sp_updatestats can degrade pretty seriously. You may need to identify those particular tables or indexes that need more frequent updates and use UPDATE STATISTICS against them specifically. Then you could, if necessary, schedule the updates more frequently without serously impacting the entire system.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Ash73 SSC Veteran Points: 286 More actions · Answer 5

I found this article to be very helpful and informative concerning statistics...

http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx