Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: update statistics

    update statistics <table>
    update index statistics <table>

    which one is better for sql engine to determine for best execution plan?

    thx

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    update index statistics <table>

    Quote from
    Performance and Tuning: Monitoring and Analyzing
    Chapter 3 Using Statistics to Improve Performance
    update statistics commands


    The effects of the commands and their parameters are:

    &#183; For update statistics:
    &#183; table_name - Generates statistics for the leading column in each index on the table.
    &#183; table_name index_name - Generates statistics for all columns of the index.
    &#183; table_name (column_name) - Generates statistics for only this column.
    &#183; table_name (column_name, column_name...) - Generates a histogram for the leading column in the set, and multi column density values for the prefix subsets.
    &#183; using step values - Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.
    &#183; For update index statistics:
    &#183; table_name - Generates statistics for all columns in all indexes on the table.
    &#183; table_name index_name - Generates statistics for all columns in this index.
    &#183; For update all statistics:
    &#183; table_name - Generates statistics for all columns of a table.
    &#183; using step values - Identifies the number of steps used. The default is 20 steps. If you need to change the default number of steps, use sp_configure.

  3. #3
    Join Date
    Jul 2006
    Posts
    115
    thx but which one is better for sybase engine to select the best index? seems "update index statistics"?!

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Yes, update index statistics

  5. #5
    Join Date
    Mar 2007
    Posts
    167

    Does update statistics need to run every day?

    Does update statistics need to run every day? Or can we run on weenends only?

    The reason I ask... is becuase we are running every morning, but it's starting to clash with business operations. I'm recommending that we run "update statistics" only over the weekend. Is this sufficient?

    Thank you.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Once a month might be OK.
    It depends on how your data distribution change
    e.g. if you have an index on prodtype and e.g.
    95% has a value of 1
    4% has a value of 2
    1% has a value of 3

    If after adding data this distribution stay the same then the optimizer will not benefit from new statistics and you just waste your time.

  7. #7
    Join Date
    Mar 2007
    Posts
    167

    Thank you! :)

    Thank you for your quick response and the information you provide. The information you provided is very helpful. Sincere thanks.

    Many thanks to you both for posting this topic.

Posting Permissions

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