Auto Create/update Statistics

DeSp

Do you use AUTO CREATE/UPDATE STATISTICS switches with your Navision database or just run a job (maintenance plan) on some schedule? What is better and what is recommended in case of performance troubleshooting? What is the optimal job schedule in this case?

Thanks in advance.

kriki

Better don't AUTO CREATE STATISTICS, but use a job.
The job, you can create with DB Maintenance. Launch the job once a day (at night when no one is working is the best moment). Best use the highest possible sample percentage. If you notice the job takes too much time (BIG DB), you can make the percentage smaller, but best not under 10%.

Remember also to recreate the indexes at least once a week.

DeSp

Better don't AUTO CREATE STATISTICS, but use a job.
The job, you can create with DB Maintenance. Launch the job once a day (at night when no one is working is the best moment). Best use the highest possible sample percentage. If you notice the job takes too much time (BIG DB), you can make the percentage smaller, but best not under 10%.

Ok, and what is about AUTO UPDATE STATISTICS? Is it necessary (and better) to update it through the day till the next statistics creating? If it is, then is it better to auto update it or to update it by a job, for example, several times a day? How much does a frequent statistics updating affect performance? The DB is about 60GB with 52 users.

Remember also to recreate the indexes at least once a week.

What is the recommended fillfactor for reindexing?

kriki

-AUTO CREATE STATISTICS is not needed
-create SQL statistics with a job once a day (at night when no one is working)

-fillfactor : depends on the table, but I think if you want to use a value for ALL tables, you can use 10%. This leave 10% space to add new records.

DeSp

Sorry for possible misunderstanding.
Now it is clear for me that it is better to use job instead of AUTO CREATE STATISTICS.

-AUTO CREATE STATISTICS is not needed

Do you mean AUTO UPDATE STATISTICS by that?

kriki

DeSp wrote:

Sorry for possible misunderstanding.
Now it is clear for me that it is better to use job instead of AUTO CREATE STATISTICS.

-AUTO CREATE STATISTICS is not needed

Do you mean AUTO UPDATE STATISTICS by that?

Yes. :whistle:

SQLGuru

hi all,

depending on sql server version:

in 2000 better turn off the auto-features (auto create/update statistics) because the developer did not finish this function... that is, it doesn't work correctly. use sp_createstats 'indexonly' and sp_updatestats instead.

in 2005 turn on async. update of statistics and turn off sync. update stats. always turn on create statistics.

fillfactors depend on:
- no of rows in table
- estimated data growth
- maintenance interval
- the nature of the key (Entry No and such need 100%)

my tool SQLSunrise NavTune calculates them very precisely... and also maintains all necessary index statistics!
it also fixes many other index issues...

if you want to use a one for all fillfactor: for small installations (<15GB) SQLSunrise tends to averages it at around 92, for larger ones (>20GB) the fillfactor is higher (up to 97%).

But again: the fillfactor should be calculated individually PER INDEX, not per table and not per database or even server!
If you choose it too big, page splits will be the result, and if chosen too small, page reads may increase dramatically. in either case, performance loss will occur.