February 9, 2016 at 11:31 pm
Comments posted to this topic are about the item Foreign Key Indexes and Binding
February 10, 2016 at 5:43 am
Nice article, and I always love to get scripts that help me understand whether or not I'm doing the right thing in my DBs. One question/point... In checking my DBs, I am getting "IsReferenceBoundToGoodIndex = Yes" for all of my FKs (yay!), but a significant number of my FKs are returning "IsParentIndexedForFK = No".
In looking deeper at the results, I believe this to be okay if you feel (for your situation), the "direction" of the JOINs between the Parent-Child tables will almost always be from the "child" side.
For example, I have a "reference" table REF_UnitType, that is the parent in a relationship with table Unit. If I only retrieve REF_UnitType info from the perspective of having a Unit
("[font="Courier New"]... FROM Unit u LEFT JOIN REF_UnitType rut ON u.unitTypeId = rut.id[/font]")
I don't think the lack of an index on Unit.unitTypeId causes a real problem. Can you confirm? Thx!
February 10, 2016 at 6:22 am
There's an added cost to have another NC index, and there's an added cost to having the FK do a scan for certain actions. Weigh the costs and do what's right for your database.
This script is more about knowing the costs and making intelligent decisions.
February 10, 2016 at 7:28 am
Hi Steve. Over recent months I've been adding indexes missing on foreign keys as well as searching for missing indexes where I see table scans occurring. Since there are quite a few, I started by limiting my search to tables with over 10 million records.
For your query I searched for cases where IsParentIndexedForFK is NO or IsReferenceBoundToGoodIndex is NO which came up with 260 records. I'm leaning towards filtering this down to larger tables for starters,but I'm not sure whether to apply the filter to the parent tables or the referenced tables.
QA requires me to apply new indexes very gradually in their environments and then push to prod weeks later, so I want to grab the biggest bang for the buck first.
WHERE (CASE WHEN EXISTS (SELECT * FROM ParentIndexGood
WHERE ParentIndexGood.constraint_object_id = k.object_id) THEN
'Yes' ELSE 'No' END='no' OR CASE
WHEN EXISTS (SELECT * FROM ReferencedBoundIndexGood WHERE ReferencedBoundIndexGood.constraint_object_id = k.object_id)
THEN 'Yes' ELSE 'No' END ='no')
February 10, 2016 at 7:43 am
There's several ways to do this.
- Look at what indexes (or heaps) are using the most space in memory using <a href="http://simplesqlserver.com/2016/01/04/query-the-buffer-pool/">Query the Buffer Pool</a>
- Look at sys.dm_db_index_usage_stats to see what indexes are doing a lot of scans, for which poorly indexed foreign keys could be an issue
- Look at your most expensive queries in <a href="https://msdn.microsoft.com/en-us/library/ms189741.aspx">sys.dm_exec_query_stats</a> ordered by logical or physical reads descending. Unindexed foreign keys that are used a lot will be moved up on this list.
- Do the largest tables first because they're the ones that will cause the most havoc when they do a scan.
The most important thing about optimizing your indexes is that you start optimizing your indexes. Too often people say they don't know the absolute best place to start (there isn't just one best place), then never start.
February 10, 2016 at 10:09 am
powell_todd (2/10/2016)
Nice article, and I always love to get scripts that help me understand whether or not I'm doing the right thing in my DBs. One question/point... In checking my DBs, I am getting "IsReferenceBoundToGoodIndex = Yes" for all of my FKs (yay!), but a significant number of my FKs are returning "IsParentIndexedForFK = No".In looking deeper at the results, I believe this to be okay if you feel (for your situation), the "direction" of the JOINs between the Parent-Child tables will almost always be from the "child" side.
For example, I have a "reference" table REF_UnitType, that is the parent in a relationship with table Unit. If I only retrieve REF_UnitType info from the perspective of having a Unit
("[font="Courier New"]... FROM Unit u LEFT JOIN REF_UnitType rut ON u.unitTypeId = rut.id[/font]")
I don't think the lack of an index on Unit.unitTypeId causes a real problem. Can you confirm? Thx!
If you try and delete a record from REF_unitType it will search for Unit records that may cause the delete to fail.
The caveat to indexing Unit.UnitTypeId is whether it has low selectivity. If you have millions of units but only a handful of UnitTypeId values then you are likely to get scanning activity rather than seeks.
February 10, 2016 at 10:31 am
Hello Steve. First off, I love all of the posting you've been doing lately. Keep it up!
This is a neat script, but I do have one gripe - the terminology that I am used to is to call the table that the foreign key is created on the Child table, and the table that it references is the Parent table. The output of your script has this reversed, which caused a few moments of confusion when I was investigating the results.
This means that the IsParentIndexedForFK column is actually presenting which Child FKs are indexed or not. This is a significant difference in terms of interpreting the results of this script and determining where to place indexes to avoid scans of the Parent tables.
Please set me straight if I am misinterpreting something here, and again, great job with all your recent contributions to the community!
February 10, 2016 at 10:41 am
Jeremy, I went with the wording in sys.foreign_keys, but did second guess how I was wording it a couple times, too. Glad to see it wasn't just me.
February 10, 2016 at 11:29 am
Ahhhh...I see what happened there. The parent_object_id from the sys.foreign_keys table is the owner of the foreign key object, which is correct. I believe the results of your query should be using terminology that references the foreign key relationship and not the object. It would make it much more clear. Of course, this is a simple fix for users that are confused by the mixed terminology (such as myself).
February 10, 2016 at 1:46 pm
Rule of thumb.
1. All FKs should have indexes on them (in minimum, FKs are covered by indexes)
2. FKs are the least expensive and should be the last sacrifice in terms of performance tuning efforts.
For DBAs who have managed tables with billions of records yet are referenced by multiple FKs, I believe my rules of thumb should sound familiar.
When a system is down to the point that it either die or live (depending on the FK status), everyone will choose the right choice.
Been there, done that.
February 10, 2016 at 1:55 pm
Relational Databases should be using the terminology of "Referencing" and "Referenced" tables in place of "Child" and "Parent" when speaking of foreign keys. Microsoft is pretty good about this in BOL although the older sys tables and procedures have not been updated to reflect this.
February 10, 2016 at 5:25 pm
Nice article, Steve. I'm going to have to take a look at your script tomorrow and compare it to mine that finds foreign keys without an index. I know mine doesn't consider the first column of an NCI only, which is a nice addition for covering deletes where they're needed.
February 10, 2016 at 5:40 pm
Thanks, Ed. You'll have to let me know how mine holds up.
February 11, 2016 at 2:15 pm
Well, the final paragraph horrfied me. A foreign Key is a CONSTRAINT. It is not a JOIN. JOINS and CONSTRAINTs are vastly different animals.
The use of Parent Table for referring table in someplces and for referenced table in others is bizarre, and until it is spotted it makes some of the article look like nonsense. It would be better to be consistent in use of this terminology. In fact it would be better still, as someone else said, to use the terms Referring Table and Referenced Table throughout.
There are many cases where I don't care whether the key is indexed in the referencing table because there is no situation in which the constraint needs to be checked in the direction from the referenced table to the refererring table. As long as the business logic implies that the referenced key is immutable and can't be deleted I have such a case. If I have a natural key that can't be deleted but is mutable I can create an immutable surrogate for foreign key constraints to refer to, so again I have such a case. These cases are pretty common, and in these cases there's no point in indexing the referring columns simply because they are used in a foreign key.
I like the "Bad Advice" section, but think you are being hopelessly optimistic if you really believe a foreign key constraint will stymie the one power user - he'll disable the constraint to do his delete or insert his nonsense, and if you are really unlucky he will then enable it at peak workload time.
The script looks useful.
Tom
February 11, 2016 at 2:46 pm
It could have been worded better with the parent and referenced tables. I second guessed how I've been saying it with how it's worded in sys.foreign_keys.
In terms of calling it a join instead of a constraint, it is a constraint which is being handled through joins between tables. The point I'm going for is that in terms of performance, look at this like you would other joins in your database. If you use the join often enough or in performance-critical places then you're going to want to make sure you're indexing it.
Like you, not all of my foreign keys are indexed on both sides. Before I wrote this script some of those instances were accidental and some were intentional. Now every foreign key that doesn't have an index on both sides is an intentional part of my indexing strategy.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply