SQL Server optimizer attributes-implicit predicates

As we all know, a SQL statement submitted to the optimizer produces a corresponding execution plan and then executes the output, but how does his execution plan come about? This may be the most complex part of a relational database. Here I'll introduce you to an attribute of the SQL Server optimizer-the implicit predicate, And a brief description of how the optimizer behaves based on the scene under this feature.

Here I pass a simple example to explain to you.

Code

CREATE TABLE INT INT )CREATETABLEintint)set on  SELECT*fromINNERjoinson=  T2. A

I can see that my statement execution plan in the optimizer added t2.a=0 to the predicate for me. 1-1

Figure 1-1

Based on the semantic logic, the optimizer thinks that filtering out the t2.a=0 result set in the T2 table in advance without changing the result set and participating in the following operation can improve efficiency so that he does so without our consent:)

This is an implicit predicate .

Since this is the default behavior of the SQL Server optimizer, there is a conflict with its default behavior when we want to control the optimizer behavior. Here is a simple example.

Code

Select @ @VERSION SELECT *  from Inner JOIN  on = T2. AWHERE=0

As you can see, I have added a hash join for SQL hint The result is an error. 1-2

Figure 1-2

cause: By default, the optimizer adds the t2.a=0,t1,t2 to us with the same filter condition, then t1.a=t2. A its own meaning does not exist, and the hash join itself needs the equivalent link (equijoin), at this time the error appears.

extension: In fact, in this case, select in the search together can be, throw errors to make developers depressed. Microsoft has noticed that this is no problem with the query in SQL2012.1-3

Figure 1-3

The problem is (not the excavator which is strong), if I use the hint this situation and how to do it? As we have just said, hash join requires an equivalent link, which avoids the problem caused by the implicit predicate characteristic of the table according to its definition.

The Where condition is replaced by not equal:)

Code

SELECT *  from Inner JOIN  on = T2. AWHERE>-1 and T1. A<=0

About Performance

It can be seen that before participating in the join operation, the optimizer filtered out some of the data for us, so that the consumption of the join is reduced, which is a good thing, but there are two aspects, for the filter out of this part of the data he is not free, and sometimes may aggravate the burden.

Here we introduce a trace flag 2324, which allows the optimizer to take no implicit predicate behavior, allowing our execution plan to perform better in special scenarios.

Here I pass a simple example to illustrate the next.

Code

Select *  fromAaaInner JoinBBB onAaa. ProductID=BBB. ProductIDwhereAaa. ProductID> +  andAaa. ProductID< theGoSelect *  fromAaaInner JoinBBB onAaa. ProductID=BBB. ProductIDwhereAaa. ProductID> +  andAaa. ProductID< theoption(Querytraceon2324)----Disable implicit predicates

You can see that because implicit predicates are skewed in a particular scene, such as data distribution. PRODUCTID=1001 Station in the example 80% data in the BBB table) the filtered join is not as fast as the overall data join join. 1-4

Figure 1-4

The example here is only for the simple explanation, the actual production may because the statistical information problem causes the optimizer to adopt the unreasonable operator (if uses seek in the BBB, then consumes the huge, but the statistic information itself is not easy to update) causes the execution plan unreasonable influence overall performance. Interested friends can test themselves.

Note: TF2324 only works on non-equivalent predicates. Equivalent predicates If you want to circumvent implicit predicates, refer to excavator examples:)

Conclusion: There are two sides to everything, implicit predicates are a good strategy in most scenarios, and Microsoft is perfecting it. But when the optimizer can't handle it properly, we need to intervene.

SQL Server optimizer attributes-implicit predicates