Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

FKs with indexes... Expand / Collapse
Author
Message
Posted Tuesday, December 18, 2012 10:54 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Hi,

I've read several articles stating that FKs should have indexes on both tables (primary and referenced) and it has quite logic since when deleting/updating on the referenced table the primary one will be searched for those records...
I also read some stating that it's best to have few wider indexes rather than lot narrow one's due to index maintenance, size on disk, what happens when a record is updated/deleted, ....
But it seems like these both statements are a bit contradictory since creating indexes on FKs will create lots of narrow indexes... for example I have a table that will have 30 "extra" narrow indexes, and since they are FKs no wide index will cover the narrow ones.
Is it always advisable to create indexes on FKs or, since in this case, the records aren't that much deleted and the keys never changed, can we "skip" the FKs indexes on the primary tables?

Thanks,
Pedro




If you need to work better, try working less...
Post #1397931
Posted Tuesday, December 18, 2012 11:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
It depends.

There are always cases where "the rules" need to be broken.

In a case like that, with 30+ FKs needed, that gets into the realms of both (a) you probably won't benefit much from 30+ indexes, and (b) what is going on in that database?!?

But really, and seriously, you'd have to look at query patterns for the table and the related tables. Some might benefit from indexes. Maybe even all of them. But it will really depend on the queries that are in use.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1397953
Posted Wednesday, December 19, 2012 1:34 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
It's a products table... it has VAT, warranty, supplier, units, .... and many other references.
The referenced tables don't have many records (top on has 20) but the products can have 16000 or many many more...
And since the referenced tables can't be updated (code column is read only after insert) it can only be deleted what doesn't happen that much...
Referenced tables with few records usually means that it isn't inserted/updated/deleted that regularly (in our case the tables are configured usually once and left alone) so narrow indexes on the primary table for these ones can be a rule to break... I guest..
We also have some other cases that probably require the primary table index...
In cases were the FK columns can't be updated cause they are read-only and the data isn't deleted (just a bit column with record state) the indexes don't make sense right?

Thanks,
Pedro




If you need to work better, try working less...
Post #1398229
Posted Wednesday, December 19, 2012 6:10 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
I'd have to run some tests to say definitively whether indexes would help or not, but I can say I've seen similar systems and indexes weren't needed there.

So, probably you're fine without.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1398346
Posted Wednesday, December 19, 2012 8:34 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Thanks,

Also if the indexes are necessary they'll probably show up on the missing index DMV query....

Pedro




If you need to work better, try working less...
Post #1398455
Posted Wednesday, December 19, 2012 8:57 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
That's definitely possible.

Or they'll show up in index plans when you're reviewing performance issues.

Until it turns into something that needs fixing ...


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1398475
Posted Wednesday, December 19, 2012 9:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, June 12, 2014 9:30 AM
Points: 513, Visits: 1,129
Thanks,
Better wait and see what shows on the sys.dm_db_missing_index_groups and/or the sys.dm_exec_query_plan

Pedro




If you need to work better, try working less...
Post #1398479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse