Fix Untrusted Foreign keys

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
*/

Gather DB and Server Stats

To find how well a database is performing
Database: Buffer Cache Hit Ratio:
Buffer Cache Hit Ratio counter indicates how often SQL Server goes to the buffer, not the hard disk, to get data. This ratio should exceed 90%, and ideally be over 99%.If your buffer cache hit ratio is lower than 90%, you need to go out and buy more RAM today. If the ratio is between 90% and 99%, then you should seriously consider purchasing more RAM, as the closer you get to 99%, the faster your SQL Server will perform. In some cases, if your database is very large, you may not be able to get close to 99%, even if you put the maximum amount of RAM in your server.

select DB_name(), GetDate() as EventTime ,
((CONVERT(decimal(6,2),t1.cntr_value)/CONVERT(decimal(6,2),t2.cntr_value))*100) as HitRatio
from sys.dm_os_performance_counters t1,
sys.dm_os_performance_counters t2
where
t1.object_name LIKE '%Buffer Manager%'
and t1.object_name = t2.object_name
and t1.counter_name='Buffer cache hit ratio'
and t2.counter_name='Buffer cache hit ratio base'

--NB: Sometimes this percentage will show greater than 100% (The key is to look for dips in Hit Ratio starting below 99-98%)


Get Server Stats: CPU Utilization

declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info

select top 1 @@SERVERNAME,record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
from (
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as SystemIdle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as SQLProcessUtilization,
timestamp
from (
select timestamp, convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%%') as x
) as y
order by record_id desc
End

(NB: You can either set up a job or use perfmon to gather these)

Performance Counters for Server Audit

Performance counter: Processor(_Total)\%Processor Time gives CPU Utilization.
Threshold: Should be always below 100%, ocassional peaks are fine.
If it shows a constant 100%, there will be a bottleneck, bad response times, reponse failures etc.

Performance counter: Memory\Pages/Sec
Threshold: Sustained values higher than five indicate a bottleneck.
Significance: This counter indicates the rate at which pages are read from or written to disk to resolve hard page faults.

Performance counter: Memory\Available Mbytes
Threshold: A consistent value of less than 20 to 25 percent of installed RAM is an indication of insufficient memory.
Significance: This indicates the amount of physical memory available to processes running on the computer. Note that this counter displays the last observed value only. It is not an average.

Performance counter: Memory\Pages faults/Sec
The Page Faults/sec value is made up of hard and soft page faults (If the page is loaded in memory at the time the fault is generated, but its status is not updated as 'present' in hardware, then it is called a minor or soft page fault). So it is a little misleading value.
If page faults is too high, check the pages\sec value. If that is high means you have hard page faults and an immidiate action is required. If that is less than 5, then possibly the number is only soft page faults and nothing to worry about. Though most admins will want it to be below 15-20.

More explanation on performance counters