When do we know we need to run statistics?

Question

Post reply

When do we know we need to run statistics?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
November 1, 2006 at 3:14 pm

#153271

I mean, do we have any criterion?

Viewing 9 posts - 1 through 8 (of 8 total)

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

RichB SSCrazy Eights Points: 9651 More actions · Answer 1

queries suddenly getting real slow for no good reason is a bit late I guess...

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

But how do we know it is due to index fragment or out of date statistics before running restatistics?

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

They should be run on a regular basis, just as you would in Oracle (daily, weekly). Basically most tasks that you set up for maintenance in Oracle to keep your instances and schema(s) running smoothly have corresponding SQL Server counterparts. There are quite a few folks on this site with DBMS experience other than SQL Server (Oracle, DB2, UDB2, Sybase) that may respond. Why don't you post some of your thoughts on how you maintain your Oracle instances and we'll see what we can do to translate them in SQL Server.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

babban ali SSC Journeyman Points: 93 More actions · Answer 4

How to avoid the self blocking in sql server it degrade the performance.

Please give me the ASAP reply

Thanx

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 5

Performance tuning is all about establishing baselines and trending statistics so that you can evaluate and diagnose performance.

Best practices should establish regular maintenence routines and processes to optimise the database(s) and server.

If you don't at least start here then you'll be doing the proverbial headless chicken when something goes wrong.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 6

All replies indicate that we should run statistics in a regular basis. But how often? What is the criteria to make such a decision?

Many thanks for all of you for your inputs.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 7

As a preventative measure I execute UPDATEUSAGE, UPDATE STATISTICS, sp_recompile and sp_refreshview on a daily basis for all but my largest databases (those less than 50 Gb). On the larger databases, weekly. Maybe it's overkill but my servers seem to function quite well.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 8

agree with Rudy, if you have the window to do so - do every day. It's quite interesting to note that systems initially set to run stats once a week when small may run into scaling issues as data grows and need stats every day. As databases get bigger and windows smaller you need to get smarter and handle individual indexes/tables/columns/stats etc. Note that a total table dbreindex will get all the stats too - but selective index rebuilds don't. Use sp_autostats tablename to see when stats were last updated.

If you have auto update stats turned on this may help ( but not always )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/