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 123»»»

An Un-indexed Foreign Key Gotcha Expand / Collapse
Author
Message
Posted Saturday, November 7, 2009 12:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 5, 2013 3:42 AM
Points: 10, Visits: 139
Comments posted to this topic are about the item An Un-indexed Foreign Key Gotcha
Post #815463
Posted Monday, November 9, 2009 12:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 AM
Points: 6,733, Visits: 8,485
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- 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 but most of the time this is me
Post #815660
Posted Monday, November 9, 2009 6:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:16 AM
Points: 2,331, Visits: 566
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



Post #815777
Posted Monday, November 9, 2009 8:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
Great job. I hadn't heard of this when I read the article, but it makes great sense.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #815830
Posted Monday, November 9, 2009 9:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
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 at GMail
Post #815861
Posted Monday, November 9, 2009 10:17 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, October 20, 2014 1:11 PM
Points: 27, Visits: 475
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
Post #815940
Posted Monday, November 9, 2009 10:48 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #815962
Posted Monday, November 9, 2009 1:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:55 PM
Points: 360, Visits: 103
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


Post #816079
Posted Monday, November 9, 2009 2:45 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 2:18 PM
Points: 4,406, Visits: 6,268
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 at GMail
Post #816151
Posted Monday, November 9, 2009 3:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, October 9, 2014 9:55 PM
Points: 360, Visits: 103
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




Post #816195
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse