Query Optimizer Part 1
It may come as a surprise to you to find out that at its core, the SQL Server Query Optimizer is like a jealous lover. How so? The optimizer doesn’t trust the constraints on your data until you prove to it that your data is trustworthy! Why do you care? Because if the optimizer doesn’t trust your constraints then it feels the need to check the data to make sure it is right. This article discusses how the constraints get on the query optimizer’s bad side, how to find the wayward constraints, what effect having non-trusted constraints has on a query, and how to bring them back into the optimizer’s good graces.
Non-Trusted Constraints
How do your constraints become non-trusted constraints? It’s simple. If you disable a constraint, and then do not specify the WITH CHECK option when you enable it, SQL Server does not check the data to make sure it meets the criteria of the constraint. Some people do this intentionally to enter data into a table that is contrary to the constraint (a bad practice), but more often, they simply did not know to use the WITH CHECK option when enabling. When this happens (intentionally or not), the SQL optimizer no longer trusts your data. Remember, she is a jealous lover (I’m a guy, so I’m going with “she” here). When your data is out of her watchful eye for even just a minute she won’t trust the data again until she can see for herself that the data deserves her trust. Your constraint is branded as untrustworthy… or slightly less dramatically, the constraint is denoted as a non-trusted constraint by the is_not_trusted column in sys.check_contraints = ‘1’.
Let’s look at an example by looking at good ‘ol AdventureWorks to see if you have any non-trusted check constraints.
USE AdventureWorks2012;
GO
SELECT name, is_not_trusted FROM sys.check_constraints WHERE is_not_trusted = 1;
Presumably, this should return no rows if you haven’t been messing around with AdventureWorks constraints (She will know!). Now let’s disable and re-enable a constraint without the WITH CHECK option.
ALTER TABLE [Person].[Person] NOCHECK CONSTRAINT [CK_Person_EmailPromotion]
GO
ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_EmailPromotion]
GO
SELECT name, is_not_trusted FROM sys.check_constraints
WHERE name = 'CK_Person_EmailPromotion';
The select now returns:
Well, there you go. Now the optimizer is all up in your query plans (we’ll see those later), checking your data because she no longer trusts that it is consistent. How do we calm her frazzled sensibilities? You guessed it (I hope). We allow SQL Server to check and verify that the data is correct by re-enabling the constraint utilizing WITH CHECK:
ALTER TABLE [Person].[Person] WITH CHECK CHECK CONSTRAINT [CK_Person_EmailPromotion]
GO
The select now returns:
Note: The WITH CHECK option causes SQL Server to read all the data in the table for the affected column. If the table in question is very large it may take a while, and you may not want to do it on a production server during normal operational hours.
So why doesn’t SQL Server trust the constraint if the data is not checked when you enable it? Because while the constraint is disabled you could enter data that is contrary to the constraint. In the above example, the constraint ‘CK_Person_EmailPromotion’ defines that the column EmailPromotion on the Person table must be >= 0 or <= 2.
ALTER TABLE [Person].[Person] WITH CHECK ADD CONSTRAINT [CK_Person_EmailPromotion] CHECK (([EmailPromotion]>=(0) AND [EmailPromotion]<=(2)))
GO
So, if you did this:
ALTER TABLE [Person].[Person] NOCHECK CONSTRAINT [CK_Person_EmailPromotion]
GO
UPDATE [Person].[Person] SET EmailPromotion = 3 WHERE BusinessEntityID = 1;
ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_EmailPromotion]
GO
You would have data in the table that does not comply with the constraint (EmailPromotion = 3). The optimizer is right not to trust your data. If you try to enable the constraint using WITH CHECK now, it fails.
Now you have to fix the data and then you can enable the constraint using WITH CHECK.
UPDATE [Person].[Person] SET EmailPromotion = 0 WHERE BusinessEntityID = 1;
ALTER TABLE [Person].[Person] WITH CHECK CHECK CONSTRAINT [CK_Person_EmailPromotion]
GO
As I mentioned earlier, this has consequences for the optimizer and your query plans. Let’s check it out. If we run the following select statement while the CK_Person_EmailPromotion constraint is trusted (is_not_trusted = 0):
SET STATISTICS IO,TIME ON;
SET STATISTICS XML ON;
GO
SELECT FirstName, LastName FROM Person.Person WHERE EmailPromotion = 3
We get the following statistics:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
And here is the Query Plan.
If we now run the same query while the CK_Person_EmailPromotion constraint is NOT trusted (is_not_trusted = 1), our statistics look like this:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 16 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table ‘Person’. Scan count 1, logical reads 3820, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 11 ms.
And our Query Plan now has an Index Scan in it:
Notice that the 2nd query with the non-trusted constraint has a clustered index scan with 3820 logical reads. The 1st query with the trusted constraint has zero logical reads and no index scan. Why? Well, if you remember the constraint stipulated that the value of the EmailPromotion column had to be >= 0 and <= 2. The query is looking for all rows with an EmailPromotion = 3. When the optimizer trusts your constraint it knows that none of the rows will meet that criteria, so it doesn’t even bother to look. However, if the optimizer doesn’t trust your constraint, it has to scan every row to see if any have the value of 3, a value that clearly could be there (since we added one) if the constraint is not trusted. This is a simple example on a small table but you can see where this could be costly on a larger table.
Here is a nice T-SQL script that will find all your non-trusted constraints:
EXEC sp_MSforeachdb N'
USE [?];
SELECT chk.name, chk.is_not_trusted, [Database Table] = ''?'' + ''.'' + sch.name + ''.'' + obj.name
FROM sys.check_constraints chk
JOIN sys.all_objects obj ON chk.parent_object_id = obj.object_id
JOIN sys.schemas sch ON chk.schema_id = sch.schema_id
WHERE chk.is_not_trusted = 1'
Conclusion
If you’re not checking your constraints for the is_not_trusted flag , you should be. This is especially true if you have developers that have access to your production databases. Developers WILL disable constraints to speed up data loads, or to get around bad data. I like to think that we as DBAs would know better, but I am sure we’ve all done it too (not me!). Now that you know what the ramifications are, get out there and find those wayward constraints and bring them back into the optimizer’s good graces. She won’t be jealous if you are always on the up and up!
