Query Optimizer Tips and Tricks Part 2
This is the second installment in a continuing series of articles on things to look for and change that will help your query optimizer perform at its best. If you missed part one, you can read it here.
Okay, now that you know about trusted vs. non-trusted check constraints, let’s talk about foreign keys. What are foreign keys? They are another type of constraint, right? Just like check constraints, if they are not handled correctly the query optimizer will stop trusting them. The scenario is similar to check constraints but is, in my experience, more prevalent and usually has a greater negative impact.
Non-Trusted Foreign Keys
How many times have you been attempting a large data load, and to speed things up you disabled the foreign keys? Data loads are much faster if it doesn’t have to keep checking each row for referential integrity (RI), right? Or, more likely is the scenario that you’ve found that you can’t load the data because something is contrary to the foreign key constraint. Just disable that pesky FK and now you can load the data no problem. Re-enable the foreign key(s) and voilà, everything works. Isn’t that convenient? Well, just as with check constraints, this scenario causes the query optimizer to no longer trust the foreign key. Just as with check constraints, if you disable a foreign key and don’t use the WITH CHECK option when you enable it, the foreign key becomes not trusted.
Okay, maybe you’ve never done this, but I guarantee you it is happening all over the SQL map. I have yet to find a company that didn’t have a fair number of non-trusted foreign keys in their production environment.
Let’s see what the ramifications are.
USE AdventureWorks2012;
SELECT name, is_not_trusted FROM sys.foreign_keys WHERE is_not_trusted = 1;Presumably no rows will be returned from this query if you haven’t been messing with your foreign keys in AdventureWorks. Picking a foreign key at random, let’s disable/enable it without the WITH CHECK option:
-- Break it
ALTER TABLE [Person].[Person] NOCHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
GO
ALTER TABLE [Person].[Person] CHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
GOEnabling the foreign key without the WITH CHECK option will enable the foreign key constraint, but the data will not be verified for integrity.
-- Check it
SELECT name, is_disabled, is_not_trusted FROM sys.foreign_keys
WHERE name = 'FK_Person_BusinessEntity_BusinessEntityID';And it should be no surprise that the result is:
The foreign key is no longer trusted by the optimizer. Oops! So if we query the data now, what does the query optimizer do?
-- Query it
SET NOCOUNT ON;
SET STATISTICS XML ON;
GO
SELECT Person.* FROM Person.Person Person
JOIN Person.BusinessEntity be ON Person.BusinessEntityID = be.BusinessEntityID;
SET STATISTICS XML OFF;
SET NOCOUNT OFF;
GOThe stats and execution plan we generated:
Table 'Person'. Scan count 1, logical reads 3820, physical reads 2, read-ahead reads 3826 Table 'BusinessEntity'. Scan count 1, logical reads 98, physical reads 1, read-ahead reads 96 SQL Server Execution Times: CPU time = 218 ms, elapsed time = 2785 ms.
Note that there is a scan of both the Person and BusinessEntity tables.
Now, let’s make the foreign key trusted by enabling the foreign key again using the WITH CHECK option:
-- Fix it
ALTER TABLE [Person].[Person] WITH CHECK CHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID]
GOThe WITH CHECK option will cause SQL Server to check all the data to verify that it does not conflict with the foreign key constraint.
NOTE: If the tables involved are large this may take some time and you may not want to do it during normal operational hours
-- Check it
SELECT name, is_disabled, is_not_trusted FROM sys.foreign_keys
WHERE name = 'FK_Person_BusinessEntity_BusinessEntityID';Now the result is:
The foreign key is now trusted by the optimizer. So if we query the data now, what does the query optimizer do?
-- Query it
SET NOCOUNT ON;
SET STATISTICS XML ON;
GO
SELECT Person.* FROM Person.Person Person
JOIN Person.BusinessEntity be ON Person.BusinessEntityID = be.BusinessEntityID;
SET STATISTICS XML OFF;
SET NOCOUNT OFF;
GO
The stats and execution plan we generated:
Table ‘Person’. Scan count 1, logical reads 3820, physical reads 2, read-ahead reads 3826
SQL Server Execution Times:
CPU time = 156 ms, elapsed time = 2310 ms.
Wow, look at that! The Person table has been completely dropped from the query and there is no merge either. The query optimizer trusted the foreign key and therefore did not have go check the data in the Person table to verify that the data met the criteria. The difference is visible even in these small tables, it could be significant if these were large tables.
And here is the T-SQL script that will find all your non-trusted foreign keys:
EXEC sp_MSforEachDB N'
USE [?];
SELECT fk.name, fk.is_not_trusted, ''Database Table'' = ''?'' + ''.'' + sch.name + ''.'' + obj.name
FROM sys.foreign_keys fk
JOIN sys.all_objects obj ON fk.parent_object_id = obj.object_id
JOIN sys.schemas sch ON fk.schema_id = sch.schema_id
WHERE fk.is_not_trusted = 1'
Conclusion
The case for checking your foreign keys for the is_not_trusted flag has been made, and now that you know what the ramifications are, get out there and do it! You may not be the one causing the problem, but if you’re not the only person working on an instance, somebody WILL disable foreign keys to speed up data loads, or to get around bad data.
