SQL Server新基数估量器

本系列属于 SQL Server性能优化案例分享 专题

    当你使用SQL Server 2014及以上版本并且数据库的兼容级别为120或以上时，可能会有一个比较奇怪的现象，原本在SQL 2008/2012上运行正常的数据库，可能因为迁移到SQL Server 2014版本，在新环境突然变慢了。

    一般来说，迁移/升级实例版本时，我们必不可少的工作有：备份、重建全库索引、全库更新统计信息。但是如果这些都做了还是没有效果，那么要考虑一下是否因为SQL Server 新基数估量器在捣鬼。这个新的估量器从2014引入，并且一直在后续版本中存在。

    另外还有一个现象，就是在Where条件有多个列需要筛选时，其统计信息可能会出现非预期情况。基于这些情况，我觉得有必要介绍一下新的基数估量器（Cardinality Estimator）

        

介绍

    统计信息是描述表/索引数据分布情况的系统元数据，通过统计信息，优化器得到了优化的大部分前提数据并进行执行计划的生成。为了得到高效的执行计划，优化器必须得到每一步操作符的预估信息，主要来自于统计信息。SQL Server从2005~2012版本一直使用1998年发布的基数预估模型。随着时代的发展，显然已经不够合理。所以从SQL 2014版本开始，微软引入了新的基数预估模型，但是从概念上来说，新旧模型是一样的。

    旧模型有四个主要的“假定”：

1. 均匀的：Uniformity，模型假定在缺少统计信息的时候，数据分布是均匀的。
2. 独立的：Independence，模型假定实体的属性是互相独立的，比如一个查询有多个来自于同一个表的不同列的筛选条件，那么这些列没有任何关系。这个很重要，本人最近优化的一个案例中就出现这类问题。
3. 简单包含：Simple Containment，模型嘉定用户查询的数据都存储在表中。比如在缺少统计信息的情况下，当你关联两个表时，模型假设一个表的不同值“都”存在于另外一个表中。
4. 包含：Inclusion，模型假设在将属性（列）与常量进行比较时, 始终存在匹配项。

    虽然在很多情况下，这些假设是可以得到可接受的结果。不过很显然这些假设不可能总是正确的。同时由于模型的重构难度很大，所以从SQL 2014开始，对模型进行了重新设计，注意两个模型是并存的，并非简单替代。新模型对假定进行了一些新的改进：

1. 相互关联：Correlation，其假定语句的谓词是相互关联的，这个更加符合现实环境。
2. 基本包含：Base Containment，假定用户可能所查询的数据并不存在于表中。除了联接谓词的选择性外, 它还将基表的直方图作为联接操作的因素。

    在SQL 2014~最新版本中（2014和2016又有稍微的区别），可以通过修改数据库的兼容级别来单独控制每个库所使用的模型，也可以使用跟踪标记来控制服务器、会话甚至语句级别的基数预估模型。执行计划具体使用的模型可以查看执行计划根操作符（图形中最左边的那个）的属性的“CardinalityEstimationModelVersion”值，图中120代表使用SQL 2014：

    新旧模型的关键区别在于如何处理多语句表值函数。旧模型总是预期函数返回一行数据，而新模型预期为100行。虽然两个模型都不合理，但是在大部分情况下，多语句表值函数返回100行会更加合理。

新旧模型对比

统计信息未过时

    首先创建测试环境，然后产生一下测试数据并创建聚集索引和非聚集索引：

create database test
GO
USE TEST  
GO
--创建表
create table dbo.Test 
( 
    ID int not null, 
    TestDate date not null, 
    TestName char(10) 
); 
--创建测试数据
;with N1(C) as (select 0 union all select 0) -- 2 行 
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 行 
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 行 
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 行 
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 行 
,IDs(ID) as (select row_number() over (order by (select null)) from N5) 
insert into dbo.Test(ID,TestDate) 
select ID,dateadd(day,abs(checksum(newid())) % 365,'2018-03-20') from IDs; 
--创建索引
create unique clustered index IDX_Test_ID on dbo.Test(ID); 
create nonclustered index IDX_Test_ADate on dbo.Test(TestDate); 

    然后使用DBCC SHOW_STATISTICS('dbo.Test',IDX_Test_ADate)来查看索引的统计信息，会看到大概如下内容，不过由于日期是随机生成，所以具体值不一定会一样：

    如图所示，表有65536行，然后以兼容级别分别为110（2012）、120（2014）和130（2016）来对比一下在查询条件属于直方图的值时模型的情况：

alter database test set compatibility_level =110--分别替换为120/130
GO
select id,TestDate,TestName
from dbo.test with (index=IDX_Test_ADate)
where TestDate='2018-03-26';

    三个结果一样，SQL Server使用直方图第四个值的EQ_ROWS列来估算影响行数。然后来试一下使用不在直方图里面的一个值'2018-03-21'（介于直方图第1、2行之间）来测试：

    三个结果也一样，他的估计行数为186，这个值来自于直方图第二行的AVG_RANGE_ROWS。

    最后试一下用参数化查询，使用本地变量来作为谓词，这个使用SQL Server会使用索引的平均选择度乘以索引键的唯一值总数来作为估计数量，即第一个图的前两个红框相乘：0.002739726×65536=179.550683136≈179.551

alter database test set compatibility_level =110
GO
declare @D date = '2018-06-07'; 
select id,TestDate,TestName
from dbo.test with (index=IDX_Test_ADate)
where TestDate=@D;
GO
alter database test set compatibility_level =120
GO
declare @D date = '2018-06-07'; 
select id,TestDate,TestName
from dbo.test with (index=IDX_Test_ADate)
where TestDate=@D;
GO
alter database test set compatibility_level =130
GO
declare @D date = '2018-06-07'; 
select id,TestDate,TestName
from dbo.test with (index=IDX_Test_ADate)
where TestDate=@D;

小结

    在统计信息未过时的情况下，三种模型的结果是一样的。

统计信息已过时

    在真实环境下，统计信息不过时是不现实的，那么下面来演示一下，在增加10%的数据量即6554行新数据的情况下统计信息的行为，为了避免系统自动更新，这里也要把自动更新选项关闭。因为在SQL 2016（兼容级别130）中会存在动态更新的特性。在演示完毕后，请重新开启自动更新！

alter database Test set auto_update_statistics off  --关闭自动更新
go 
;with N1(C) as (select 0 union all select 0) -- 2 行 
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 行 
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 行 
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 行 
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 行 
,IDs(ID) as (select row_number() over (order by (select null)) from N5) 
insert into dbo.Test(ID,TestDate) 
select ID + 65536,dateadd(day,abs(checksum(newid())) % 365,'2018-06-01') 
from IDs 
where ID <= 6554; 

接下来我们重复上面的三个语句测试，可以看到所有模型的估计行数为194.701行，比之前的值多10%。

    再次执行DBCC SHOW_STATISTICS('dbo.Test',IDX_Test_ADate)可以看到SQL Server调整第四行的EQ_ROWS作为实际行数：

    接下来在执行第二个语句，即不在直方图的日期，2018-03-21，可以发现兼容级别是110的，其估计数量跟120及以上的不一样：

    因为新模型（兼容级别120+）会把10%的差异计算进去，而兼容级别为110的依旧使用AVG_RANGE_ROWS。同样的情况也会发生在参数化语句中：

小结

    这种差异有好有不好，新模型对于新数据均匀分布在索引的情况中能够得到更好的结果，在本例中是因为日期随机生成，所以从统计学上来说是均匀分布。

    但是，在旧数据分布不变但是新数据不均匀分布情况下，旧模型会更加好，其中一个典型的例子是索引键为自增。

    因此，目前的正式版本中依旧保留了旧模型可用，只是默认使用新模型，除非你的兼容模式为120以下。记得恢复自动更新统计信息！

自增索引键

    接下来演示一下，当值不在直方图范围中的情况，这种情况通常发生在索引键为自增的情况下（比如用identity或者sequence列作为索引键）。这次使用表中另外一个索引IDX_Test_ID(聚集索引)，目前为止，索引统计信息并没有更新：

    同样按照三种兼容级别先查询一下“不在”直方图中的值，比如66000到67000：

alter database test set compatibility_level =110
GO
select top 10 ID, TestDate 
from dbo.Test 
where ID between 66000 and 67000 
order by TestName;
alter database test set compatibility_level =120
GO
select top 10 ID, TestDate 
from dbo.Test 
where ID between 66000 and 67000 
order by TestName;
alter database test set compatibility_level =130
GO
select top 10 ID, TestDate 
from dbo.Test 
where ID between 66000 and 67000 
order by TestName;

    可以看出，旧模型的估计行数只有1行，而新模型则基于索引的平均数据分布（66000~67000范围值为1001，注意between and是≥和≤）来预估。在这种索引自增情况下，新模型更加合适，至少避免了手动更新统计信息。

表关联

    接下来演示日常场景的另外一种使用场景，表关联情况，先创建另外一个关联表Test1，只有一个列ID，并与Test表有外键关联，然后把Test表的数据插入到这个Test1表（现在已经有72090行），并创建聚集索引在表上：

use test
GO
create table dbo.Test1
( 
    ID int not null 
     constraint FK_Test1_Test foreign key references dbo.Test(ID) 
); 
insert into dbo.Test1(ID) -- 72,090 行 
select ID from dbo.Test; 

create unique clustered index IDX_Test1_ID on dbo.Test1(ID); 

    第一步先关联并查询仅在Test1表中的数据，由于外键约束能确保Test1的每行数据都能与Test表的数据关联，所以SQL Server在这种情况下可以直接忽略关联，注意也要加上三种兼容级别：

alter database test set compatibility_level =110
GO
select d.ID 
from dbo.Test1 d join dbo.Test m on d.ID = m.ID 
GO
alter database test set compatibility_level =120
GO
select d.ID 
from dbo.Test1 d join dbo.Test m on d.ID = m.ID 
GO
alter database test set compatibility_level =130
GO
select d.ID 
from dbo.Test1 d join dbo.Test m on d.ID = m.ID 
GO

    读者可以自行检查，行数都是正确的。

多筛选条件

    前面提到，旧模式有一个主要假设就是独立性，而新模型移除了这种假设，它认为实体的属性之间存在某种层面上的关联。所以在WHERE条件出现多个列筛选时，表现的行为跟旧模型有所区别。下面对Test表进行演示三种模型下的情况：

alter database test set compatibility_level =110
GO  
select ID, TestDate 
from dbo.Test 
where  ID between 20000 and 30000 and  TestDate between '2018-03-01' and '2018-04-01'
GO
alter database test set compatibility_level =120
GO
select ID, TestDate 
from dbo.Test 
where  ID between 20000 and 30000 and  TestDate between '2018-03-01' and '2018-04-01'
GO
alter database test set compatibility_level =130
GO
select ID, TestDate 
from dbo.Test 
where  ID between 20000 and 30000 and  TestDate between '2018-03-01' and '2018-04-01'
GO

    这一次可以看到，估计行数差异就比较大了。新旧模型由于在是否独立的点上有出入，导致其计算公式也有不同：

• 旧模型：(第一个条件的选择度×第二个条件的选择度)×(表总行数)=（第一个条件的预估行数×第二个条件的预估行数）/（表总行数），多个条件以此类推。
• 新模型：（最佳选择度的条件的选择度）×平方根（次佳选择度条件的选择度）×（表总行数）

    两者都是“合理”的，对于多条件之间确实没有关联时，旧模型会更好，反之，新模型更佳。

模型选择

    前面演示了很多例子，从中可以发现新旧模型都有适用长期，那么如何选择模型来达到最佳的基数预估呢？通常情况下，新技术的出现是为了弥补甚至重构旧技术在某些方面的缺失，所以一般我们都会优先选择新技术。但是如果环境是从兼容级别较低（120以下）升级到120以上的情况，那么就要小心处理，因为很有可能会出现统计信息的差异导致性能突降。

总结

    本文演示了很多不同兼容级别（兼容级别导致了基数预估模型的不同）下新旧模型的差异。旧模型（兼容级别120以下）和新模型（120以上）在基数预估上具有很大差异。而SQL 2014（120）和SQL 2016（130）则差异不明显，130主要以增强为主，更好地应对自增索引和多列统计信息的环境。

    如果读者所使用的SQL Server是新项目，优先选择兼容级别120甚至130以上的版本，如果是120以下的环境，需要升级到以上兼容级别，那建议对核心功能进行测试。

    另外可以参考一下联机丛书的内容：高效使用统计信息的查询