An Un-indexed Foreign Key Gotcha

  • dwivedialok

    SSC Enthusiast

    Points: 124

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

  • Johan Bijnens

    SSC Guru

    Points: 134254

    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


    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[/url] :alien: but most of the time this is me :hehe:

  • richardd

    Hall of Fame

    Points: 3899

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715374

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

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • jswong05

    Hall of Fame

    Points: 3503

    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.

    Jason
    http://dbace.us
    😛

  • SanjayAttray

    SSChampion

    Points: 13157

    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.

  • sqlpro

    Hall of Fame

    Points: 3859

    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

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • sqlpro

    Hall of Fame

    Points: 3859

    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

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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

  • mcvilbar

    Say Hey Kid

    Points: 701

    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. 🙂

  • sqlpro

    Hall of Fame

    Points: 3859

    that makes sense. thanks for correcting that TheSQLGuru 🙂

  • denise.crabtree

    SSC Veteran

    Points: 213

    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

  • TheSQLGuru

    SSC Guru

    Points: 134017

    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 23 total)

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