Foreign Keys or Check Constraints Not Trusted
Blitz Result: Foreign Keys or Check Constraints Not Trusted
If you need to load a lot of data quickly, you can disable keys and constraints in order to improve performance. After the data load finishes, enable them again, and SQL Server will check them behind the scenes. This technique works best in large data warehouse environments where entire dimension tables might be reloaded from scratch every night. Disabling constraints is usually safer and easier than dropping and recreating them.
As long as you remember to enable them correctly again – and most people don’t.
This part of our SQL Server sp_Blitz® script checks sys.foreign_keys looking for indexes with is_not_trusted = 1.
It turns out this can have a huge performance impact on queries, too, because SQL Server won’t use untrusted constraints to build better execution plans.
To Fix the Problem
Use the steps below to find the keys and constraints that need help, and plan a change to fix them.
Return to sp_Blitz® or Ask Us Questions
Query A List of Untrusted Foreign Keys and Constraints
First, get the list of affected foreign keys with this T-SQL script. It must be run inside the affected database(s):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname from sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0; GO SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname from sys.check_constraints i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0 AND i.is_disabled = 0; GO |
(Thanks to Aaron Lowe @Vendoran for improving this script!)
You have to tell SQL Server to not just enable the constraint, but to recheck all of the data that’s been loaded.
|
1 2 |
ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName; GO |
The word CHECK appears twice on purpose – this tells SQL Server that it needs to check the data, not just enable the constraint.
For Large Tables, You May Need an Outage Window
Checking of the existing data can take time, burn a lot of IO, and it will require schema modification locks, so you may want to do this during maintenance windows if the table is large. Test this first on a development database to get an idea of what you’re in for.
Will Checking Keys and Constraints Always Work?
It might turn out that some of the data violates your constraints – but that’s a good thing to find out too.
After this change, you may see improved query performance for tables with trusted keys and constraints.
Want to learn more? Catch Kendra’s free video on foreign keys.
3 Comments.
The command to fix this issue should be:
ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName
(the current version has one too many CHECKS)
Great catch, Ed! Thanks, fixed.
It might also be worth adding a note that if a constraint is set NOT FOR REPLICATION, running this command will not change the un-trusted status of the constraint, because actions from replication procedures will still be able to bypass it.
Maybe NOT FOR REPLICATION constraints are a candidate for a seperate category in sp_Blitz?