An Un-indexed Foreign Key Gotcha

  • Comments posted to this topic are about the item An Un-indexed Foreign Key Gotcha

  • Good Job !

    Nice article, well documented, good refs.

    As documented, the general attitude for FK-indexes sould be : always implement FK-indexes, unless one can prove a particular index hurts, and then only remove/disable that index !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Great catch!

    Paul Nielsen posted a script back in 2007 to auto-generate indexes for foreign keys:

    http://sqlblog.com/blogs/paul_nielsen/archive/2007/02/08/codegen-to-create-indexes-for-fks.aspx

    http://www.sqlserverbible.com/scripts.htm

  • Great job. I hadn't heard of this when I read the article, but it makes great sense.

  • Steve Jones - Editor (11/9/2009)


    Great job. I hadn't heard of this when I read the article, but it makes great sense.

    Yepper. I have had several clients that have benefited from indexing FKs and reducing deadlocks and/or increasing concurrency. Indexes aren't just about performance! 🙂

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

  • Nice article, very elaborative.

    I want to stress :

    by improving performance, query will run faster and reduce required lock time, hence reducing blocking and deadlock posiibilities.

    If you want to understand transaction processing, Thomas Kyte -- Expert Oracle .... is the book to study, a bible.

  • Nicely written Article, Alok.

    Its always a good to read any article which had combination of enough code and to-the-point explanation in it.

    SQL DBA.

  • am not sure adding indexes on FKs. i read somewhere if your child table contains lot of unique records then there is no point of creating index as index size is same as table size in that case seeks wont be any faster than scans , am i wrong ? thanks for the advise

  • sqlpro (11/9/2009)


    am not sure adding indexes on FKs. i read somewhere if your child table contains lot of unique records then there is no point of creating index as index size is same as table size in that case seeks wont be any faster than scans , am i wrong ? thanks for the advise

    Yes, you are wrong. Take it to the extreme: you have a billion row child table where every single parentid value is unique. Parentid is not indexed. You need to get the ONE child row for parentid 111222333. You have no choice but to do a table scan to check every single parentid value for equality to 111222333. That cannot be efficient. Index parentid and you will probably get somewhere between 8 and 10 total page reads (btree depth) depending on the width of the child PK and parentid. The table scan without the index could well be many tens or even hundreds of thousands of pages.

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

  • thanks for the reply. probably i misunderstood but following is the link i got my understanding from.its here http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=145

  • sqlpro (11/9/2009)


    thanks for the reply. probably i misunderstood but following is the link i got my understanding from.its here http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=145

    In general, nonclustered indexes (and the associated bookmark lookup to the base table) will be used only if the query is estimated to hit between 0 and perhaps as much as 2 percent of the total rows. This is a surprisingly low percentage to most people. Often, and certainly the case in my example where I explicitely stated there was exactly one FK child row per parent in a billion row table, FK children tables have VERY few rows per parent key and an index WILL be beneficial for lookups/joins.

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

  • I've been realizing that creating a non-clustered index on the foreign key is quite a very good idea. Nice article, very informative and interesting. 🙂

  • that makes sense. thanks for correcting that TheSQLGuru 🙂

  • I couldn't resist -- I wanted to query to find them. Didn't read all replies first. So here is my simple query to find a table without an index on a foreign key.

    If FK exists and column is in NO index -- then it will list out. Could be refined more. (exact order for multiple column FK match exact order for indexes )

    -----------------------------------------------------------------------

    -- Find a list of Foreign Keys that are not indexed

    --

    -- Created 11-09-2009 Denise Crabtree

    -----------------------------------------------------------------------

    -----------------------------------------------------------------------

    SELECT

    object_name(parent_object_id) [FK table]

    ,object_name(referenced_object_id) [References this Table]

    ,object_name(constraint_object_id) [Foreign Key Name]

    ,sys.columns.name [ChildTable Column]

    FROM sys.foreign_key_columns

    INNER JOIN sys.columns

    ON sys.foreign_key_columns.parent_object_id = sys.columns.object_id

    AND sys.foreign_key_columns.Parent_column_id = sys.columns.column_id

    LEFT OUTER JOIN sys.index_columns

    ON sys.foreign_key_columns.parent_object_id = sys.index_columns.object_id

    AND sys.foreign_key_columns.Parent_column_id = sys.index_columns.column_id

    WHERE

    sys.index_columns.object_id is null

  • See the earlier reply by richardd. It has a link to a script by Paul Nielson that not only identifies FKs missing indexes but will actually generate CREATE INDEX scripts for them (IIRC).

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

Viewing 15 posts - 1 through 15 (of 22 total)

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