Indexed Columns Duplicated

  • Hi

    I was wondering if anyone had any experience with columns that have been indexed more than once in a table.

    We currently have several tables where a column is indexed more than once,

    i.e.

    Index1 Column1, Column3

    Index2 Column2, Column3, Column4

    Index3 Column3, Column5

    Obviously the indexes have been added to tune different queries.

    I'd like to hear how people would deal with this situation, if we replaced Index1 and 3 with a new Index with Column1, Column3, Column5 would that affect read performance?

    Thanks.

    Stef.

  • Hello,

    It depends on how your data is being accessed. If all queries on the table go via Column1 and column3 or Column3 and Column5, then by combining the indexes you would have a performance hit. This is because the index will be physically bigger and therefore slower to read. How big the performance hit is depends on the size of the data-types involved, server activity, etc so it might well be insignificant.

    Having said that, you might consider replacing some of your existing non-clustered indexes with "Covering Indexes" i.e. ones that include enough columns so that the query does not need to access the underlying data pages. (See BOL "Creating Indexes with Included Columns")

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • No you should not alter your indexes for that reason. Generally the selectivity of the first column defines whether the optimiser will use the index. as all your start with a different column it's unlikely they would work better joined together. It's actually slightly more complicated than that because size as in width and number of pages and the actual queries all affect this too.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Stef (9/17/2008)


    I'd like to hear how people would deal with this situation, if we replaced Index1 and 3 with a new Index with Column1, Column3, Column5 would that affect read performance?

    Potentially, because if you have a query that filters only on Column3, Column5, it will no longer be able to do an index seek if you drop index 3.

    A query can only seek on an index if the where conditions are a left-based subset of the index keys

    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
  • Run the following query on a schedule and store in a table for later analysis:

    SELECT

    OBJECT_NAME( s.[object_id] ) AS TableName

    ,i.name AS IndexName

    ,s.user_updates

    FROM

    sys.dm_db_index_usage_stats s

    INNER JOIN

    sys.indexes i

    ON

    s.object_id = i.object_id

    AND i.index_id = s.index_id

    WHERE

    db_name(s.database_id) = db_name(db_id())

    AND objectproperty(i.object_id, 'IsIndexable') = 1

    AND LEFT(i.name, 3) NOT IN ('PK_', 'UC_')

    AND s.user_updates > 0 AND s.user_seeks = 0

    AND s.user_scans = 0 AND s.user_lookups = 0

    ORDER BY

    OBJECT_NAME( s.[object_id] )

    Alternatively, loosen the fliters of the above query somewhat to retrieve indexes with low usage in your SQL instance.

    After some time (say a few days or so) look at your results.

    Are some of your duplicate indexes included in that list? Then perhaps you should consider dropping those indexes.

    If not, then you will need to go through the more laborious process of tracking down which queries are using which index etc.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Thanks guys, didn't know about the first column being used for selectivity by the optimiser, that will be useful.

    I'll let you know how it goes, thanks for the input guys.

    Stef.

  • there a series of posts which I did here http://www.grumpyolddba.co.uk/sql2005/sql2005.htm

    I don't actually have anything which directly relates to your question, I just did a presentation on indexes but again didn't directly cover your question - I'll perhaps look into writing something up as it's a question I'd not considered.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • blogs.msdn.com/sqlprogrammability/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx

    blogs.ameriteach.com/chris-randall/2007/8/15/sql-server-2005-detecting-overlapping-indexes.html

    http://blogs.msdn. com/mssqlisv/archive/2007/06/29/detecting-overlapping-indexes-in-sql-server-2005.aspx

    http://blogs.msdn.com/mssqlisv/archive/2007/06/29/ detecting-overlapping-indexes-in-sql-server-2005.aspx

    Amazing what a very simple web search can do for you.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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