April 21, 2016 at 5:58 am
Hi Experts,
We have more than 100 Foreign Keys with no supporting index. Do we need to add index on all these Foreign Keys ?
Can someone help on fixing this ?
April 21, 2016 at 6:12 am
VastSQL (4/21/2016)
Hi Experts,We have more than 100 Foreign Keys with no supporting index. Do we need to add index on all these Foreign Keys ?
Can someone help on fixing this ?
You may like to read this article, which discusses this very issue.
What sort of 'help' do you have in mind?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 21, 2016 at 6:19 am
Thanks Phil.
Do we need to consider these column which are more read intensive?
I had the same doubt as article mentioned, that instead of creating index just for FK column ,why no not add multiple columns which are used in other frequently executed queries?
April 21, 2016 at 6:29 am
VastSQL (4/21/2016)
Thanks Phil.Do we need to consider these column which are more read intensive?
I had the same doubt as article mentioned, that instead of creating index just for FK column ,why no not add multiple columns which are used in other frequently executed queries?
As you have so many, I guess one idea is that you start by creating the indexes and then fine-tune them, adding further columns where necessary, once they are in place.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 24, 2016 at 1:57 am
Phil Parkin (4/21/2016)
VastSQL (4/21/2016)
Thanks Phil.Do we need to consider these column which are more read intensive?
I had the same doubt as article mentioned, that instead of creating index just for FK column ,why no not add multiple columns which are used in other frequently executed queries?
As you have so many, I guess one idea is that you start by creating the indexes and then fine-tune them, adding further columns where necessary, once they are in place.
Thanks Phil.
April 24, 2016 at 1:17 pm
I would take a look at this book: Performance Tuning With SQL Server Dynamic Management Views. It will help you put together scripts you can use to determine which FK columns are getting hit the most often and the cost. This will help you prioritize which FKs to index first.
I usually add INCLUDE columns to my indexes on FK columns so as to avoid costly lookups against the clustered index. DMV scripts can help you determine which columns should be included.
-- Itzik Ben-Gan 2001
April 25, 2016 at 1:13 am
Alan.B (4/24/2016)
I would take a look at this book: Performance Tuning With SQL Server Dynamic Management Views. It will help you put together scripts you can use to determine which FK columns are getting hit the most often and the cost. This will help you prioritize which FKs to index first.I usually add INCLUDE columns to my indexes on FK columns so as to avoid costly lookups against the clustered index. DMV scripts can help you determine which columns should be included.
Thanks Alan.
Hope that will help me a lot.
April 25, 2016 at 4:14 am
VastSQL (4/21/2016)
Hi Experts,We have more than 100 Foreign Keys with no supporting index. Do we need to add index on all these Foreign Keys ?
Can someone help on fixing this ?
Microsoft RAP report brings Foreign keys without indexes up as a medium risk to the SQL instance.
I disagree most strongly with the Microsoft Rap Report as you should only index columns that have queries against them.
If it does not come up as a huge benefit in the missing indexes DMV, then it is a waste of space and time.
Case in point, you could have a Gender column in your customer table which is a foreign key, but you never query on that column, and an index will bring back half the table, so may end up being completely unused.
So creating an index will increase lock footprint and size of DB, index rebuild jobs take longer, copying your backups will take longer for absolutely no reason.
April 25, 2016 at 10:07 am
My thought is same as Phil's. If its a new database start by creating indexes for all FKs. If its a proper normalized design, most of these FKs need to be used in joins some point of time.
April 25, 2016 at 1:14 pm
Lack of index on FK column has bitten me when the parent table was one where the data was occasionally deleted. Deletion of rows in the parent table necessitated scans of all referencing child tables to ensure there were no FK references. This could be extremely slow. Adding an index on the FK column in the referencing child table addressed this even if otherwise that index appeared useless.
April 25, 2016 at 1:44 pm
Mike Good (4/25/2016)
Lack of index on FK column has bitten me when the parent table was one where the data was occasionally deleted. Deletion of rows in the parent table necessitated scans of all referencing child tables to ensure there were no FK references. This could be extremely slow. Adding an index on the FK column in the referencing child table addressed this even if otherwise that index appeared useless.
Does that mean it is good practice to create indexes on systems that dont ever do deletes, or would you recommend based on your workload.
April 25, 2016 at 1:51 pm
If no parent deletes (and no updates to parent keys) I would not add an index "because of" the FK. If you need the index due to workload then add it, just like you would normally.
April 26, 2016 at 1:00 am
Thanks MadAdmin and Mike.
Will check with work loads and then will proceed with index creation.
Is there anyway I can get those details?
April 26, 2016 at 8:51 am
Workload-related index recommendations can be obtained from sys.dm_db_missing_index_details. Many good articles have been posted on how to do this.
This would also recommend FK indexes due to parent delete/update, but only if these happen often enough. In my case this activity was quite rare, so I did not get the recommendation.
My shop disallows direct/dynamic SQL, so for me it's simple to discern whether or not anything updates keys or deletes from my parent tables--I just review the related stored procedures. So one way is to just review the pertinent code. Another way (I haven't used this) might be to use SQL's audit functionality to track delete/update activity on parent tables. Again there are several good articles on this.
You can't go terribly wrong either way. Having extra indexes that you don't need is not awful. And if you are missing one that you really need, I expect you'll eventually find out about it and add it.
April 26, 2016 at 3:43 pm
Based on me, When we have FK constraint on Fact tables then mostly we use in join to get the DimTable data in any report or DW Cubes(in DW world).
So always the Index help on FK columns help for better performance.
Also, We don't need to create the index on FK blindly, but if you see the FK is used much in join's then we can have noncluster.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply