在SQL Server中加入包含假设和CE模型变化-CSDN博客

In this post we are going to talk about one of the model assumptions, that was changed in the new cardinality estimation mechanism in SQL Server 2014 – Join Containment Assumption.

在本文中，我们将讨论一种模型假设，该假设已在SQL Server 2014中的新基数估计机制–连接包含假设中进行了更改。

You may find some information about this assumption in the Ian Jose’s blog post: Query Processor Modelling Extensions in SQL Server 2005 SP1, there you may find the description of the so-called simple assumption and base assumption. Another source of available information is a white paper from Joseph Sack Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator.

您可以在Ian Jose的博客文章： SQL Server 2005 SP1中的查询处理器建模扩展中找到有关此假设的一些信息，在这里您可以找到所谓的简单假设和基本假设的描述。可用信息的另一个来源是Joseph Sack的白皮书，其中包括使用SQL Server 2014基数估计器优化查询计划。

Let’s take a look at the example in action. We’ll use the AdventureWorks2012 database under the compatibility level of SQL Server 2014 and three identical queries, that demand customers from the particular territory and their orders for particular dates. The only exception is that the first query uses TF 9481 that forces the old cardinality estimation behavior, the second also uses the old CE but with the Modeling Extensions enabled (as they were described in the Ian Jose’s blog post mentioned above), the third one query uses the new CE. Let’s run them and look at the estimates.

让我们看一下实际的例子。我们将在SQL Server 2014的兼容级别和三个相同的查询下使用AdventureWorks2012数据库，这三个查询要求特定地区的客户及其在特定日期的订单。唯一的例外是，第一个查询使用TF 9481强制执行旧的基数估计行为，第二个查询也使用旧的CE，但启用了Modeling Extensions（如上文提到的Ian Jose的博客文章所述），第三个查询查询使用新的CE。让我们运行它们并查看估计值。

alter database AdventureWorks2012 set compatibility_level = 120;
go
set statistics xml on
go
 
-- The Old Behavior
declare @a int, @b int;
select
	@a = soh.SalesOrderID,
	@b = c.CustomerID
from 
	Sales.SalesOrderHeader soh 
	join Sales.Customer c on soh.CustomerID = c.CustomerID
where
	soh.OrderDate between '20070101' and '20070201' and 
	c.TerritoryID = 10
option(querytraceon 9481)
go
 
-- The Old Behavior with TF 2301 that enables Modelling Extensions
declare @a int, @b int;
select
	@a = soh.SalesOrderID,
	@b = c.CustomerID
from 
	Sales.SalesOrderHeader soh 
	join Sales.Customer c on soh.CustomerID = c.CustomerID
where
	soh.OrderDate between '20070101' and '20070201' and 
	c.TerritoryID = 10
option(querytraceon 9481, querytraceon 2301)
go
 
-- The New Behavior
declare @a int, @b int;
select
	@a = soh.SalesOrderID,
	@b = c.CustomerID
from 
	Sales.SalesOrderHeader soh 
	join Sales.Customer c on soh.CustomerID = c.CustomerID
where
	soh.OrderDate between '20070101' and '20070201' and 
	c.TerritoryID = 10
go
 
set statistics xml off
go

The actual number of rows for the Join, for all the queries, is the same and equals 43, however, the estimates are different.

对于所有查询，Join的实际行数相同且等于43，但是估计值不同。

We see that the first query overestimated the number of rows more than 10 times! The other two are pretty close to the actual number of rows. There is a small difference between them, that is because some algorithm details have changed, however, they both use the same approach contradictory to the old model without any extensions. Of course, this query is too simple to result in different plans due to the different estimates, we’ll look at the more complex example later in this post. Now it is interesting to know – why does the first query overestimated the Join 10 times.

我们看到，第一个查询高估了行数10倍以上！另外两个非常接近实际的行数。它们之间的差异很小，这是因为某些算法细节已更改，但是，它们都使用与旧模型相反的相同方法，没有任何扩展。当然，由于估算值不同，此查询太简单了，无法得出不同的计划，我们将在本文后面的示例中介绍更复杂的示例。现在有趣的是，为什么第一个查询高估了Join十次。

加入简单和基本遏制假设 (Join Simple and Base Containment Assumptions)

In the example query, we have two tables, two filters on each table and the Join. As we remember, the cardinality estimation process goes from bottom to top, using the child operator statistics as an input, to estimate its cardinality.

在示例查询中，我们有两个表，每个表上有两个过滤器以及Join。我们记得，基数估计过程是从下到上，使用子运算符统计信息作为输入来估计其基数。

That means, that two tables are first filtered by TerrioryID and OrderDate, then the filter statistics collection is used by the join to estimate the join condition selectivity. We may depict this process using the following scheme:

这就是说，首先通过TerrioryID和OrderDate过滤两个表，然后联接使用过滤器统计信息集合来估计联接条件的选择性。我们可以使用以下方案描述此过程：

What does it mean, if we use filter statistics collection to estimate the Join predicate selectivity? That means, that join selectivity is dependant on the filters, that assumes some correlation between the join condition and the filtering predicates implying that these two filters are also correlated.

如果我们使用过滤器统计信息收集来估计Join谓词的选择性，那是什么意思？这意味着，连接选择性取决于过滤器，假定连接条件和过滤谓词之间存在某种相关性，这意味着这两个过滤器也相关。

Often this is the wrong assumption. Often the filters from the two tables are independent. That means, that join selectivity should be estimated independently from filters. To estimate this way, the base table statistics and cardinality should be used. That is what Join Base Containment assumption is.

通常这是错误的假设。通常，两个表中的过滤器是独立的。这意味着，连接选择性应独立于过滤器进行估算。要估计这种方式，应使用基表统计信息和基数。这就是“加入基本遏制”假设。

We may depict Join Base Containment Assumption as follows:

我们可以将联接基本遏制假设描述如下：

Of course, there are cases when the “simple assumption” is closer to the actual than the “base” one, but often the base assumption is closer to the truth. That is why Microsoft tells that there might be regressions, but you may expect the increased plan quality of the workload as a whole.

当然，在某些情况下，“简单假设”比“基本”假设更接近实际情况，但通常，基本假设更接近事实。这就是为什么Microsoft告诉您可能存在回归的原因，但您可能希望整个工作负载的计划质量有所提高。

更复杂的例子 (More Complex Example)

Let’s add another table to view the actual differences in the query plans. Suppose we want to know the order detail information also, and join SalesOrderDatail table for that purpose.

让我们添加另一个表来查看查询计划中的实际差异。假设我们还想知道订单详细信息，并为此目的加入SalesOrderDatail表。

set statistics xml on
go
declare @a int, @b int;
select
	@a = soh.SalesOrderID,
	@b = c.CustomerID
from 
	Sales.SalesOrderHeader soh 
	join Sales.Customer c on soh.CustomerID = c.CustomerID
	join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
where
	soh.OrderDate between '20070101' and '20070201' and 
	c.TerritoryID = 10
option(querytraceon 9481)
go
declare @a int, @b int;
select
	@a = soh.SalesOrderID,
	@b = c.CustomerID
from 
	Sales.SalesOrderHeader soh 
	join Sales.Customer c on soh.CustomerID = c.CustomerID
	join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
	
where
	soh.OrderDate between '20070101' and '20070201' and 
	c.TerritoryID = 10
go
set statistics xml off
go

The plans would be the following (the old one first, and the new one next).

计划将是以下计划（旧计划优先，然后新计划）。

The actual number of rows is 170 for the both queries, but the first one, using the old CE overestimated the Join and used inefficient join type that lead to Index Scan and 121 317 rows to be read. Of course it is a very small database to see the timing difference, but you can imagine what may happen in a real-word database.

两个查询的实际行数均为170，但是第一个使用旧CE的行高估了Join，并使用了效率低下的join类型，导致索引扫描和121 317行被读取。当然，这是一个非常小的数据库，可以看到时间差异，但是您可以想象一下，在实字数据库中可能会发生什么。

The old CE may produce the same plan as the new one if you use TF 2301 (mentioned above) to enable Join Base Containment Assumption, that is enabled in 2014 by default.

如果使用TF 2301（如上所述）来启用“加入基本容纳假设”，则旧的CE可能会产生与新CE相同的计划，该计划默认在2014年启用。

In the case when we query data filtered by territory and order date, indeed, these filters are hardly correlated. But, for the sake of truth, I should provide the opposite example. Let’s try to filter by the modified date. It is very likely, that when the order was placed on the table, in the same time, the order details were also saved. So filters are supposed to be correlated. I’ll also add a few more tables to demonstrate the difference, supposing we are looking for some special offers.

实际上，当我们查询按地区和订购日期过滤的数据时，这些过滤器几乎不相关。但是，为了真实起见，我应该提供相反的例子。让我们尝试按修改后的日期进行过滤。当订单被放置在桌子上时，很有可能同时保存了订单明细。因此，应该将过滤器关联起来。假设我们正在寻找一些特价商品，我还将添加更多表格来说明两者之间的区别。

select distinct Type into Sales.SpecialOfferType from Sales.SpecialOffer;
create clustered index cix_Type_TypeCategoryID on Sales.SpecialOfferType(Type);
go
set statistics xml on;
go
declare @a int, @b int, @c int;
select
	@a = soh.SalesOrderID,
	@b = sod.SalesOrderDetailID
from 
	Sales.SalesOrderHeader soh 
	join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
where
	soh.ModifiedDate between '20070101' and '20070115' and
	sod.ModifiedDate between '20070101' and '20070115' and
	exists (select * from Sales.SpecialOffer so join Sales.SpecialOfferType sot on so.Type = sot.Type where so.SpecialOfferID = sod.SpecialOfferID and so.Category = 'Reseller')
option(querytraceon 9481)
go
declare @a int, @b int, @c int;
select
	@a = soh.SalesOrderID,
	@b = sod.SalesOrderDetailID
from 
	Sales.SalesOrderHeader soh 
	join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
where
	soh.ModifiedDate between '20070101' and '20070115' and 
	sod.ModifiedDate between '20070101' and '20070115' and
	exists (select * from Sales.SpecialOffer so join Sales.SpecialOfferType sot on so.Type = sot.Type where so.SpecialOfferID = sod.SpecialOfferID and so.Category = 'Reseller')
go
set statistics xml off;

The plans would be the following (the old one and next the new one).

计划将是以下计划（旧计划和新计划）。

It may be a point of doubt which plan is more efficient, because the first one (using the old CE) is still overestimating the Join, but the second one, this time, underestimated the Join row count. The estimated number of rows is 6 while the actual is 20. The numbers are small as the database is small, but even here, the second plan uses the Spool to optimize rewinds (and I particularly don’t like the plans with a spool and underestimated rewinds – 5 estimated vs. 1000 actual).

怀疑哪个计划更有效，因为第一个计划（使用旧的CE）仍然高估了Join，但是第二个计划这次低估了Join行数。估计的行数为6，而实际的行数为20。由于数据库很小，行数很小，但是即使在这里，第二个计划也使用Spool来优化倒带（而且我特别不喜欢带有spool和被低估的倒带–估计为5，而实际为1000）。

What is worth remembering that the estimation algorithms are still based on the statistics, mathematical model, assumptions, and guesses, i.e. have statistical nature. The new model was modified to reflect the modern workload types and use the accumulated experience, so you may expect improvements, however, don’t expect miracles.

值得记住的是，估计算法仍基于统计信息，数学模型，假设和猜测，即具有统计性质。对新模型进行了修改，以反映现代工作负载类型并利用累积的经验，因此您可能会期望有所改善，但是不要期望奇迹。

模型变化 (The Model Variation)

Join Base Containment Assumption is used by default in the new CE framework, however, it is still possible to use Join Simple Containment Assumption and child operator input statistics (not base table statistics) for an estimation. To do it, you should run the query with the TF 9476.

在新的CE框架中，默认情况下使用Join基本包含假设，但是，仍然可以使用Join Simple Containment Asumption和子运算符输入统计信息（而非基本表统计信息）进行估算。为此，您应该使用TF 9476运行查询。

Let’s run the previous query with this flag and gather the compiled plan:

让我们使用该标志运行上一个查询，并收集已编译的计划：

declare @a int, @b int, @c int;
select
	@a = soh.SalesOrderID,
	@b = sod.SalesOrderDetailID
from 
	Sales.SalesOrderHeader soh 
	join Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
where
	soh.ModifiedDate between '20070101' and '20070115' and
	sod.ModifiedDate between '20070101' and '20070115' and
	exists (select * from Sales.SpecialOffer so join Sales.SpecialOfferType sot on so.Type = sot.Type where so.SpecialOfferID = sod.SpecialOfferID and so.Category = 'Reseller')
option(querytraceon 9476)

With this TF enabled, the estimate is pretty close to the old CE estimated 140 rows, and we have the same query plan:

启用此TF后，估算值非常接近旧的CE估算的140行，并且我们有相同的查询计划：

Warning: Again, I should frighten each and everyone – this is a complete undocumented stuff, use it for the exploration in the test environments only, unless you are guided by official Microsoft support. All the information provided just for curiosity and is of my own, no warranty expected!

警告：同样，我应该吓each每个人–这是一个完整的未记载的内容，除非您有Microsoft官方支持，否则请仅将其用于测试环境中。提供的所有信息仅供参考，仅供我参考，概不保证！

It is interesting to run the query with this trace flag combined with the flag for diagnostic output TF 2363, mentioned earlier. However, this query uses calculator CSelCalcExpressionComparedToExpression, which does not output the desired details, so we will run another query in a simple synthetic database opt, that I often use in the exploration and demonstration process. The query will use calculator CSelCalcSimpleJoinWithDistinctCounts, that shows the stuff of interest.

将此跟踪标志与前面提到的诊断输出TF 2363的标志结合使用来运行查询很有趣。但是，该查询使用计算器CSelCalcExpressionComparedToExpression ，该计算器不会输出所需的详细信息，因此我们将在一个简单的合成数据库opt中运行另一个查询，该数据库我经常在探索和演示过程中使用。该查询将使用计算器CSelCalcSimpleJoinWithDistinctCounts ，该计算器显示感兴趣的东西。

use opt;
go
-- using base cardinality
declare @v1 int = 10, @v2 int = 10, @v3 int = 100;
select * from t1 join t2 on t1.c = t2.d
where
	t1.b < @v1 and
	t2.c between @v2 and @v3
option(
	querytraceon 3604, 
	querytraceon 2363
 )
 go
 -- using input cardinality
 declare @v1 int = 10, @v2 int = 10, @v3 int = 100;
select * from t1 join t2 on t1.c = t2.d
where
	t1.b < @v1 and
	t2.c between @v2 and @v3
option(
	querytraceon 9476,
	querytraceon 3604, 
	querytraceon 2363
 )
go

Let’s switch to the Message tab. If you inspect the CSelCalcSimpleJoinWithDistinctCounts output, among the other information you’ll see the following description.

让我们切换到“消息”选项卡。如果您检查CSelCalcSimpleJoinWithDistinctCounts输出，以及其他信息，您将看到以下描述。

The first one (default):

第一个（默认）：