Untrusted check constraints

| 5 Comments | 5,527 views
Categories:

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

This metric returns the number of check constraints that have their is_not_trusted flag set to 1 in the sys.check_constraints table. Untrusted constraints force SQL Server to construct less efficient query plans, because it doesn’t know enough about the kind of data contained in the table. This can point to a data integrity issue which should be investigated.

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

SELECT * FROM sys.check_constraints WHERE is_not_trusted = 1

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

Untrusted check constraints, 3.7 out of 5 based on 3 ratings

Metric definition

Metric Name:
Untrusted check constraints
Description:

The number of check constraints that have their is_not_trusted flag set to 1 in the sys.check_constraints table.

If untrusted check constraints exist, find out which ones they are by running:

SELECT * FROM sys.check_constraints WHERE is_not_trusted = 1
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 needs to be combined with the @DBName variable */
DECLARE @strSQL VARCHAR(200)
SET @strSQL = 'SELECT COUNT(*) FROM [' + @DBName + '].sys.check_constraints WHERE is_not_trusted = 1'

/*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 check constraints
Description:

This alert is raised when SQL Monitor detects check constraints that have their is_not_trusted flag set to 1 in the sys.check_constraints table.

To find out where untrusted check constraints exist, run:

SELECT * FROM sys.check_constraints WHERE is_not_trusted = 1
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
Raise an alert when the threshold is passed for:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 3.7/5 (3 votes cast)
  • Todd

    Woo Hoo!! Now I know I have them. How about telling me what to do with that info? That is to say how do I change them to trusted? Don’t assume I know.
    That said, thinks for bring this to my attention. Now that I know I do not know I can change.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
    • Todd

      Sorry to the bad sentence construction. Also, should be “thanks” not “thinks”.

      VA:F [1.9.17_1161]
      Rating: 0 (from 0 votes)
  • CNU

    how to fix these not trusted constraints?

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
    • purumae

      ALTER TABLE [Table Name] WITH CHECK CHECK CONSTRAINT [Constraint Name];

      VA:F [1.9.17_1161]
      Rating: 0 (from 0 votes)
  • SQL Monitor Team

    Thanks for the feedback. We’ve updated the introduction to this metric – it now includes a link to Brent Ozar’s great article on how to fix the problem.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)