Post reply

choosing best index columns

  • June 26, 2009 at 8:36 am

    #201844

    hi

    I have some tables with lots of fk and I'm trying to optimize the way indexes are on that tables, because in queries I see lots of scans instead of seeks.

    So I built some tables and try to test hoping I will find the best solution

    the tables look like this

    create table tabel7

    (

    id uniqueidentifier,

    fk1 uniqueidentifier,

    fk2 uniqueidentifier,

    fk3 uniqueidentifier

    )

    and I tried lots of possibilities in choosing the clustered and non-clustered index(es).

    for instance for this :

    create nonclustered index ix_ncl_tabel7 on tabel7(fk2)

    create clustered index ix_cl_tabel7 on tabel7(fk1,id,fk2)

    and the select (I execute them one by one) :

    select id,fk1,fk2,fk3

    from tabel7

    --where fk1 = 'B12B8831-BDC5-4D8C-A5E0-3DA8AC4A96E8'

    --where id = 'EF54E72F-0693-407D-A4C4-BDA2B4A44FA0'

    --where fk2 = 'A25C22F2-074F-449C-B17F-D0E6352098D6'

    where fk3 = '48904ABA-C505-4A02-BBD7-050F82F2321D'

    I have a index seek only for id and fk3(the first column in the enumeration for non cl index). The result was the same for other examples too, the seek was only for the first column.

    So....should I put a non-clustered index for every fk used?

    I was thinking to group more fk in the non-cl index, and the clustered one on the pk but it seems that I don't get what I want.

  • June 26, 2009 at 8:52 am

    #1016083

    shnex (6/26/2009)

    I have a index seek only for id and fk3(the first column in the enumeration for non cl index). The result was the same for other examples too, the seek was only for the first column.

    Yes it will be. SQL can only seek on an index if the columns that it's seeking are a left-based subset of the index keys.

    Think of a phone book, it's sorted by surname then firstname or initial, then address. How would you find all the people named Matthew in London? Can't do a seek, because you don't have a surname.

    I was thinking to group more fk in the non-cl index, and the clustered one on the pk but it seems that I don't get what I want.

    If you put all the pk column into one index, then only when the first column in there is used will SQL be able to seek

    For starters you can try cluster on the pk (it's not a guid, is it?) and a nc index for each foreign key. It's not necessarily the best config, but it's far from the worst.

    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

  • June 26, 2009 at 9:08 am

    #1016099

    Thanks man

    That's exactly the answer I needed.

    Unfortunately the pk is a uniqueidentifier...but I asked you before about this and we agree that is better to put on the uniqueidentifier than not to put one at all.

    Anyway you cleared the problem for me. 10q again

  • June 26, 2009 at 9:20 am

    #1016113

    shnex (6/26/2009)

    Unfortunately the pk is a uniqueidentifier...but I asked you before about this and we agree that is better to put on the uniqueidentifier than not to put one at all.

    Just keep a close eye on the fragmentation.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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