Does Your Database Have Trust Issues?

You probably know someone with trust issues, but do you know if your database has trust issues? In other words, does your database have Check Constraints and Foreign Keys that are not trusted? If so, it’s important to realize that SQL Server may not be able to use them for query plans and optimization, which can in turn affect the performance of your queries. Check Constraints and Foreign Keys that were disabled, perhaps for bulk loading of data, and then enabled again, are no longer trustworthy to SQL Server. As a result, tables must be altered for those Check Constraints and Foreign Keys to earn SQL Server’s trust again.

Custom Conditions

It probably seems like I am always mentioning SQL Sentry Custom Conditions, and perhaps I am. In fact, Custom Conditions are the reason I am writing this blog post. In the next Custom Conditions Pack, there are conditions created from the “Check Constraints: Objects Not Trusted by Database Count” and “Foreign Keys: Objects Not Trusted by Database Count” scripts (below) to help you stay on top of these potential performance issues across your SQL Sentry monitored databases. As a matter of fact, the next pack will be published in only a couple of weeks, and there are numerous new conditions in it!

Performance Impact

Trust Issues Hurting Performance?

To begin with, this post by Sarvesh Singh has a great example of how the performance is impacted. Since that is worthy of its own long blog post, I’ll refer you there for the details.

Next, I set up a very similar quick example using a DogOwners and Dogs table (because if you haven’t noticed, I am a dog person!) and used SQL Sentry Plan Explorer to explore the differences in execution.

Example:

This example has a simple select all from the Dogs table with a where exists sub-select on the DogOwners table, where the OwnerIDs in each table are a match. First of all, let’s take a look at the Plan Diagram when the Foreign Key exists and is trusted:

Plan Diagram with Foreign Key

Notice that Dogs is the only table used in the plan. Comparatively, that is not the case below:

Plan Diagram without Foreign Key

Now, the Foreign Key constraint is not checked, and consequently there’s a join to the DogOwners table. As a result, the plan has changed (for the worse) without the Foreign Key constraint (nocheck).

Plan Tree with Foreign Key

Plan Tree without Foreign Key

Accordingly, the Plan Tree tab in Plan Explorer shows some key information about the increased number of steps, executions, and cost when the Foreign Key constraint is not used by the query optimizer.

Table I/O with Foreign Key

Table IO without Foreign Key

Finally, a look at the Table I/O tab shows an increase in logical reads. Due to the Foreign Key being turned off, the total logical reads increases from just 2 to 202!

Check Constraints

The scripts below will show you how to discover which databases have trust issues with Check Constraints, which specific Check Constraints are not trusted, and how you can fix them.

Check Constraints not Trusted:

First, let’s looks for all databases on an instance with trust issues regarding Check Constraints:

DECLARE @sql nvarchar(max);
SET @sql = N'';
SELECT @sql = @sql + N'UNION ALL 
  SELECT DBName = N''' 
   + name 
   + ''' COLLATE Latin1_General_BIN, 
  CCsNotTrusted =  
  (
    SELECT COUNT(*) AS CCsNotTrusted
      FROM ' + QUOTENAME(name) 
      + '.sys.check_constraints AS c'
      + N' WHERE c.is_not_trusted = 1 
        AND c.is_not_for_replication = 0 
        AND c.is_disabled = 0
  )
  ' FROM sys.databases 
WHERE database_id > 4 AND state = 0;
 
SET @sql = N'SELECT DBName, CCsNotTrusted FROM 
(' + STUFF(@sql, 1, 10, N'') 
   + N') AS x WHERE CCsNotTrusted > 0;';
 
EXEC sys.sp_executesql @sql;

Untrustworthy Check Constraints by Database

Check Constraints: Objects Not Trusted by Database Count

Second, we’ll obtain a list of those specific Check Constraints with trust issues for a database in the above results:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) 
  + N'.' + QUOTENAME(c.name) AS CCsNotTrusted
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
ORDER BY CCsNotTrusted;

Untrustworthy Check Constraints

Check Constraints: Objects Not Trusted

Restoring Trust

Finally, we’ll execute statements to restore trustworthiness to individual Check Constraints:

ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ConstraintName;

Fix Check Constraints

ALTER TABLE [dbo].[dogs] WITH CHECK CHECK CONSTRAINT [chk_dog_owners];

Fix an Example Constraint

Moreover, you may have many Check Constraints to fix, in which case, you can run this script so that you can generate the necessary T-SQL:

SELECT N'ALTER TABLE ' 
    + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) 
    + N' WITH CHECK CHECK CONSTRAINT ' 
    + QUOTENAME(c.name) + N';' AS CCsToFix
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
ORDER BY CCsToFix;

Generate Fix Check Constraints Script

Generated Scripts to Fix Check Constraints

Foreign Keys

In addition to check constraints not being trusted, you can have essentially the same problem with foreign key constraints. Likewise, there’s a great video by Kendra Little (b|t) on the brentozar.com website which provides a detailed explanation of this issue.

Foreign Keys not Trusted:

Again, we’ll first look for all databases on an instance with trust issues, but this time for foreign key constraints.

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name)
  + N'.' + QUOTENAME(f.name) AS FKsNotTrusted
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
ORDER BY FKsNotTrusted;

Untrustworthy Foreign Keys by Database

Foreign Keys: Objects Not Trusted by Database Count

Next, we’ll get a list of affected foreign key constraints from one of the databases in the previous results set:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name)
  + N'.' + QUOTENAME(f.name) AS FKsNotTrusted
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
ORDER BY FKsNotTrusted;

Untrustworthy Foreign Keys

Foreign Keys: Objects Not Trusted

Restoring Trust

Lastly, we’ll use another query to create a script for restoring trustworthiness to Foreign Keys:

SELECT N'ALTER TABLE ' + QUOTENAME(s.name) 
  + N'.' + QUOTENAME(o.name) + N' WITH CHECK CHECK CONSTRAINT ' 
  + QUOTENAME(f.name) + N';' AS FKstoFix
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
 ORDER BY FKstoFix;

Fix Foreign KeysSummary

As shown above, trust issues in your databases can cause performance issues with poor query plans. Overall, these constraint issues are not difficult to resolve, however, they frequently go unnoticed. Regularly checking for trust issues across your databases via Custom Conditions in SQL Sentry will help you stay ahead of any potential performance issues caused by a lack of trust.