SQL Server Performance Tuning

Untrusted Foreign Keys and Constraints Can Degrade Your SQL Server Performance

Updated October 29, 20254 min read

Untrusted foreign keys slow SQL Server. A ring of keys hangs from a hook, one key tagged with a red X.

Disabling keys and constraints to improve the performance of loading data is a common practice in large data warehouses and other environments with big tables.

This technique is relatively more natural and safer than dropping and recreating all the keys and constraints if you don’t forget to enable them after the work has been done.

Having untrusted or disabled FOREIGN KEY or CHECK constraints in your database may cause query performance problems with inadequate query plans and degrade data consistency and integrity.

How to check for untrusted foreign keys and constraints?

Use the script below to find untrusted foreign keys:

T-SQL
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;

Use the following script to get the list of untrusted constraints:

T-SQL
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;

How to restore trust for foreign keys and constraints?

Once you have that result set above, you can use the alter table statement to re-establish the trust for each result in the list.

Just replace “MyTableName” and “MyConstraintName” with your table name and foreign key/constraint name in the code below.

T-SQL
ALTER TABLE MyTableName
	WITH CHECK CHECK CONSTRAINT MyConstraintName

Alternatively, you can also run the script below that will generate the necessary T-SQL to recheck all the untrusted constraints:

T-SQL
SELECT N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(c.name) + N';' AS ConstraintsToFix
FROM sys.check_constraints c
INNER JOIN sys.objects o ON c.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE c.is_not_trusted = 1
	AND c.is_not_for_replication = 0
	AND c.is_disabled = 0

The following script will generate the necessary T-SQL to recheck the untrusted foreign keys:

T-SQL
SELECT N'ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(f.name) + N';' AS ForeignKeystoFix
FROM sys.foreign_keys f
INNER JOIN sys.objects o ON f.parent_object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE f.is_not_trusted = 1
	AND f.is_not_for_replication = 0

More information:

Check Red9's SQL Server Services

SQL Server Consulting

Perfect for one-time projects like SQL migrations or upgrades, and short-term fixes such as performance issues or SQL remediation.

Discover More ➜

SQL Server Managed Services

Continuous SQL support, proactive monitoring, and expert DBA help with one predictable monthly fee.

Discover More ➜

Emergency SQL Support

Take the stress out of emergencies with immediate access to a SQL Server Sr. DBA 24x7x365

Discover More ➜

Explore All Services ➜