October 2, 2013 at 4:09 am
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
October 2, 2013 at 6:13 am
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
October 2, 2013 at 6:22 am
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
October 4, 2013 at 8:34 am
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
October 7, 2013 at 7:30 am
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
October 7, 2013 at 7:35 am
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
October 7, 2013 at 7:43 am
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
October 7, 2013 at 9:04 am
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
October 7, 2013 at 9:25 am
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply