Going off of Brent Ozar's note on fix untrusted foreing keys here:
http://www.brentozar.com/blitz/foreign-key-trusted/
He explains how the optimizer cannot use untrusted Fk's to generate query plan and hence can lead to suboptimals plans getting selected and hence slowness.
we decided to work on untrusted foriegn key contraints in our system to get performance improvements if any. With a system of hundreds of dbs below is the query I came up with. For finding untrusted fks and also the count of records that did not satisfy the FK constraint (if any i.e. values in child table which were not in parent table). For those the records needed to be fixed before fixing the untrusted FK's.
This was run on all dbs at once using registered server and results copied to excel
CREATE TABLE #temp
(
servername NVARCHAR(500) ,
dbname NVARCHAR(500) ,
parenttable NVARCHAR(500) ,
RefTable NVARCHAR(500) ,
FKName SYSNAME ,
keyname NVARCHAR(1000) ,
PTForeignKeyColumn NVARCHAR(500) ,
RTForeignKeyColumn NVARCHAR(500) ,
cntStmt NVARCHAR(MAX) ,
cntnotexistsRecords INT NULL
)
--select top 1 * from sys.foreign_key_columns
EXEC sp_msforeachdb N' USE [?]
INSERT INTO #temp(servername,dbname,parenttable,RefTable,FKName,keyname,PTForeignKeyColumn,RTForeignKeyColumn,cntStmt,cntnotexistsRecords)
SELECT DISTINCT @@servername AS servername, DB_NAME() AS dbname,OBJECT_NAME(i.parent_object_id) AS parenttable,OBJECT_NAME(i.referenced_object_id) AS RefTable,i.name AS FKName,
''ALTER TABLE ['' + s.name + ''].['' + o.name + ''] WITH CHECK CHECK CONSTRAINT ['' + i.name + '']'' AS keyname,
(SELECT
c.name + '',''
FROM
sys.columns c where fkc.parent_object_id = c.object_id AND c.column_id IN (select fkc2.parent_column_id FROM sys.foreign_key_columns fkc2 where i.parent_object_id = fkc2.parent_object_id AND i.referenced_object_id=fkc2.referenced_object_id and i.object_id=fkc2.constraint_object_id)
FOR XML PATH('''')) AS PTForeignKeyColumn,
(SELECT
c.name + '',''
FROM
sys.columns c where fkc.referenced_object_id = c.object_id AND c.column_id IN (select fkc2.referenced_column_id FROM sys.foreign_key_columns fkc2 where i.parent_object_id = fkc2.parent_object_id AND i.referenced_object_id=fkc2.referenced_object_id)
FOR XML PATH('''') ) AS RTForeignKeyColumn,
'''' AS cntStmt,NULL AS cntnotexistsRecords
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
left JOIN sys.foreign_key_columns fkc ON i.parent_object_id = fkc.parent_object_id AND i.referenced_object_id=fkc.referenced_object_id
WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0
and i.is_disabled = 0
AND DB_NAME() not in (''master'',''msdb'',''tempdb'',''dfwr_th'',''model'',''mmshare'',''distribution'')
;'
/** Create untrusted-mismatched records count statement **/
UPDATE t
SET cntStmt = 'Select @cnt_OUT = COUNT(DISTINCT '
+ REPLACE(t.PTForeignKeyColumn, ',', '') + ') FROM [' + t.dbname
+ '].[dbo].[' + t.parenttable + '] WHERE '
+ REPLACE(t.PTForeignKeyColumn, ',', '') + ' NOT IN (SELECT '
+ REPLACE(t.RTForeignKeyColumn, ',', '') + ' FROM [' + t.dbname
+ '].[dbo].' + t.RefTable + ')'
FROM #temp t
WHERE ( LEN(t.PTForeignKeyColumn) - LEN(REPLACE(t.PTForeignKeyColumn, ',',
'')) ) = 1
/* CURSOR through to execute statement to get mismatched/untrusted record counts */
DECLARE @dbName SYSNAME ,
@parentbl SYSNAME ,
@reftbl SYSNAME ,
@fkname SYSNAME ,
@sqlStmt NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(200)
DECLARE cntcursor CURSOR
FOR
SELECT dbname ,
parenttable ,
reftable ,
fkname ,
cntstmt
FROM #temp
OPEN cntcursor
FETCH NEXT FROM cntcursor
INTO @dbName, @parentbl, @reftbl, @fkname, @sqlStmt
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @cnt INT
SET @ParmDefinition = N'@cnt_OUT int OUTPUT'
EXECUTE sp_executesql @sqlstmt, @ParmDefinition,
@cnt_OUT = @cnt OUTPUT;
UPDATE #temp
SET cntnotexistsRecords = @cnt
WHERE dbname = @dbName
AND parenttable = @parentbl
AND RefTable = @reftbl
AND FKName = @fkname
SET @cnt = 0
FETCH NEXT FROM cntcursor
INTO @dbName, @parentbl, @reftbl, @fkname, @sqlStmt
END
CLOSE cntcursor
DEALLOCATE cntcursor
SELECT *
FROM #temp
ORDER BY dbname
DROP TABLE #temp
/*
while doing the last select * from #tempdb, you can do a where cntnotexistsRecords=0 (to get only the records which satisfy the FK's) and cntStmt != NULL ( to get values where FK relationship do not involve more than one column, because I have refrained from creating a count statement for FK's involving multiple columns) This query is designed for Fk's with only one column
*/
