Post reply

2 Indexes in same columns, but the column order is different - Whats the difference?

  • September 1, 2011 at 9:56 am

    #390323

    I have an table which has 2 indexes on four columns say,

    col1 asc, col2 asc, col3 asc, col4 asc

    and

    col1 asc, col3 asc, col4 asc, col2 asc

    Is there any difference between these 2 indexes or is one of them redundant?

    Also, in table design for this table, "Recompute Statistics" is set to NO for most indexes. What is it about? Whats the best practive for this setting?

  • September 1, 2011 at 10:08 am

    #1376050

    Nope, they're different indexes.

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    If Recompute stats is off, that means the statistics on those won't get auto updated. If the stats aren't getting auto updated, you must have a manual stats update job or you're going to have badly outdated stats and poor query plans.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 1, 2011 at 10:09 am

    #1376052

    the order of the columns are important.

    i hate the pseudocode of col1,col2, so lets switch to a little more detailsed example.

    suppose you have an index like this:

    CREATE INDEX IX_OrdersTable ON Orders(OrderID,CustomerID,OrderDate)

    the idnex would be ideal for a query that used all three columns in the where clause:

    WHERE CustomerId = 42

    AND OrderID =144

    AND OrderDate = '20110901'

    now, if you know the order number, you wouldn't really need the order date, right?

    a query the FIRST two columns would probably use the index:

    WHERE CustomerId = 42

    AND OrderID =144

    but a query that did not feature the OrderID would not use that index, maybe a different one, or just switch to a table scan:

    WHERE CustomerId = 42

    the leading column of an index needs to exist int he WHERE statementin order to be useful.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • September 1, 2011 at 10:49 am

    #1376062

    Thanks Gail, Lowell.

    So, having "Recompute statistics" to NO, would override the Auto update statistics set for the database?

  • September 1, 2011 at 10:51 am

    #1376063

    Yes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 1, 2011 at 11:26 am

    #1376085

    I am assuming that, Auto Update statistics gets triggered when the changes to the table is about 5% (or 20%) of total rows in the table. So, if that condition is satisfied, auto update would cause a marginal performance degrade, until auto update is completed.

    Setting Recompute Statistics to YES, would mean, that whenever there is modification, the respective inded gets updated immediately, which can also slow a query/degrade performance for little time.

    Am I right?

    Which would be best, Auto Update in DB level or Recompute statistics at index level? My table has 30 million records of which maybe 5000 records(maximum) may undergo modification..

  • September 1, 2011 at 12:37 pm

    #1376114

    balasach82 (9/1/2011)

    Setting Recompute Statistics to YES, would mean, that whenever there is modification, the respective inded gets updated immediately, which can also slow a query/degrade performance for little time.

    Am I right?

    Not at all.

    Which would be best, Auto Update in DB level or Recompute statistics at index level? My table has 30 million records of which maybe 5000 records(maximum) may undergo modification..

    It's not a case of 'which is best'

    Autoupdate statistics is a database-level setting that controls whether stats in the database get updated when stale. Recompute statistics is an index-level option that controls whether that index can have its statistics updated or not.

    If auto-update stats is on and an index is set to recompute = 'yes', then when the stats reach the threshold they'll be updated. If auto-update stats is on and an index is set to recompute = 'no' then that index's statistics will never be updated.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 2, 2011 at 10:16 am

    #1376545

    AS Gail and Lowell have mentioned the indexes are different, but they could be redundant depending on the queries sent to the database. I'd suggest checking sys.dm_db_index_usage_stats to see if they are used. Just remember that DMV is reset when the server is restarted so don't take these as the be all end all.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply