Untrusted foreign keys

| 0 comments | 8,861 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (3 votes cast)

This metric returns the number of foreign keys that have their is_not_trusted flag set to 1 in the sys.foreign_keys table.

A foreign key points to a primary key that must exist in another table, for example, column X in Table 1 must also be present in Table 2. The key protects this link, and only valid data can be inserted in the foreign key column. An untrusted foreign key may threaten a database’s referential and data integrity.

If the metric and alert tells you that untrusted foreign keys exist, find out which ones they are by running:

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1 AND is_not_for_replication = 0

For details of how to fix them, see Brent Ozar’s article: Blitz Result: Foreign Keys or Check Constraints Not Trusted.

Untrusted foreign keys, 5.0 out of 5 based on 3 ratings

Metric definition

Metric Name:
Untrusted foreign keys
Description:
The number of foreign keys that have their is_not_trusted flag set to 1 in the sys.foreign_keys table. If foreign keys exist, find out which ones they are by running:
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1 AND is_not_for_replication = 0
For details of how to fix them, see Brent Ozar's article: Blitz Result: Foreign Keys or Check Constraints Not Trusted.
Enter the T-SQL query that will collect data:
/*get the current database name*/
DECLARE @DBName VARCHAR(500)
SELECT @DBName = DB_NAME()

/*set the tsql to be executed in a variable as it need to be combined with the @DBName variable */
DECLARE @strSQL VARCHAR(200)
SET @strSQL = 'SELECT COUNT(*) FROM [' + @DBName + '].sys.foreign_keys WHERE is_not_trusted = 1 AND is_not_for_replication = 0'

/*execute the tsql*/
EXEC (@strSQL)
Select instances to collect from:
Select all
Choose databases to collect from:
All databases
Set collection frequency:
Collect data every day
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
Enabled

Alert definition

Alert name:
Untrusted foreign keys
Description:
This alert is raised when the number of foreign keys detected exceeds the thresholds set. If foreign keys exist, find out which ones they are by running:
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1 AND is_not_for_replication = 0
For details of how to fix them, see Brent Ozar's article: Blitz Result: Foreign Keys or Check Constraints Not Trusted.
Raise an alert when the metric value goes:
Above the defined thresholds
Default threshold values:
Set the thresholds as follows:
High:0
Medium:Disabled
Low:Disabled
Note: These threshold settings are intended as guideline values. You may consider replacing them with values more suited to your environment.
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 5.0/5 (3 votes cast)