September 2, 2008 at 12:43 am
Should we create indexes on all foreing keys.
September 2, 2008 at 2:04 am
Generally, yes, but you should test your queries to make sure they use the indexes you've created.
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
September 2, 2008 at 12:59 pm
I think the answer depends a lot on the nature of your queries. I have found tables with 6 or more indexes, each on a single foreign key column. When I check the query plans for the representative queries I find that none of the indexes are used. So, the indexes provide protection for referential integrity but based on the query plans they do not contribute to performance. In fact they take up space and processor to maintain.
If you use DTA (Database Tuning Advisor) and Profiler you can create indexes that will actually contribute to the overall performance. Profiler lets you capture the queries and see which ones have performance issues (long duration, large number reads/writes, etc). You can then build these queries into a work load file and play them into DTA which will give you a starting point for the correct indexes. You may be surprised to find that a smaller number of indexes with multiple included columns will work much better than the multiple single column indexes.
The results also change depending SQL version (8.0, 9.0, 10.0) since MS seems to modify/improve index utilization each release.
Good Luck.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply