Trust Issues Hurting Database Performance?

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;