Benjamin Nevarez : Check Constraints, Contradiction Detection, Query Optimizerhttp://sqlblog.com/blogs/ben_nevarez/archive/tags/Check+Constraints/Contradiction+Detection/Query+Optimizer/default.aspxTags: Check Constraints, Contradiction Detection, Query OptimizerenCommunityServer 2.1 SP2 (Build: 61129.1)The Query Optimizer and Contradiction Detectionhttp://sqlblog.com/blogs/ben_nevarez/archive/2009/08/04/the-query-optimizer-and-contradiction-detection.aspxTue, 04 Aug 2009 06:56:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:15733Ben Nevarez7http://sqlblog.com/blogs/ben_nevarez/comments/15733.aspxhttp://sqlblog.com/blogs/ben_nevarez/commentrss.aspx?PostID=15733<P class=MsoNormal>One of the many interesting topics I found while working as technical editor of the SQL Server 2008 Internals book was the concept of contradiction detection. So, in this post I will to try to expand on this topic and provide some additional examples.</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>Contradiction detection is performed by SQL Server during the early steps of the query optimization process. During this process the Query Optimizer tries to find contradictions that can be removed and can make the query perform better. Since these parts of the query are not executed at all, SQL Server saves resources like I/O, locks, memory and CPU, making the query to be executed faster. For example, the Query Optimizer may know that no records can satisfy a predicate even before touching any page of data.</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>A contradiction may be related to a check constraint, or may be related to the way the query is written, that is, the query itself contains a contradiction. Let us explore both scenarios and analyze the execution plans that are generated on each case.</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>First, I need to a table with a check constraint. The following code creates a table that stores information about classic rock albums and its check constraint validates that every record entered was released before 1990.</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:green;FONT-FAMILY:'Courier New';mso-no-proof:yes;">-- create table </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">create</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">table</SPAN> albums<SPAN style="COLOR:blue;"> </SPAN><SPAN style="COLOR:gray;">( </SPAN></P>
<P></SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">name <SPAN style="COLOR:blue;">varchar</SPAN><SPAN style="COLOR:gray;">(</SPAN>40<SPAN style="COLOR:gray;">),</SPAN> </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">band <SPAN style="COLOR:blue;">varchar</SPAN><SPAN style="COLOR:gray;">(</SPAN>40<SPAN style="COLOR:gray;">),</SPAN> </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">album_year <SPAN style="COLOR:blue;">smallint</SPAN><SPAN style="COLOR:gray;">, </SPAN></P>
<P></SPAN><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">constraint</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> check_year <SPAN style="COLOR:blue;">check </SPAN><SPAN style="COLOR:gray;">(</SPAN>album_year <SPAN style="COLOR:gray;">&lt;</SPAN> 1990<SPAN style="COLOR:gray;">)) </SPAN></P>
<P></SPAN><SPAN style="FONT-SIZE:10pt;COLOR:green;FONT-FAMILY:'Courier New';mso-no-proof:yes;">-- add some records </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">insert</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">into</SPAN> albums <SPAN style="COLOR:blue;">values </SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:red;">'Machine Head'</SPAN><SPAN style="COLOR:gray;">,</SPAN> <SPAN style="COLOR:red;">'Deep Purple'</SPAN><SPAN style="COLOR:gray;">,</SPAN> 1972<SPAN style="COLOR:gray;">) </SPAN></P>
<P></SPAN><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">insert</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">into</SPAN> albums <SPAN style="COLOR:blue;">values </SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:red;">'A Night at the Opera'</SPAN><SPAN style="COLOR:gray;">,</SPAN> <SPAN style="COLOR:red;">'Queen'</SPAN><SPAN style="COLOR:gray;">,</SPAN> 1975<SPAN style="COLOR:gray;">) </SPAN></P>
<P></SPAN><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">insert</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">into</SPAN> albums <SPAN style="COLOR:blue;">values </SPAN><SPAN style="COLOR:gray;">(</SPAN><SPAN style="COLOR:red;">'British Steel'</SPAN><SPAN style="COLOR:gray;">,</SPAN> <SPAN style="COLOR:red;">'Judas Priest'</SPAN><SPAN style="COLOR:gray;">,</SPAN> 1980<SPAN style="COLOR:gray;">) </SPAN></P>
<P></SPAN>So, if I run a query to list all the records released, let us say before 1980, the execution plan uses a Table Scan operator, which is exactly what I expected for this scenario.</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">select</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:gray;">*</SPAN> <SPAN style="COLOR:blue;">from</SPAN> albums </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">where</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> album_year <SPAN style="COLOR:gray;">&lt;</SPAN> 1980 </SPAN></P>
<P>&nbsp;</P>
<P class=MsoNormal><A href="http://sqlblog.com/blogs/ben_nevarez/clip_image002_34F25E56.jpg"><IMG title=clip_image002 style="BORDER-TOP-WIDTH:0px;DISPLAY:block;BORDER-LEFT-WIDTH:0px;FLOAT:none;BORDER-BOTTOM-WIDTH:0px;MARGIN-LEFT:auto;MARGIN-RIGHT:auto;BORDER-RIGHT-WIDTH:0px;" height=117 alt=clip_image002 src="http://sqlblog.com/blogs/ben_nevarez/clip_image002_thumb_4769518A.jpg" width=271 border=0></A></P>
<P class=MsoNormal><B style="mso-bidi-font-weight:normal;">Check Constraint </B>
<P>But what about if somebody tries to request all the records released, for example, after 1995, like in the following query? Does the Query Optimizer know that no records will be returned even before looking at the data on the table?</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">select</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:gray;">*</SPAN> <SPAN style="COLOR:blue;">from</SPAN> albums </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">where</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> album_year <SPAN style="COLOR:gray;">&gt;</SPAN> 1995 </SPAN></P>
<P>In this case the Query Optimizer should know that because of the existing check constraint the previous query returns no records. So if I execute this query there is no need for SQL Server to access the data on the table, right? But surprisingly, the resulting execution plan still shows the Table Scan operator. Why?</P>
<P class=MsoNormal>Actually, there is another optimization called autoparameterization, which is closely related to the&nbsp;trivial plan feature and&nbsp;that&nbsp;overrides contradiction detection. To avoid this&nbsp;optimization I can use some features like query hints, joins, etc. like in the following example</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">select</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:gray;">*</SPAN> <SPAN style="COLOR:blue;">from</SPAN> albums </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">where</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> album_year <SPAN style="COLOR:gray;">&gt;</SPAN> 1995 </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">option </SPAN><SPAN style="FONT-SIZE:10pt;COLOR:gray;FONT-FAMILY:'Courier New';mso-no-proof:yes;">(</SPAN><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">maxdop</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> 1<SPAN style="COLOR:gray;">) </SPAN></P>
<P></SPAN>Once I change the query to use a query hint,&nbsp;the execution plan does not access the table at all, and instead it is just using a Constant Scan operator. </P>
<P class=MsoNormal><A href="http://sqlblog.com/blogs/ben_nevarez/clip_image004_2BF2F206.jpg"><IMG title=clip_image004 style="BORDER-TOP-WIDTH:0px;DISPLAY:block;BORDER-LEFT-WIDTH:0px;FLOAT:none;BORDER-BOTTOM-WIDTH:0px;MARGIN-LEFT:auto;MARGIN-RIGHT:auto;BORDER-RIGHT-WIDTH:0px;" height=96 alt=clip_image004 src="http://sqlblog.com/blogs/ben_nevarez/clip_image004_thumb_2C8D516D.jpg" width=270 border=0></A></P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>Now, see what happens if I disable the check constraint</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:green;FONT-FAMILY:'Courier New';mso-no-proof:yes;">-- disable constraint </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">alter</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">table</SPAN> albums <SPAN style="COLOR:blue;">nocheck</SPAN> <SPAN style="COLOR:blue;">constraint</SPAN> check_year </SPAN></P>
<P>This time running the last query uses a Table Scan operator again as the optimizer can not longer use the check constraint.</P>
<P class=MsoNormal>Finally, there are two choices to enable back the existing check constraint. If I ask SQL Server to enable the constraint and validate the existing data, the constraint will be enabled only if all the data complies with the check constraint definition. If I ask not to validate the existing data, the check constraint will be enabled but only the new records will be validated.</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>Of course the resulting execution plan of my query will be different depending on the selected choice. If the check constraint is enabled validating the existing data, running the same query again (and including the query hint) will use the Constant Scan operator. But enabling the constraint without validating the existing data will again result in a Table Scan even when no records are returned. Basically, in this last case the query optimizer can not trust the check constraint.</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal>You can test yourself the resulting execution plans of the query after enabling the check constraint with any of these two commands</P>
<P class=MsoNormal>&nbsp;</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:green;FONT-FAMILY:'Courier New';mso-no-proof:yes;">-- enable constraint validating existing data </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">alter</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">table</SPAN> albums <SPAN style="COLOR:blue;">with</SPAN> <SPAN style="COLOR:blue;">check</SPAN> <SPAN style="COLOR:blue;">check</SPAN> <SPAN style="COLOR:blue;">constraint</SPAN> check_year </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:green;FONT-FAMILY:'Courier New';mso-no-proof:yes;">-- enable constraint without validating existing data </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">alter</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">table</SPAN> albums <SPAN style="COLOR:blue;">with</SPAN> <SPAN style="COLOR:blue;">nocheck</SPAN> <SPAN style="COLOR:blue;">check</SPAN> <SPAN style="COLOR:blue;">constraint</SPAN> check_year </SPAN></P>
<P>By the way, you can use the following query to see if a check constraint is not trusted, that is, if it has not been verified for all the records on the table.</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">select</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> name<SPAN style="COLOR:gray;">,</SPAN> is_not_trusted <SPAN style="COLOR:blue;">from</SPAN> <SPAN style="COLOR:green;">sys</SPAN><SPAN style="COLOR:gray;">.</SPAN><SPAN style="COLOR:green;">check_constraints </SPAN></P>
<P></SPAN><B style="mso-bidi-font-weight:normal;">Contradicting Predicate </B></P>
<P>The second type of contradiction case is when the query itself contains a contradiction. Take a look at this query</P>
<P class=MsoNormal><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">select</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:gray;">*</SPAN> <SPAN style="COLOR:blue;">from</SPAN> albums </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">where</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> album_year <SPAN style="COLOR:gray;">&lt;</SPAN> 1970 <SPAN style="COLOR:gray;">and</SPAN> album_year <SPAN style="COLOR:gray;">&gt;</SPAN> 1980 </SPAN></P>
<P><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">option </SPAN><SPAN style="FONT-SIZE:10pt;COLOR:gray;FONT-FAMILY:'Courier New';mso-no-proof:yes;">(</SPAN><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">maxdop</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> 1<SPAN style="COLOR:gray;">) </SPAN></P>
<P></SPAN>Same as before the execution plan for this query will use a Table Scan when the query hint is not used even when it is so obvious that there is a contradiction and no records should be returned. </P>
<P class=MsoNormal>Once I add the query hint,&nbsp;the Table Scan again is gone and only a Constant Scan operator is used.</P><img src="http://sqlblog.com/aggbug.aspx?PostID=15733" width="1" height="1">Check ConstraintsContradiction DetectionOptimizationPerformanceQuery Optimizer