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

First, get the list of affected foreign keys with this T-SQL script.  It must be run inside the affected database(s):

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

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

(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.

ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName

The word CHECK appears twice on purpose – this tells SQL Server that it needs to check the data, not just enable the constraint.  The checking of the existing data can take time and burn a lot of IO throughput, so you may want to do this during maintenance windows if the table is large.  Also, 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.

Return to sp_Blitz or Ask Us Questions

css.php