Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005

    Unanswered: Update stats vs update index stats

    How many DBAs are still using the Sybase 'update statistics' command versus the newer 'update index statistics' command?

    I administer a large third party application (over 800 tables, 1.5 TB) and run the older 'update statistics' due to time constraints.

    I have found a way to reduce the amount of time it takes to run 'update statistics' and I wanted to see if it was valuable to others before I posted the tip.

  2. #2
    Join Date
    Mar 2001
    Lexington, KY
    Well, remember INDEX statistics are used to update the inner columns of compound indexes. Not necessary if you don't have any multi-column indexes.

    Also if you use ASE and up you can specify a sampling for the update to avoid scanning every row:

    But, everyone would probably benefit from your trick if you'd care to share!


  3. #3
    Join Date
    Jan 2005
    Short version:
    Multiple threads with ‘update statistics table_name index_name”.

    Longer proof:
    The command “update statististics table_name” updates the histogram for leading column of each index (as well as information about total number of rows in the table).
    The command “update statististics table_name index_name” updates the histogram for the leading column of that index (as well as information about total number of rows in the table).

    Therefore, given a table
    Create table table_a
    (col_1 int,
    col_2 char(10),
    col_3 int,
    col_4 char(10)
    create clustered index idx_1 on table_a (col_1)
    create nonclustered index idx_2 on table_a (col_3, col_2)

    The following 2 set of statements are equal:

    update statistics table_a


    update statistics table_a idx_1
    update statistics table_a idx_2

    Either approach is fine for a single table, but when you have a hundreds of tables (and support personnel complaining that update statistics takes too long), most DBAs (including me), break out the single job into multiple jobs. These can be run at the same time or on multiple days of the week.

    Splitting the “update statistics tablename” command into 2 threads usual results in a time savings of approximately 45% to 50%. This is usually due to slightly uneven halfs more than the contention caused by running 2 threads.

    Splitting the ‘update statistics tablename indexname’ into 2 threads ends up saving approximately 40% more than the ‘update statistics tablename’ approach. The total savings over the single threaded approach is 70% of the needed time. (My suspicion is that this cause an index scan over a table scan, but this is only a guess as the showplan only says “UPDATE STATISTICS”).

    If you are running “update statistics”, you can add additional threads. If you are using “update index statistics”, you need to be careful on how many threads you run as statistics on “non leading” columns of indexes are generate by sorts in your tempdb.

    Since you mentioned sampling, has anyone seen much benefit in using sampling? My tests have only resulted in approximately a 10% reduction, even when I am using the tricks mention above and sampling at 20%. I haven’t seen much benefit, but again, I don’t routinely run “update index statistics”.

  4. #4
    Join Date
    Feb 2005
    Omaha, NE
    For other tips on making statistics updates run faster, download my Techwave 2003 presentation called "Opening your Maintenance Window" from the sybase website, or at
    Last edited by KSherlock; 02-10-05 at 01:09.

Posting Permissions

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