I recently ran one of Brent Ozar’s Blitz tools (sp_BlitzIndex specifying a table name) and noticed that the output showed the table with is_not_trusted set for a foreign key on the table. As many of you know, SQL Server will set a table to an “untrusted” state when you temporarily disable a foreign key constraint, then re-enable it. SQL Server can in many cases build more efficient execution plans when it can trust that the constraint is valid, so it is usually best to make sure your foreign keys are trusted.
The common fix for the issue is to tell SQL Server to recheck the constraint when the constraint is re-enabled. The syntax is a bit weird since it has a double CHECK in the statement, but it makes more sense when you understand what it’s actually doing.
ALTER TABLE TableName WITH CHECK CHECK CONSTRAINT ConstraintName
The command for re-enabling the key constraint often leaves out the WITH CHECK option. This option tells SQL Server to check the current table data against the constraint. The second CHECK tells SQL server to enable the constraint and check any new items added to the table (as opposed to NOCHECK which disables it). It’s perhaps unfortunate that the same keyword provides two separate functions in the same command, but that’s the syntax we have to live with.
Seeing the table state with is_not_trusted set, I dutifully ran the alter table command and rechecked the table. Imagine my surprise when the table was still not trusted! What’s more, most of the articles that discuss untrusted foreign keys include queries to find untrusted foreign keys and my “bad” keys didn’t show up in those queries.
I noticed that the BlitzIndex output showed is_not_for_replication set as well and that most of the queries looking for untrusted indexes that specifically did not have that flag set. It turned out that many of my most active tables were untrusted and set for Not For Replication mode. The problem is, my system isn’t using replication and should not have had the bit set. As near as I can determine, the foreign keys have been untrusted since the tables were first created in 2003. I suspect the person who originally created the tables used the table designer to create the keys. The table designer has a Yes/No combo Box labeled “Enforce For Replication”, which might lead someone who doesn’t realize what it’s doing to believe they can set it to NO when they aren’t using Replication.
So how do you find out what keys are untrusted?
Listing 1 below is slightly modified version of Brent Ozar’s query from http://www.brentozar.com/blitz/foreign-key-trusted/ and will show you ALL tables in the current database which are not trusted. Brent’s query shows only untrusted tables that don’t have the NOT FOR REPLICATION flag set. I’ve also added the flags to the output.
Listing 1
SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname, is_disabled, is_not_trusted, is_not_for_replication from sys.foreign_keys i INNER JOIN sys.objects o ON i.parent_object_id = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE i.is_not_trusted = 1 -- AND i.is_not_for_replication = 0
I’ve simulated the problem using the Person.Person table in AdventureWorks. by modifying the foreign key to set the Not For Replication bit. The outputs below are from that simulation.
Once you have your list of untrusted foreign keys, you should take several steps to resolve the problem.
1. Make sure that the table isn’t replicated. The setting has a purpose and is needed in certain replication situations. If the table is replicated, you may need to leave it alone, but definitely will need to do more research and testing before changing it in your production environment. (Discussion of when to use NOT FOR REPLICATION is beyond the scope of this article but is in BOL, start here http://technet.microsoft.com/en-us/library/ms152529(v=sql.105).aspx ).
Do the remaining steps during a maintenance window since some of these steps will lock your tables for the changes are made.
2. I like to check the table’s consistency before making other changes. Use DBCC to do this.
DBCC CheckConstraints(TableName)
Resolve any consistency problems before continuing. I was fortunate that all my tables pass the checks.
3. Modify the foreign key to remove the NOT FOR REPLICATION setting. You can do this by right clicking the foreign key in SSMS and either choosing Modify to use the table designer or script a Drop and Create to a new windows and remove the NOT FOR REPLICATION option for the create. Here is the SSMS option shown in the image below.
Be sure and save the table after making your modification.
You can also use a script like this one. Note, you would need to change the values to your specific tables, columns, constaints, etc.
USE [AdventureWorks2012] GO EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA' , @level0name=N'Person' , @level1type=N'TABLE' , @level1name=N'Person' , @level2type=N'CONSTRAINT' , @level2name=N'FK_Person_BusinessEntity_BusinessEntityID' GO ALTER TABLE [Person].[Person] DROP CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] GO ALTER TABLE [Person].[Person] WITH NOCHECK ADD CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID]) REFERENCES [Person].[BusinessEntity] ([BusinessEntityID]) -- NOT FOR REPLICATION GO ALTER TABLE [Person].[Person] CHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] GO EXEC sys.sp_addextendedproperty @name=N'MS_Description' , @value=N'Foreign key constraint referencing BusinessEntity.BusinessEntityID.' , @level0type=N'SCHEMA' , @level0name=N'Person' , @level1type=N'TABLE' , @level1name=N'Person' , @level2type=N'CONSTRAINT' , @level2name=N'FK_Person_BusinessEntity_BusinessEntityID' GO
If you script the Drop and Create to a window, comment out the NOT FOR REPLICATION option and execute it.
4. If you run the query in listing 1, again you will notice that the key is still not trusted but is no longer has is_not_for_replication set.
5. Use the standard command to check foreign key consistency and make it trusted again.
ALTER TABLE [Person].[Person] WITH CHECK CHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
7. Run the query once more and you’ll no longer see it in the untrusted list.
SUCCESS!!!
Repeat for other tables as needed.