SQL Server Performance

improving indexes - removing duplicate columns etc

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Oct 9, 2011.

  1. Trev256b Member

    1) i have noticed on one table that there are several columns that are repeated in a number of indexes. I think this is bad pracitce. For example:
    INDEXA (ColBit, ColDate) - NonClustered
    INDEXB (ColNum, ColUniNum, ColIdentity) - NonClustered
    INDEXC (ColIdentity, ColUniNum) - NonClustered
    INDEXD (ColIdentity, ColUniNum, ColNum) - Clustered

    2) Will the above index setup reduce performance by increasing update stats time both automatic and scheduled; and increasing fragmentation; and increasing update/insert time???
  2. FrankKalis Moderator

    Hard to tell, but there is a handy dmv out there, that might be a starting point: sys.dm_db_index_usage_stats
    Or this dmf; dm_db_index_operational_stats
  3. preethi Member

    I suspect that IndexC may be unnecessary. But you need to verify with the DMVs Frank mentioned..
    Too many indexes will definitely increase the insert/update/delete time.
    I prefer scheduled update stats only. There is another thread on this. Please refer: http://www.sql-server-performance.com/forum/threads/does-rebuilding-indexes-update-statistics.32898/
    Not sure about the data types and updates you perform on the base tables. but all updates (Including insert & delete) to the columns in indexes can increase the fragmentation.
  4. satya Moderator

    True, you cannot say an index is bad or good until it is used, so going by wtih Frank and Preethi response here you should take your time to analyze these objects on your environment.
  5. mmarovic Active Member

    Is there a clustered index on the table? Consider making colIdentity clustered primary key. In most case that is the best choice. If you do so, you will not need last two indexes at all. Also on the second index you can remove colIdentity from the index. For the first index, without knowing queries and usage it is not possible to even guess.

    Anyway, I suspect that the person who created indexes was not very experienced/skilled.

Share This Page