Results 1 to 9 of 9
  1. #1
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72

    Unanswered: AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS are Null

    What is the real net affect of these 2 parameters set to NULL?

    How can I tell if stats have been generated for all tables in a db?

    I am guessing that stats don't exist for the db and this accounts for the high degree of scans I am seeing on a 1.5gb db running on a 4 gb memory machine.

    Any thoughts?
    Mike

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    The net effect is that you have to create the stats your self and also update then on a timely basis.

    Personnaly would turn them both on. Some people would argue to turn on the create but do the update on a scheduled basis, the thought is that you can get by with slightly out of date stats rather than take the performace hit to auto update.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72
    so the bottom line is the optimizer will not have any information on the tables and therefore do scans to find the data?

    Is this an accurate description?

    Mike

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    basically, Yes.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72
    and what is the most expedient mthod of generating the stats for all objects?

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    BTW, once you create statistics, you should recompile all views, triggers, stored procs and re-build all indexes starting with any clustered indexes.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.
    Paul Young
    (Knowledge is power! Get some!)

  8. #8
    Join Date
    May 2003
    Location
    California Coast
    Posts
    72
    Originally posted by Paul Young
    turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.

    I just found out each weekend the indexes get rebuilt. Dropped and recreated. I read that whenever an index is created on a table containing data the optimizer collects stats and stores them. Does that still occur when these options are not on?

    Mike

  9. #9
    Join Date
    Apr 2003
    Posts
    30
    Still recommend to turn those two options on. The major purpose of rebuilding index is not for updating statistics. Even if rebuild index will
    update statistics, but the statistics may not be updated during the week.

    BTW, never recreate index by drop-and-create strategy. Try to use DBCC DBREINDEX, or CREATE INDEX with DROP_EXISTING, or DBCC INDEXDEFAG.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •