Options

Auto Create/update Statistics

DeSpDeSp Member Posts: 105
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.
Nil desperandum

Comments

  • Options
    krikikriki Member, Moderator Posts: 9,090
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    DeSpDeSp Member Posts: 105
    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?
    Nil desperandum
  • Options
    krikikriki Member, Moderator Posts: 9,090
    -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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    DeSpDeSp Member Posts: 105
    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?
    Nil desperandum
  • Options
    krikikriki Member, Moderator Posts: 9,090
    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:
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • Options
    SQLGuruSQLGuru Member Posts: 13
    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.
    Bodo Michael Danitz
    MCT, MCITP SQL Server 2005

    Author of SQL Server 2005 MOC exam items
    and SQLSunrise NavTune:
    http://www.sqlsunrise.com
Sign In or Register to comment.