Indexing Foreign Keys – should SQL Server do that automatically?

I've been doing another performance tuning job today and it highlighted to me once again that problem that a lack of indexes on foreign key columns can bring.

By adding indexes on the foreign keys on three tables, we saw a reduction of 87% in total I/O load on the server. There are other aspects of the system that I'm now working on but it really struck me that having SQL Server do this by default would avoid a lot of apparent performance problems. It would have to be one of the most common indexing issues that I see in my work.

What do you think? Should SQL Server simply do this by default when you declare a foreign key reference?

20 thoughts on “Indexing Foreign Keys – should SQL Server do that automatically?”

  1. As long as it allowed said index to be dropped later if a DBA so decided that it wasn't necessary, then certainly.  My only problem with just a simple index like this, is that it can force Key/Bookmark Lookups from the non-clustered index to clustered index, which on improperly tuned systems can result in deadlocks.  A correct index that covers the query should be built by the DBA to resolve this problem, but if the proper indexes are in place then it is likely that the simple index built on the FK may never get used, but requires maintenance for every insert/update/delete.
    I just deleted 40+ indexes on FK columns from a vendor database because of exactly what I describe above, so this post is relative to what I have experienced recently.

  2. I'm not sure I want the index created automatically because I may be using the Foreign Key column along with another column in the where clause that I would want to be part of the index.  Granted it would be easier, but you may end up with extra indexes that are never removed.

  3. My take is – no, it shouldn't. I believe in 6.5 and ealier it did (someone please correct me if I'm wrong). By creating it automatically it blurs the distinction between a foreign key (an integrity constraint) and an index (performance). This is the "dumbing down" of a database that happened a when 7.0 was marketed ("It tunes itself, you don't need a DBA" – I about dropped dead when I first heard that). I want the person creating the foreign key to understand what a foreign key is and what an index is. In the scenario you just described (of which I too have run into again and again and again), it can be either a 3rd party product trying to be "platform agnostic" or just ignorance in the initial design. I tire of these types of issues as well, but I think the solution is greater education, not automation (one of my personal mantras "Automated does not mean automatic").
    There can be reasons not to place an index on a foreign key. I've seen people use lookup tables where the cardinality of the column is such that an index over a table scan isn't chosen by the query optimizer. That of course implies other design issues, but I'd rather have people think and understand rather than have the decisions made for them.
    The fact that there are so many basic mistakes like this made out there in the field just shows how poor a job either MS has taken on education and stressing the importance of these typical DBA tasks, or the reluctance of the public to take SQL Server seriously as a data platform.
    Still, a good issue to ponder. Just my two cents.

  4. Hi Greg,
    Almost all my FK constraints have covering indexes. So I'd suggest that automated creation of indexes for FKs should be optional, a configuration parameter. Also we should be able to specify an existing index to be used. BTW, than (the ability to specify an existing index) could be handy when creating unique constraints too.

  5. The thing I'm regularly seeing is the problem where there is no index at all on the foreign keys, including other columns covered or not.
    What concerns me is that it's such a common problem and I'm wondering whether there should be some automated way around it, given the level of problems it also tends to cause. The situations where having such an index causes a problem are much less frequent.
    I suppose I'm weighing up "the greater good" in terms of the product getting an undeserved bad rap :-)

  6. Just a though, perhaps warning may be generated for those who do not realize that fkeys may need indexes. Practices, or the lack of, will always keep us busy.
    My .02. Cheers

  7. My vote is "yes". PK constraints automatically create indexes. Why FK constraints cannot?

  8. The index on foreign key issues usually occurs when one deletes from the primary key table. Not all applications do so. So really, one just needs the intelligence to look at an execution plan, but that item is the true source of problems

  9. Hi,
    I can't say I agree that it's mostly used for deletes. Most systems I work on (OLTP) do such a small percentage of writes compared to reads that it's not the main problem. Clearly if you need to support any form of cascading deletes then it's really important.
    However, the biggest need I see is where people are reading not writing eg: find me all the room types for a particular establishment. As others have pointed out, there's usually more to it than that. For example, you might say "find me all the orders for a given customer" but you are much more likely to have further criteria on that (eg: recent orders) and then heading into covering index territory.

  10. The problem to me Greg is the variety of ways in which a foreign key is created…  Automating a creation would be problematic…  second…  what if a foreign key is dropped…  do you end up with an orphaned index?  
    Why not look at this from the other angle…  why not look at the loads on the server…   can you not have system generated test triggers on commonly called processes which return response times…  then graph them..   so that the loads can be viewed and analysed…  then if an unindexed foreign key is found inside the process… can it not be detected… and the appropriate scripts be generated by some kind of bolt on tool?  
    In this way you do not clog the schema… logs etc with a bunch of junk…   I agree it should be easier for novice users (such as me) to get across this basic solution…  which after all you see as a waste of senior people's time…  its a very good idea… saves costs…  improves productivity…  I think the entry point for your idea is wrong.  The tool should be able to identify the basics..  orphan keys etc…   I know I am extremely untidy when I develop, I leave junk everywhere.  My sandpit looks like a litterbox…

  11. May be there should be easier way but it should not be default. For example, may be there should be WITH INDEX clause for foreign key creation. However remeber, creating index is expensive (initial CPU as well as space as well as inserts etc). If your table has 10 million records and if it creates indexes by default you can get screwed.

  12. ERWin creates indexes on forgeign keys by default. Maybe more databases should be designed in an application like this before they are deployed :-)

  13. I think it would be great if SQL server provided the option to create the index when the FK is created. This way if the index is also required, the designer can check this option.

  14. I would like to see it as a property of hte foreign key.  At creation time, SqlServer would ask if this FK uses an index.  
    The index would have a default name suggested, similar to the constraint name and the DBA would be able to change it at creation time or rename it later.  
    The Index and the FK would be marked as "related" each other so SqlServer would include that index on its execution plans quicker than usual, the DBA would beable to specify if that inclusion is
    (A) Always (quicker plan)
    (B) Depending on Context, (in this case SqlServer would nalyze this index as usual).
    SqlServer would ask the DBA at drop time if the other component should be dropped too (when dropping the FK you get prompted if you want to drop the companion IX or not).
    All these characteristics would be able to be configured as default for any FK.  The DBA would be able to adopt the default for a FK or specify properties ad hoc for one particular FK.
    When FK and IX get marked as "related" by using the default rules, SqlServer would remember that the association was created as default.  Then, later, if the DBA changes the Default, SqlServer would be able to replicate the change for each default relationship.
    The DBA would be able to query metadata and retrieve these associations and all its properties.
    My 2 cents.

  15. The reason why the production of indexes over FK is not by default is that many of these type of index can be included in other indexes causing redundancy of index wich is quite poorless in terme of performances.
    Take a look over this case :
    Having an order table nammed T_ORDERS with ORD_ID the PK.
    Having a product table nammed T_PRODUCTS with PRD_ID the PK.
    Having an association table namme T_J_PRODUCT_ITEMS with ORD_ID and PRD_ID the combined PK and ORD_ID a FK for orders and PRD_ID the FK for products.
    For this 3rd table, applying the default indexing technics will results 3 indexes :
    1) PK : ORD_ID, PRD_ID
    2) FK : ORD_ID
    3) FK : PRD_ID
    You may see that the second index is inbclude in the firts one resulting of redundancy index…
    As you must known, every index has a cost in terms of insertind, deleteing and updating. So it is not appropriate to create the 2nd index.
    Such tools like AMC Designer (Power AMC for the french – it's in fact an original french product) can dreate FK indexes and detect included indexes…

  16. I would say this should be implemented and "on" by default.  The reason being that for noobs, most of them will not index by default and therefore suffer performance issues.  I too have seen this many, many times in my career.
    If you are a DBA and implementing a covering index, then how much work is it to drop an existing FK index first?
    In this case, the benefit to many outweighs the inconvenience to the pro in my opinion.

  17. Hey,
    Anybody can help me in dropping Index, Which is a foreignkey in other table, i want to drop this index with out dropping the foreign key…
    Thanks in advnce

  18. I get the feeling that a lot of these comments are based on the assumption that a table may already exist that may have existing indexes, and that an automatically generated index on the foreign key may cause a redundant index to be created.
    I think people forget that the outcome may be different if it was turned around and the table was just being created.
    If the table was new, with no existing indexes, and if an index is automatically created when the foreign key is created, then the table would automatically provide some base-line performance that would benefit the majority of people, remembering, of course, that in development, there are less and less people that have knowledge of the database and how it should be optimised.
    Of course, you would have to allow people to customise the automatically created indexes, but not everyone is an expert and would know when that should occur. Sure, you may want to add extra columns to an index, and extra covering columns, but that is entirely up to you based on analysing the query plan.
    Also, there could be smarts included to prevent the creation of foreign key indexes if there are already existing indexes covering the foreign key.
    But I believe what Greg is saying is spot on – by default there should be automatic adding of foreign key indexes, done for the greater good.
    BTW, I have an improvement over Paul Nielsons's foreign key index generation that doesn't require the creation of a function. It may be found here:

Leave a Reply

Your email address will not be published. Required fields are marked *