SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating Index on all FK (more than 15 per table)


Creating Index on all FK (more than 15 per table)

Author
Message
aleksandar.ivanovski
aleksandar.ivanovski
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 55
Hi,

I have 2-3 huge tables (more than 20M rows, and more than 50 columns each).
About 20 of these columns are FK to other tables. (used both for joins and referential integrity)


I have been reading a lot regarding performance that all FK should have index on them.
Besides these indexes I need (and use) about 10 indexes per table.

What is your suggestion/experience should I create indexes on all FKs or I can omit some of them (for those that I can guarantee integrity from within the application)?

Thanks
Aleksandar
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223017 Visits: 46294
Indexes on a foreign key have nothing to do with referential integrity, the foreign key ensures that. If you're suggesting dropping the foreign keys, don't.

As for whether or not you should index those foreign keys, tet and see. If they help and don't measurably impact modifications, keep them. If they don't, then don't. Very few 'set in stone' rules for indexes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


SrcName
SrcName
Old Hand
Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)Old Hand (320 reputation)

Group: General Forum Members
Points: 320 Visits: 394
On the start you can leave all the index.
analyse work on database, read statistic and you will see usage of indexes.
after that you can decide about existence of indexes
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 1400
I have found the best way to determine if a very large table should have additional indexes is to see what is running against that table. If there is a SP that runs frequently and impacts overall performance to the end user then by all means evaluate that procedure to find what index or indexes are needed.

You can use the Database Tuning Advisory but you may find it may impact the overall performance of the machine using it, or at least from my experience. I would most likely evaluate the procedure by getting an estimated execution plan to try to clean up the scanning.

Good luck.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
aleksandar.ivanovski
aleksandar.ivanovski
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 55
Thank you all for the answers.

I did created indexes on all FK fileds and run the following query:

Select (s-thing from 5-6 tables)
from Table_Containing_FK A
Left outer join B
on A.id1 = B.id
Left outer join C
on A.id2 = C.id
Left outer join D
on A.id3 = D.id
Left outer join E
on A.id4 = E.id
where sthing

DTA gives me the following instructions:

CREATE NONCLUSTERED INDEX [_dta_index_XYZ] ON [dbo].[Table_Containing_FK]
(
[Status] ASC,
[A1] ASC,
[A2] ASC,
[A3] ASC,
[A4] ASC,
)
INCLUDE ([Code])

Does this makes sense to you?

Thank you,
Aleksandar
Kurt W. Zimmerman
Kurt W. Zimmerman
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2608 Visits: 1400
aleksandar.ivanovski (10/7/2013)
Thank you all for the answers.

I did created indexes on all FK fileds and run the following query:

Select (s-thing from 5-6 tables)
from Table_Containing_FK A
Left outer join B
on A.id1 = B.id
Left outer join C
on A.id2 = C.id
Left outer join D
on A.id3 = D.id
Left outer join E
on A.id4 = E.id
where sthing

DTA gives me the following instructions:

CREATE NONCLUSTERED INDEX [_dta_index_XYZ] ON [dbo].[Table_Containing_FK]
(
[Status] ASC,
[A1] ASC,
[A2] ASC,
[A3] ASC,
[A4] ASC,
)
INCLUDE ([Code])

Does this makes sense to you?

Thank you,
Aleksandar


Sure it does. The reason is the index puts all of the keys together in a way it is best utilized for the search.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223017 Visits: 46294
Maybe. Test it and see if that index helps with the performance of the application. If it does, implement it. If it doesn't, don't implement it. Either way, it's not the 'one index per foreign key' that you were asking about.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


aleksandar.ivanovski
aleksandar.ivanovski
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 55
I agree it is not one index per FK but I was thinking the following:
Whenever somebody updates/deletes table that holds the primary key SQL server needs to check if integrity violation happened (in this case search my table to check all values for the key)

If there is no single column index per key it will last long right?
My question was, if there is no way to insert/update/delete rows in some of these columns and should I leave these without index on them?
But I guess try and see will be the best case scenario.

Thanks
Aleksandar
GilaMonster
GilaMonster
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223017 Visits: 46294
aleksandar.ivanovski (10/7/2013)
If there is no single column index per key it will last long right?


It could, yes.

My question was, if there is no way to insert/update/delete rows in some of these columns and should I leave these without index on them?


Maybe. Test and see. As I said earlier, there are very few rules that are set in stone for indexes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search