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

Creating Index on all FK (more than 15 per table) Expand / Collapse
Author
Message
Posted Wednesday, October 2, 2013 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 10:43 PM
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
Post #1500714
Posted Wednesday, October 2, 2013 6:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 42,481, Visits: 35,552
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 2008, MVP
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

Post #1500742
Posted Wednesday, October 2, 2013 6:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 5:11 AM
Points: 94, Visits: 328
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
Post #1500746
Posted Friday, October 4, 2013 8:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 984, Visits: 1,324
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
Post #1501621
Posted Monday, October 7, 2013 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 10:43 PM
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
Post #1502120
Posted Monday, October 7, 2013 7:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 8:52 AM
Points: 984, Visits: 1,324
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
Post #1502127
Posted Monday, October 7, 2013 7:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 42,481, Visits: 35,552
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 2008, MVP
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

Post #1502135
Posted Monday, October 7, 2013 9:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 10:43 PM
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
Post #1502195
Posted Monday, October 7, 2013 9:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 42,481, Visits: 35,552
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 2008, MVP
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

Post #1502219
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse