SQL Server Performance

double indexes

Discussion in 'T-SQL Performance Tuning for Developers' started by SalsaFreak, Aug 28, 2007.

  1. SalsaFreak New Member

    Hi there,
    I want to check if my tables have double indexes on the same column. Is there a select statement that I can do on the sysindexes?
    Regards, Salsa
  2. alzdba Member

    Keep in mind you're using systemobjects, so don't build software on it because it may change with every version/sp/hotfix ...
    check out sysindexkeys to start with.
    There is also the supported sp_helpindex [;)]
  3. SalsaFreak New Member

    Really, does it change that much?
    there are 4 columns in this table, so with statement I'm getting the doubles?
    so if colid and keyno have the same value, is this a double index?
    Thanks, Salsa
  4. satya Moderator

    They could be _WA_SYS indexes too that auto generated with statistics.
    Run SP_HELPINDEX on this table and see how many it returns.
  5. Adriaan New Member

    While sysindexkeys is probably relevant, by itself it's just a list of ID numbers that doesn't tell you much.
    Run sp_helpindex <table_name> to get all the indexes on a table, together with a list of the columns in the index. You then compare those lists yourself to see if a column is listed for more than one index.
    Note that if a column is listed after the first column in an index, and the same column also has a separate index to it, that the separate index is appropriate if you do searches on this column by itself, where the multi-column index would be less helpful.
  6. alzdba Member

    If it concerns indexes with only one colum, and the index column sorting (ascending/descending) is equal, then you have a duplicate idex.
    With composit indexes, column order in the index is important !
    Don't just drop indexes. Script the table with the indexes and investigate the statstics.
    Some indexes are created because of foreign-key-constraints ! (exact column order with the relevant PK is needed to support deletes of parent table rows if DRI is active)
  7. SalsaFreak New Member

  8. ndinakar Member

    If you are in 200, here's a script I stole from somewhere on the net:CREATE VIEW vw_index_list AS
    SELECT
    tbl.[name] AS TableName,idx.[name] AS IndexName,
    INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
    INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
    INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,
    INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,
    INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,
    INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,
    INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,
    INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,
    dpages,
    used,
    rowcntFROM
    SYSINDEXES idxINNER
    JOIN SYSOBJECTS tbl ON idx.[id] = tbl.[id]WHERE indid > 0
    AND INDEXPROPERTY( tbl.[id], idx.[name], 'IsStatistics') = 0GoSELECT l1.tablename, l1.indexname,
    l2.indexname AS overlappingIndex, l1.col1,
    l1.col2,
    l1.col3,
    l1.col4,
    l1.col5,
    l1.col6,
    l1.col7,
    l1.col8,
    l1.col9,
    l1.col10,
    l1.col11,
    l1.col12,
    l1.col13,
    l1.col14,
    l1.col15,
    l1.col16,
    l1.dpages,
    l1.used,
    l1.rowcntFROM
    vw_index_list l1 INNER JOIN vw_index_list l2 ON l1.tablename = l2.tablename
    AND l1.indexname <> l2.indexnameAND l1.col1 = l2.col1
    AND (l1.col2 IS NULL OR l2.col2 IS NULL OR l1.col2 = l2.col2)AND (l1.col3 IS NULL OR l2.col3 IS NULL OR l1.col3 = l2.col3)
    AND (l1.col4 IS NULL OR l2.col4 IS NULL OR l1.col4 = l2.col4)AND (l1.col5 IS NULL OR l2.col5 IS NULL OR l1.col5 = l2.col5)
    AND (l1.col6 IS NULL OR l2.col6 IS NULL OR l1.col6 = l2.col6)AND (l1.col7 IS NULL OR l2.col7 IS NULL OR l1.col7 = l2.col7)
    AND (l1.col8 IS NULL OR l2.col8 IS NULL OR l1.col8 = l2.col8)AND (l1.col9 IS NULL OR l2.col9 IS NULL OR l1.col9 = l2.col9)
    AND (l1.col10 IS NULL OR l2.col10 IS NULL OR l1.col10 = l2.col10)AND (l1.col11 IS NULL OR l2.col11 IS NULL OR l1.col11 = l2.col11)
    AND (l1.col12 IS NULL OR l2.col12 IS NULL OR l1.col12 = l2.col12)AND (l1.col13 IS NULL OR l2.col13 IS NULL OR l1.col13 = l2.col13)
    AND (l1.col14 IS NULL OR l2.col14 IS NULL OR l1.col14 = l2.col14)AND (l1.col15 IS NULL OR l2.col15 IS NULL OR l1.col15 = l2.col15)
    AND (l1.col16 IS NULL OR l2.col16 IS NULL OR l1.col16 = l2.col16)ORDER
    BYl1.tablename,
    l1.indexnamego
  9. SalsaFreak New Member

    Dear ndinakar,
    Thank you so much for the script! Honestly I'm not very experienced with this stuff, so i'm looking at the script and i'm having some questions (anuone who now thinks this subject should be moved to the Beginner Forum, just say so).
    Anyway what values should tbl.[name] and TableName have?
    Regards, Salsa
  10. satya Moderator

    Appreciate your feedback.
    Replace name with your tablename on the script to get more information.

Share This Page