SQL Server Performance Forum – Threads Archive

Foreign Keys. (Newbie Question)

Whilst not a new comer to SQL Server I have uptill now gone about my business with cavalier non-interest in performance matters. Just making sure that fields in large tables that are commonly used in joins and where clauses have a reasonable set of indexes has seemed to be enough. However I’ve recently joined a project that has a well established SQL database but no real DBA input or SQL expertise. One thing I noticed that a whilst there are plenty of Primary Keys there are few other indexes. There are also lots of FKs about. I’m told by other team members that indexes are created for FKs by SQL automatically however I am unconvinced this is so. Can any one shed light on this? Also I’m told by the same source that SQL creates temporary indexes for commonly accessed fields hence adding indexes in various places is also unnecessary. I’m even more dubious about that.
Anthony Jones

---

Foreing key are created by designer according to mantein data integrity.
SQL create statistics in common accessed fields, works like index but this no means that there is no neccesary more indexs to implementate.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Luis, Thanks. So what do these statistics do? It seems to me that (from a developers point of view) quite a lot of foreign keys ought to also have an index. It is quite common for an application to already be in the context of a ‘master’ record then later want to retrieve ‘detail’ records from a table. The app already has what it needs from the ‘master’ record and knows it’s ID. It just needs to query the ‘detail’ table selecting on the FK pointing back to the master. Anthony.
Anthony Jones

---

What you said about master and detatail is Ok. Need a FK.
About statistics is a database properties (you can find it in each database properties). If is set to on, SQL generate some kind of index of most used columns call statistics.
Statistics begin with _W you can see it if run run "select * from sysindexs"
If application use, i.e., some query often, SQL made statistics to increase performace next time. To mantein those statistics up to day, is necesary to Update Statistics frequently.
To determinate if there is any Index (no statistics) is necesary to analize execution plan of each query.
You can find good information in our Forum. See Articles.
But this Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

The statistics are used by sql to help decide on an execution plan, and contain such information as key ranges and frequency distribution of the data. As Luis says the better informed sql server is about your data organisation, the more likely it is to choose an optimal query plan, but this may still be suboptimal if the foriegn keyed tables are not indexed optimally. In the case of your example, I think your app is selecting from a single child table, so the fact there happens to be a foreign key on that pointing back to the parent is irellevant. If the child table has a primary key which I assume it has, then by default sql server would have created a clustered index on that PK if one didnt already exist. This is often a wise design and if for whatever reason the clustered index is not on those fields (or not present at all), then you ought to review it. Recommend you run the commonly used sql statements through index tuning wizard and review its recommendations (hopefully theyre using stored procs, but id take a wild guess that without a dba they might just be shooting off ad-hoc statements. Use profiler to record a typical workload to analyse)
Also you should learn how to read execution plans if you cant already, once you can do this, you can often answer a lot of questions just by trying different scenarios and studying the changes to the plan

---

Chaps, Thanks that clears a lot up. There is a significant use of SPs in the db especially the more complex operations. However there is still a significant amount of moderately complex SQL generated and executed in ADODB.Openrecordset calls. I was of the understanding that whilst this is not as efficient as using SP (whose execution plan is persisted in the DB) that this approach can still benefit from some form of execution plan re-use. I suspect though there must be some overhead with either inital parsing and/or hashing to identify a matching cached execution plan which calling SP wouldn’t have incurred. Thanks again.
Anthony Jones

---

Tip: In order to gain the performance for the SP while using complex queries you can schedule a job to recompile the SPs plan & update statistics on the involved tables. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>