Post reply

Creating index for all Foreign key columns!!! Please help

  • November 20, 2013 at 6:25 pm

    #286451

    Hello All,

    I have a requirement to create primary key for tables with no primary keys and to create index for all foreign key columns with out an index and this is for a large database. There are over 750 FK columns without index for the whole db.

    Please assist how I should approach this. Any help much appreciated ... I need help with the scripts too..

    MANY THANKS in advance...

    SQL Server - 2005 Enterprise

  • November 20, 2013 at 8:00 pm

    #1668002

    Generally it is a good idea to index your FK columns, but I suggest that you test before and after applying your changes. You need to be aware that performance may suffer in certain areas and it will increase the size of your database. You also need to make sure that you are maintaining these indexes on a go-forward basis. It is also a good to apply a few indexes at a time and make it an iterative deployment. Baseline - Apply - Capture Performance metrics - Compare with Baseline - Keep or ditch index - repeat with next index.

    I have linked to a blog post by Jason Strate on indexing FKs:

    http://www.jasonstrate.com/2010/06/index-those-foreign-keys/

    He includes some scripts for indexing FKs


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 20, 2013 at 8:17 pm

    #1668006

    Thank you!

  • November 20, 2013 at 10:58 pm

    #1668026

    To be honest, I wouldn't do a blanket index creation without a lot of analysis first. Could be many of those foreign keys don't need indexes on then, meaning this will create lots of useless indexes. Maybe that some will find an index on just the foreign key handy, but a wider index on the foreign key and other columns extremely useful, etc.

    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

  • November 21, 2013 at 2:19 am

    #1668047

    In general, having indexes on foreign keys is good.

    Once, I have created a bunch of supporting missing indexes for FKs and the performance gain came. The goal then was to improve a tool that deletes (archives) data. They are really good for delete operations.

    If lots of queries use joining by FKs, than indexes will for sure help you. You should not just create them. As Gail said you should analyse the reason for creating each well and see their usefulness to the system after. Sometimes for some cases extending an index with a couple of columns by adding them to the INCLUDE section can bring you a lot of benefit, or extending the key list as well. That's why you should not just create them at first hand.

    The standard report "Index usage statistics" is the best source to see the indexes usage.

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

  • November 21, 2013 at 4:10 am

    #1668067

    Thanks Gail and Igor !

Viewing 6 posts - 1 through 5 (of 5 total)

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