SQL Server Performance

Affect of AUTO CREATE STATISTICS on performance

Discussion in 'Performance Tuning for DBAs' started by gaurav_bindlish, Dec 20, 2002.

  1. gaurav_bindlish New Member

    Hi All,

    This is a very general Performance Related Question. As we know that SQL Server has the option of AUTO CREATE STATISTICS. This option if kept on shall generate the statistics for a column used in the WHERE clause of the query if already statistics are not present on the same. Now my question is whether this option should be kept ON or OFF as keeping this option ON shall lead to load on the server for genearing the statistics and subsiquently maintaining them.

    Similarly for AUTO UPDATE STATISTICS, should this option be set ON or OFF?

    I feel both the options should be set OFF for OLTP system and ON for DSS System.

    Please advice on the same....

  2. satya Moderator

    True, on OLTP database keep these options OFF which will usual performance affect when its ON.
    For our Archive offline servers and database we keep this ON to give optimum performance and regular check thru about performance article from this site.


    Satya SKJ
  3. bradmcgehee New Member

    Generally speaking, I recommend that Auto Create and Auto Update Statistics be turned on all databases, except for very busy OLTP databases.

    One way to find out if Auto Create and Auto Update Statistics is a problem or not is to gather a Profiler trace and track the Missing Column Statistics event and the Auto-Update Stats event, along with any other events you want to track. The first event, Missing Column Statistics, will tell you how often a query could have used column statistics, but couldn't because they did not exist. If you see this a lot, you may want to leave Auto Create and Auto Update Statistics on, or at least update stastics at least once a day. The second event, Auto-Update Stats, tells you how often Auto Update is working. If this is a huge number, then consider turning this feature off, but if the number doesn't appear to be too high, then leave this setting on.

    Brad M. McGehee
  4. satya Moderator

    For optimum performance its advised to keep regular DBCC and maintenance checks on the database.

    Satya SKJ
  5. royv New Member

    Don't forget that if you turn off these features, it becomes that much more important that you run the appropriate update statistics functions as satya has suggested. That is why it is recommended in general to keep these as on.

    "How do you expect to beat me when I am forever?"

Share This Page