It’s one of those fierce religious wars that divides families and tears loved ones apart.
First, if two tables are related, should we tell SQL Server about it? If SQL Server knows that there’s a relationship, then it can make better decisions when building execution plans. The problem is that SQL Server has to be able to actually trust that relationship. Often, folks will disable keys and constraints temporarily in order to make a big operation go faster, like a bulk load, and then enable them again afterwards. However, if you don’t tell SQL Server to check the data afterwards, it simply doesn’t trust the constraints. I see it all the time in sp_Blitz warnings of “Foreign Keys or Check Constraints Not Trusted.” In those cases, the keys & constraints aren’t really helping your query plans.
Next, should we have SQL Server re-check our data? Once we’ve realized that our keys & constraints aren’t trusted, what do we do about it? We’ll go re-check the keys & constraints, which is an intensive operation that other users on the box will notice. Try it on a restored copy of production first, like in your development environment, because you’re likely going to discover that some of your data doesn’t match your defined relationships. We’ve got orphans, and they’re not as adorable as Annie.
Then what do we do about the bad data? You’ll need to clean that up, and it’s likely going to involve business discussions about data validity. How’d the junk get in there in the first place? How do we make sure it doesn’t happen again?
Finally, do your fixes in production. Script out the changes first in dev to delete the bad data and re-check the existing keys & constraints, then do it live. The changes are going to be logged operations, so the bigger they are, the more you have to look out for log shipping subscribers, replication subscribers, database mirrors, AG replicas, etc.
Is it all worth it? I’ve never seen a performance tuning case where I’ve said, “The one thing that will take you across the finish line is to have trusted foreign keys and constraints.” Usually, the simpler/easier/faster fix is to tune the queries and/or indexes. Plus, it’s an easier battle to fight with developers – who often don’t want to have any perceived overhead of enforcing foreign key relationships inside the database.
Even worse, sometimes the keys are the problem. If sp_Blitz reports serializable locking, your application may be doing updates and deletes, and relying on cascading updates & deletes to clean up child records. (You can learn more about this in Klaus Aschenbrenner’s key range locks demo.)
This is why I don’t pick a side on this particular religious war – neither side is right all the time, hahaha.
To learn more about the gotchas, read Erik’s 5-part adventure in setting up foreign keys in the Stack Overflow database, and why they didn’t really pay off: