November 7, 2009 at 12:14 pm
Comments posted to this topic are about the item An Un-indexed Foreign Key Gotcha
November 9, 2009 at 12:57 am
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
November 9, 2009 at 6:35 am
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
November 9, 2009 at 8:41 am
Great job. I hadn't heard of this when I read the article, but it makes great sense.
November 9, 2009 at 9:08 am
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 on googles mail service
November 9, 2009 at 10:17 am
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
😛
November 9, 2009 at 10:48 am
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.
November 9, 2009 at 1:07 pm
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
November 9, 2009 at 2:45 pm
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 on googles mail service
November 9, 2009 at 3:35 pm
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
November 9, 2009 at 3:52 pm
sqlpro (11/9/2009)
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
In general, nonclustered indexes (and the associated bookmark lookup to the base table) will be used only if the query is estimated to hit between 0 and perhaps as much as 2 percent of the total rows. This is a surprisingly low percentage to most people. Often, and certainly the case in my example where I explicitely stated there was exactly one FK child row per parent in a billion row table, FK children tables have VERY few rows per parent key and an index WILL be beneficial for lookups/joins.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 9, 2009 at 3:56 pm
I've been realizing that creating a non-clustered index on the foreign key is quite a very good idea. Nice article, very informative and interesting. 🙂
November 9, 2009 at 3:57 pm
that makes sense. thanks for correcting that TheSQLGuru 🙂
November 9, 2009 at 4:05 pm
I couldn't resist -- I wanted to query to find them. Didn't read all replies first. So here is my simple query to find a table without an index on a foreign key.
If FK exists and column is in NO index -- then it will list out. Could be refined more. (exact order for multiple column FK match exact order for indexes )
-----------------------------------------------------------------------
-- Find a list of Foreign Keys that are not indexed
--
-- Created 11-09-2009 Denise Crabtree
-----------------------------------------------------------------------
-----------------------------------------------------------------------
SELECT
object_name(parent_object_id) [FK table]
,object_name(referenced_object_id) [References this Table]
,object_name(constraint_object_id) [Foreign Key Name]
,sys.columns.name [ChildTable Column]
FROM sys.foreign_key_columns
INNER JOIN sys.columns
ON sys.foreign_key_columns.parent_object_id = sys.columns.object_id
AND sys.foreign_key_columns.Parent_column_id = sys.columns.column_id
LEFT OUTER JOIN sys.index_columns
ON sys.foreign_key_columns.parent_object_id = sys.index_columns.object_id
AND sys.foreign_key_columns.Parent_column_id = sys.index_columns.column_id
WHERE
sys.index_columns.object_id is null
November 9, 2009 at 4:18 pm
See the earlier reply by richardd. It has a link to a script by Paul Nielson that not only identifies FKs missing indexes but will actually generate CREATE INDEX scripts for them (IIRC).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply