Query tuning 101: Non-trusted Foreign Keys

Foreign keys are an interesting feature of relational databases. They help enforce data integrity, sometimes help improve performance by eliminating joins, and sometimes slow down DML operations (inserts, updates, and deletes).

For this post I want to focus on what a non-trusted FK or constraint is. By it’s definition, SQL Server does not trust the relationship and this could be both good and bad.

The Good

I don’t need to validate relationships on DML. This can be great for performance.

Inserting large amounts of data into the table? Why not disable the check on your key to speed along the process? The following example shows what happens when a key is trusted and not.

image

Note that the trusted keys greatly reduce performance for this simple insert statement. Too many keys may also lead to deadlocks if the application isn’t designed well.

The Bad

Keys and constraints provide performance benefits on some SELECT queries and also keep the data in check by validating what is inserted or updated. They can also cascade delete data in setup to do so.

When a constraint or key is not trusted none of the aforementioned items can be performed.

Let’s take a look at how join elimination works for trusted and non-trusted constraints and keys.

image

Note that the optimizer completely eliminated a branch from the query plan. This is because the data is trusted in the child table and does not need to be accessed to produce the result.

How do I find Non-Trusted Keys in my instance?

Finding the Foreign Keys with these issues is easy. Simply run the following query to identify the offenders. *Note that this should be run in each database on the instance

SELECT OBJECT_NAME(referenced_object_id) as obj, name
FROM sys.foreign_keys
WHERE is_not_trusted = 1

Each row returned is a foreign key that is not trusted.

Why is it untrusted? Perhaps we disabled the check to load data and neglected to re-enable it?

No matter what the reason is the next part is not as simple. This is for two reasons.

  1. The data in the child table may not be valid. Since the key was not being checked I may have data in my table that isn’t represented in the parent.
  2. The syntax is a bit silly. As Mike Byrd in Austin, TX says, Microsoft studders. The syntax to reenable is “CHECK CHECK”. Let’s look at how we reenable the Address key check.

ALTER TABLE Person.BusinessEntityAddress WITH CHECK CHECK CONSTRAINT FK_BusinessEntityAddress_Address_AddressID;

In summary

Know when Keys are best trusted and not trusted.

Evaluate your keys on a regular basis. What you find may be shocking.

If you’d like to work with me or need help tuning your environment, please reach out on LinkedIn, Twitter, or daniel@austindatapros.com. I’m always happy to meet new friends in the community.

4 thoughts on “Query tuning 101: Non-trusted Foreign Keys

  1. Getting a FK back to trusted status is almost impossible in a 24×7 environment. For SQL Server to go back and check existing data, it puts a schema lock on the child table for the duration of the data check. This is almost unacceptable in a 24×7 operation.

    Like

  2. This particular implementation of PK – FK relations is not the only way to do it. I liked the old Watcom SQL Anywhere method. Primary key values appear only in the referenced table. But all the foreign key references are implemented as pointer chains. This makes joins incredibly fast, prevents orphans, etc. This is a version of what we used to do inside network databases, so the technology was very well known before it was used in SQL.

    Like

  3. In a testing environment, this will produce the necessary output:

    SELECT ‘ALTER TABLE ‘+OBJECT_NAME(fk.parent_object_id)+
    ‘ WITH CHECK CHECK CONSTRAINT ‘+fk.name+’;’ as Instruction
    FROM sys.foreign_keys fk
    inner join sys.foreign_key_columns fkc on fk.object_id=fkc.constraint_object_id
    WHERE is_not_trusted = 1

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s