全废话SQL Server统计信息（2）——统计信息基础

接上文：http://blog.csdn.net/dba_huangzj/article/details/52835958

我想在大地上画满窗子，让所有习惯黑暗的眼睛都习惯光明——顾城《我是一个任性的孩子》

这一节主要介绍一些理论层面的东西，主要针对SQL Server，为后面的做铺垫，如果从实操层面考虑可以跳过，但是我强烈建议还是要找时间看一下这节。本节的内容如下：

1. SQL Server统计信息
2. 列级统计信息
3. 统计信息与执行计划
4. 统计信息与内存分配
5. 开销预估模型

SQL Server统计信息

说到统计信息，就一定要提到查询优化器，主流关系型数据库管理系统的查询优化器都是基于开销的优化（cost-based optimizer, CBO）,而优化器是生成执行计划的组件，所以执行计划的质量直接依赖于开销预估的准确性，同样，执行计划的预估开销又基于算法/操作符的使用和基数预估。所以，为了让优化器得到准确的预估开销，优化器需要尽可能准确地预估制定查询要返回的记录数。

在查询被优化的过程中，SQLServer会分析很多候选执行计划，并预估它们的相对开销，然后选择最高效的那个执行计划。因此，不准确的基数和开销预估会引起优化器选择不高效的执行计划从而影响数据库性能。

这里提到的开销、基数等概念，在用户层面看来，就是统计信息，或者说，对于用户来说，这些信息中可控部分主要是统计信息。

SQL Server的统计信息包含三个主要部分：直方图（histogram）、密度信息（density information）和字符串统计信息（string statistics），这三个部分在基数预估过程中分别协助不同的部分。

提醒：SQL Server在统计信息中存储了一个额外的针对字符串值的信息，称为Trie Trees（直译叫字典树或前缀树），这个信息可以针对字符串键值提供更好基数预估。但是这部分属于“未公开功能”，所以不在这里介绍。

SQL Server 创建和维护统计信息，通过提供基数预估帮助优化器分析。而基数预估是对一个查询，“假设”使用了某些筛选条件、JOIN联接或GROUP BY 操作之后，会返回的记录数。而另一个常见术语选择度（Selectivity）的概念和基数预估很类似，它计算满足谓词的行在表中的百分比，选择度越高，返回的结果越小。提醒一下，选择度是索引键值选择的重要指标之一。

最后，我们来回答一下一个一直没有正式回答的问题：为什么我们需要统计信息？答案其实很简单，但是可能需要有过一定的经历，才会深有体会，这个答案就是统计信息降低了在优化过程中必须分析的数据量，如果优化器每次优化都要访问实体表/索引的话，分析过程会变得非常低效。所以优化器会使用实际数据的样本（也就是统计信息）来做分析，统计信息的量通常来说会远低于原数据，所以分析和生成执行计划的速度会快得多。但是正如我一直在很多文章中说到的一样，没有什么功能是绝对的好或者绝对的坏，统计信息也有缺点，这个缺点就是维护成本，对于大型数据库的统计信息创建和维护（实时更新）会消耗很多资源和时间，另外由于统计信息是数据表/索引的取样结果，所以对于超大型的表来说，准确程度不可能太高。

统计信息的样子：

下面我们来看看上一节创建的演示库中统计信息的样子，先用以下脚本创建数据库环境：

use StatisticsTest;
go
-- Create a test table
if (object_id('T0', 'U') is not null)
  drop table T0;
go
create table T0(c1 int not null, c2 nchar(200) not null default '#')
go
-- Insert 100000 rows. All rows contain the value 1000 for column c1
insert T0(c1)
select 1000 from Numbers
where n <= 100000
go
-- Now insert only one row with value 2000
insert T0(c1) values(2000)
go
--create a nonclustered index on column c1
create nonclustered index ix_T0_1 on T0(c1) 

首先看看图形化的统计信息，我们可以在SSMS的这个地方找到统计信息：

在环境创建完之后，可以发现统计信息这个文件夹下面是没有东西的，因为表没有“被使用”，所以优化器不会对这个表创建任何统计信息。但是当第一次使用或者创建索引（实际上也是对数据进行使用）时，就会创建统计信息，我们可以尝试两个操作，第一个是执行一个简单的SELECT语句，优化器会对上面用到的列创建统计信息：

需要注意要带上WHERE条件，其中竖框部分的1代表表创建时的第一列也就是x，而_WA_Sys代表由SQL Server自动创建的统计信息，WA传说是SQL Server开发组所在地华盛顿（Washington）的缩写。

下面再来创建一个索引，即前面脚本中注释掉的那段：

可以看到又多了一个统计信息，并且这个统计信息是和索引名一样，这个可以说是SQL Server自己创建的（因为你没有显式编写命令单独创建统计信息），也可以说是用户操作导致的。为了和前面_WA这个做区别，我们通常把它定义为非SQL Server自动创建的统计信息。

SQL Server统计信息元数据

下面我们来看看如何查询统计信息，统计信息是独立于实体表/索引的实际存储的信息，我们可以从一些元数据中获取它们。SQL Server 2005开始引入了目录视图、动态管理对象（DMO）等替代2000时代的系统表，减少对系统表的误操作所带来的系统故障，同时这些视图也添加了很多详细信息供后续使用。关于统计信息，我们首先用到的目录视图是：sys.stats，注意这部分的元数据存储在对应的数据库中，所以也需要切换到对应的数据库下执行：

use StatisticsTest
GO
SELECT *
FROM sys.stats
WHERE object_id = object_id('dbo.T1')

可以看到每一个统计信息都单独存在一行中，可以使用DBCC SHOW_STATISTICS命令来对某个统计信息进行详细展示：

如果要查询的统计信息不存在（或者拼错），会得到以下错误：

消息 2767，级别16，状态 1，第 8 行

无法在系统目录中找到统计信息 'a'。

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

在这里是因为我们从创建开始就没有使用过a这个列，所以只要我们执行一个使用到它的语句，然后就可以查询：

回到DBCC SHOW_STATISTICS命令得到的结果，前面提到了统计信息主要有三个部分，从上图看到也确实有三部分，这三部分分别叫做头信息、密度信息和直方图。

头信息：

下面来看看每个列的简要说明：

• Name：_WA_Sys_00000002_108B795B，这是统计信息的名字，所以自动创建的统计信息都以_WA_Sys开头，跟着是一个值，标识为统计信息是基于哪一列创建的，注意自动创建的统计信息只会在单列上，对于多列组合的统计信息必须手动创建，同时这个列顺序是表创建时候的顺序，可以通过sys.columns目录视图查看，接下来是一个十六进制的值，代表表的object_id，可以用Windows自带的计算器反计算。然后使用Object_Name()函数得出表名：

                       

• Updated：09 17 2016  5:13PM 这个值是统计信息创建或最后一次更新的时间。
• Rows：100000，表示统计信息创建或最后一次更新的时间。
• Rows Sampled：100000，表示统计信息创建或最近一次更新时的取样行数。
• Steps：109，直方图的步数，接下来会介绍。
• Density：0.03002139，密度值，这个值在新版（最晚在SQL 2008开始）SQL Server中仅用于向后兼容，对优化器没有用处。
• Average key length：3.62263，统计信息所针对的列的平均字节数。这个值可以通过这样计算出来，虽然没有多大研究价值：

即把列中每行的字节数（注意datalength函数返回字节数，len()函数返回字符数）加起来再除以总行数即可。

• String Index：YES，这个值表示统计信息是否包含字符串信息，只有YES或者NO可选。这个值可以对LIKE条件提供预估支持，并且字符串统计信息只对第一列创建且必须为字符串类型（单列、多列统计信息都一样），由于这里的统计信息是建在A列，而这列是字符串类型，所以这里的值为YES。
• FilterExpression和UnfilteredRows：这两个值只在统计信息创建在过滤索引（filter index）上才出现非null的值，后面会介绍。

密度信息：

本例中比较简单，是单列统计信息，所以密度信息这部分比较少，后面会演示多列统计信息，从名字来看，就三列：

• All density：0.0003333333，它是对于这列（多列的先不管），1/唯一值的个数。

• Average Length：3.62263，表示唯一值的平均长度。
• Columns：a ，明显代表这个密度是包含哪些列，不罗嗦。

那么这部分内容有啥用呢？大部分情况下，这部分的信息可以对语句中的GROUP BY 和ON条件中的未知值（比如本地变量）提供信息给优化器。再次看看这个表的统计信息情况：

DBCC SHOW_STATISTICS('Sales.SalesOrderDetail', IX_SalesOrderDetail_ProductID)

这里表示了这个索引包含了3列，每种组合情况下的密度及平均长度信息。下面来看看这个语句：

USE AdventureWorks2014
GO

SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID

优化器在编译这个语句时，由于ProductID列上有统计信息，不需要遍历整表，直接从密度信息中就可以获取唯一值（Group By本质就是去重）的预估行数。

这个值，根据定义，是1/对应的密度，由于只计算ProductID，所以密度就是

结果大家可以自行算一下，是265.99996。大家也可以自己算一下GROUP BY ProductID,SalesOrderID及SalesOrderDetailID，也就是上面的另外两行。

那么在使用本地变量的情况下会如何 ？

USE AdventureWorks2014
GO

DECLARE @ProductID INT

SET @ProductID = 921

SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE ProductID = @ProductID

由于本地变量在统计信息的行为上有点特殊，所以这里要特意拿来说一下，优化器在最终实际运行之前没办法知道提交的SQL语句中参数会是什么值，但是它又必须产生一个预估执行计划，同时接下来会解释，此时也没办法使用直方图，所以优化器只能借助密度信息来获取预估行数。此时的预估行数由表总数乘以密度信息得出。也就是：0.003759399 * 121317≈456.079。

另外一个情况下，WHERE条件不是使用“等于”符号，而是“不等于”，

这个时候，参数的具体值已经没所谓了，大家可以试一下随便填一个值。此时优化器连密度信息都不能用了，但是又必须给点东西，怎么办？使用一个标准假设（表总数的30%作为选择度），也就是说在这种不等操作符中，预估行数总是表总数的30%，即121317*30%≈36395.1。这个30%的值有时候却会带来很大的性能影响，特别是对超大表，30%的量还是非常大的，但是可能实际上语句只需要极其少量的数据，基于这个原因，在查询中，尽可能避免本地变量，而使用参数化或者字符串形式。因为此时优化器可以使用直方图来协助预估，从而提供更准确的预估行数。

直方图：

在SQL Server中，只会对统计对象的第一列创建直方图，并且压缩在这些列中的分布值的信息到一系列子集中，这些子集称为桶（buckets）或者步（steps）。为了创建直方图，SQL Server需要先在首列中查找唯一值，并且使用最大偏差算法尝试获取那些最常用的变化值，以便把最显著的统计信息保存下来。最大偏差是其中一个用于精确地表达关系数据库中数据分布情况的算法。

在SQL Server中，我们可以使用DBCC SHOW_STATISTICS命令查看直方图，比如在微软示例数据库AdventureWorks2014中显示Sales.SalesOrderDetail表的IX_SalesOrderDetail_ProductID索引的当前统计信息：

USE AdventureWorks2014
GO
DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail',IX_SalesOrderDetail_ProductID)

前面提到，直方图只对统计对象的首列进行创建，所以在这个统计信息中，只对ProductID列创建统计信息。下面来看看这个语句产生的关于直方图方面的结果：

• RANGE_HI_KEY：直方图中每一步的最高边界（上限值），比如上图第21行对应的738，和第22行对应的741，这两行意味着第22行中的统计信息边界从739~741。
• RANGE_ROWS：是一个预估行数，表示不包含上限值在内的该步长中的行数。在截图中，也就是意味着第22行的值应该是关于739~740的行数。
• EQ_ROWS：同样是一个预估值，预估在这列的对应步长中，跟RANGE_HI_KEY相同的值有多少，拿22行做例子：最高值是741，然后直方图预估等于741这个值的数有97个。
• DISTINCT_RANGE_ROWS：在该步长范围内，除去上限值的其他值中，唯一值的数量。
• AVG_RANGE_ROWS：该步长中，每个非上限值的唯一值的平均行数，计算公式为：RANGE_ROWS/DISTINCT_RANGE_ROWS。

下面来大概计算一下截图中的值怎么来的：

我们针对图中第22行，使用下面语句可以得出：

• RANGE_HI_KEY：由SQL Server自动标注，我们干预不了，
• RANGE_ROWS：表示了不包含上限值的行数，即ProductID=739的行数，这里算出是167，跟直方图的RANGE_ROWS相同。
• EQ_ROWS：上限值，即741的行数，这里很显然是94行。
• DISTINCT_RANGE_ROWS：这个步长有两个唯一值，741和739，出去上限741之外，只有739这个唯一值，所以对应的值为1。
• AVG_RANGE_ROWS：RANGE_ROWS/DISTINCT_RANGE_ROWS=167/1=167。

通过定义，我们把直方图中的值都计算出来了，大家可以用这种方法去验证其他值。

所谓知其然知其所以然，除了知道解释之外，更重要的是知道为什么要计算这些值，存在必然有意义，我们大部分情况下都不需要去研究这些意义，但是一旦出现一些深入问题或者你想做深入研究的时候，那就成了必要了。下面我们来看个很普通的语句：

USE AdventureWorks2014
GO

SELECT *
FROM sales.SalesOrderDetail
WHERE ProductID = 741

然后看看实际执行计划：

这里的预估行数为94，由于这个例子是ProductID=741，所以优化器直接使用直方图中RANGE_HI_KEY的值来替代，刚好是94。然后把741换成该步长中的另外一个值739，看看对应的部分：

由于739不是这个步长的上限值，所以不能使用RANGE_HI_KEY来直接获取，所以会使用AVG_RANGE_ROWS。

下面来一个不等于的操作：

这个13223怎么来的？累加！，把直方图中，RANGE_HI_KEY小于714（注意不是等于）的EQ_ROWS加起来，大家可以自己算一下。

下面把例子扩展一下，把WHERE条件加一个AND谓词：

这个语句实际上使用了两个统计信息，前面提到过00000004代表表定义的第四列。SQL Server使用对应的统计信息对象来分别评估两个条件。得出对应的预估值之后，取两者交集作为最终的预估行数。所以这个过程需要乘以两个的选择度。

大家可以自己尝试，ProductID=870的值有4688行，对于这个有121317行的表，对应的选择度为4688/121317，也就是大概0.0386425645。同理，OrderQty=1的有74954行（不同版本的AdventureWorks库行数可能不一样，所以最好自己算一次），对应的选择度为0.6178359175。

为了获取交集，需要把这两个选择度相乘：0.0386425645*0.6178359175≈0.02387476429241042875。最后，用这个值乘以表的总数，得出最终的预估影响行数：0.02387476429241042875*121317≈2896.41。但是图中的数据是2656.97，因为默认情况下数据库是启用自动创建统计信息功能的。在执行语句的时候，优化器会自己创建统计信息（在OrderQty列上），而由于这个列的统计信息取样不准确（没索引），所以会出现跟计算不一样的情况。如果你用一个原始库还原，然后先关闭自动创建统计信息功能再执行，可能会出现：

当你启用再执行的时候，数值可能变成了：

但是从技术上来说，预估的计算公式就是上面提到的。下面来更加复杂化一点：OR

上面的语句就是取两个条件的预估行数，并且除去重复值的并集。

在我这个库中，ProductID=870的有4688行，OrderQty=1 的有行。前一个例子我们已经算出AND的结果（也就是重复值）为2896.41 ，那么OR的结果就是4688+74954-2896.41=76745.59，跟图上的差异比较大，但是还是前面的原因，自动创建的统计信息不准确。其实只要我们建一个索引再检查，可以发现就是我们算出来的数值：

use AdventureWorks2014
go
create index IX_SalesOrderDetail_OrderQty on
Sales.SalesOrderDetail(OrderQty)

为避免后续影响，尽可能保持原有数据库状态，把索引删掉：

use AdventureWorks2014
go
drop index IX_SalesOrderDetail_OrderQty on
Sales.SalesOrderDetail
 

列级统计信息

我们一般讨论的统计信息，有两种：1.索引上的统计信息。2. SQL Server自动创建或人工创建的统计信息。

这里讨论的列级统计信息算是第二种，而且多为SQL Server自动创建。下面在AdventureWorks2014库中演示一下，先创建一个Customers表：

USE AdventureWorks2014
GO
--建表
CREATE TABLE dbo.Customers(
    CustomerId INT NOT NULL identity(1, 1),
    FirstName NVARCHAR(64) NOT NULL,
    LastName NVARCHAR(128) NOT NULL,
    Phone VARCHAR(32) NULL,
    Placeholder CHAR(200) NULL
    );

CREATE UNIQUE CLUSTERED INDEX IDX_Customers_CustomerId ON dbo.Customers(CustomerId)
GO

-- 通过Crossjoin产生测试数据
-- 使用GO50循环50次
    ;

WITH FirstNames(FirstName)
AS (
    SELECT Names.NAME
    FROM (
        VALUES ('Andrew'),('Andy'),('Anton'),('Ashley'),
            ('Boris'),('Brian'),
            ('Cristopher'),('Cathy'),
            ('Daniel'),('Donny'),
            ('Edward'),('Eddy'),('Emy'),
            ('Frank'),('George'),
            ('Harry'),('Henry'),
            ('Ida'),('John'),
            ('Jimmy'),('Jenny'),
            ('Jack'),('Kathy'),
            ('Kim'),('Larry'),
            ('Mary'),('Max'),
            ('Nancy'),('Olivia'),
            ('Olga'),('Peter'),
            ('Patrick'),('Robert'),
            ('Ron'),('Steve'),
            ('Shawn'),('Tom'),
            ('Timothy'),('Uri'),('Vincent')
        ) Names(NAME)
    ),
LastNames(LastName)
AS (
    SELECT Names.NAME
    FROM (
        VALUES ('Smith'),('Johnson'),('Williams'),('Jones'),
            ('Brown'),('Davis'),('Miller'),('Wilson'),
            ('Moore'),('Taylor'),('Anderson'),
            ('Jackson'),('White'),('Harris')
        ) Names(NAME)
    )
--插入数据
INSERT INTO dbo.Customers(
    LastName,
    FirstName
    )
SELECT LastName,
    FirstName
FROM FirstNames
CROSS JOIN LastNames
GO 50 --循环50次

--额外插入一行数据
INSERT INTO dbo.Customers(
    LastName,
    FirstName
    )
VALUES ('Isakov','Victor' )
GO
--创建非聚集索引
CREATE NONCLUSTERED INDEXIDX_Customers_LastName_FirstName ON dbo.Customers(
    LastName,
    FirstName
    );

注意索引的定义顺序，LastName是首列，下面使用一个非SARG写法：

对于第一个语句，SQLServer使用了聚集索引扫描，返回700行数据，第二个语句使用了非聚集索引扫描，返回1行数据。接下来检查一下统计信息：

select  stats_id, name, auto_created
from sys.stats
where object_id= object_id(N'dbo.Customers')

前两行是聚集索引和非聚集索引创建时SQL Server自动创建的统计信息。而第三个以“_WA”为前缀的，就是列级统计信息，由SQL Server在语句优化过程中自动创建的。然后再细化检查一下这个统计信息的情况：

从数据可以看出，它是基于FirstName创建的统计信息，因为在语句优化过程中发现FirstName没有统计信息支持，所以默认情况下由SQL Server自动创建。有了这个统计信息，优化器就可以对第二个语句进行优化，从而得出了不同的执行计划。

除了由SQL Server自己创建（自动创建只基于单列），还可以使用CREATE STATISTICS命令手动创建基于单列或多列（多列必须手动创建）的统计信息。

但是统计信息天生带有维护开销，虽然相对于索引带来的统计信息而言，通常列级统计信息会小很多，但是在频繁更新的情况下，还是会带来客观的开销，所以对于很少执行的语句，这种统计信息的创建会比额外创建索引更加好，毕竟索引在主键更新时也会*更新。相反，对于使用极其频繁的语句，创建索引会比创建列级统计信息对优化器的支持更好。因为索引不仅有预估信息，还能对数据进行组织和快速定位。

统计信息与执行计划

前面大量的例子说明，统计信息影响了优化器的语句优化，而优化器语句优化的产物就是预估执行计划，预估执行计划又直接影响了语句的运行性能，所以体现出统计信息影响了预估执行计划从而影响语句性能。

默认情况下，SQL Server自动创建和更新统计信息。在数据库级别有两个选项可以控制这个行为：

1. 自动创建统计信息：控制SQL Server是否自动创建列级统计信息。这个选项不影响索引自带的统计信息，因为索引总是带有统计信息（可以使用STATISTICS_NORECOMPUTE选项来关闭索引上统计信息是否自动创建）。默认情况下这个选项是开启的。
2. 自动更新统计信息：当SQL Server在编译或执行查询时发现统计信息过时时，这个选项用于控制是否随之更新统计信息，默认也是开启的。

是否创建统计信息很好理解，那么是否更新如何判断呢？SQL Server会基于统计信息列上的INSERT/UPDATE/DELETE/MERGE语句的影响行数来判断，技术上成为统计信息更新阈值（statistics update thresholds），有时也叫做统计信息重编译阈值（statistics recompilation thresholds），判断条件如下：

1. 当表为空时，一旦插入数据即认为统计信息过时。
2. 当表小于500行时，在统计信息列的每500次变更时认为过时。注意是500次，不是500行，比如对同一行数据更新100次，会认为是100次变更而不是1次。
3. 当表多余500行数据时，统计信息列后续每500次更新并且影响表上总行数的20%时，被认为过时。

这三个条件得出一个结论：对于越大的表而言，统计信息的自动更新频率越低。比如对于10亿行数据，要到2亿次更新后才认为统计信息过时从而触发统计信息更新。这部分在后续演示。

统计信息与内存分配

关系数据库在运行时候都严重依赖于内存，不同的操作符需要不同的内存资源。比如，索引扫描操作符需要一行接一行地提取数据，并不需要在内存中存储多行数据。而其他某些操作如排序，需要在进行排序前访问整个结果集，所以需要在内存中尽可能保留整个数据集。

但是SQL Server并不会随意分配内存，它会尝试评估某个查询及其中基于预估行数的操作符所需内存（memory grant）。简单来说就是执行某个查询，SQL Server会预估每个部分所需的内存。这一步很重要，过高或过低的评估都会对整体性能带来严重影响。多高会浪费SQL Server的内存，并且在负荷很大的服务器中，分配大量内存也会相对久很多。

评估多低，会导致实际执行过程中，某些操作符因为内存不足而失败或等待。比如排序操作，当没有得到足够的内存时，SQL Server会把溢出的结果集放到TempDB中进行排序。虽然TempDB也是在没办法的情况下才使用，但是相对于在内存中的性能，TempDB通常会明显低效很多。下面来演示一下：

USE AdventureWorks2014
GO
CREATE TABLE dbo.MemoryGrantDemo(
    ID INT NOT NULL,
    Col INT NOT NULL,
    Placeholder CHAR(8000)
    );

CREATE UNIQUE CLUSTERED INDEX IDX_MemoryGrantDemo_ID ONdbo.MemoryGrantDemo(ID);
--创建一个MemoryGrantDemo表，然后产生65536行数据，其中Col列存储0~99的值，大概每655或656行一个值。
;WITH N1(C)
AS (
    SELECT 0

    UNION ALL

    SELECT 0
    ) -- 2 rows
    ,
N2(C)
AS (
    SELECT 0
    FROM N1 AS T1
    CROSS JOIN N1 AS T2
    ) -- 4 rows
    ,
N3(C)
AS (
    SELECT 0
    FROM N2 AS T1
    CROSS JOIN N2 AS T2
    ) -- 16 rows
    ,
N4(C)
AS (
    SELECT 0
    FROM N3 AS T1
    CROSS JOIN N3 AS T2
    ) -- 256 rows
    ,
N5(C)
AS (
    SELECT 0
    FROM N4 AS T1
    CROSS JOIN N4 AS T2
    ) -- 65,536 rows
    ,
IDs(ID)
AS (
    SELECT row_number() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            )
    FROM N5
    )
INSERT INTO dbo.MemoryGrantDemo(
    ID,
    Col,
    Placeholder
    )
SELECT ID,
    ID % 100,
    convert(CHAR(100), ID)
FROM IDs;
--创建一个在Col列上的非聚集索引
CREATE NONCLUSTERED INDEXIDX_MemoryGrantDemo_Col ON dbo.MemoryGrantDemo(Col);
 

接下来添加656行新数据，并且指定Col为1000，这个数量是表总数的1%，根据前面说的统计信息更新阈值来说，是不被认为过时的。因此不更新统计信息，从而直方图也不会有Col=1000的这个值的数据分布情况：

USE AdventureWorks2014
GO
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 CROSS JOIN N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 CROSS JOIN N2 as T2) -- 1,024 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N5)
insert into dbo.MemoryGrantDemo(ID,Col,Placeholder)
        select 100000 + ID, 1000, convert(char(100),ID)
        from IDs
        where ID <= 656;

产生了数据之后，下面来测试一下运行情况，分别对Col列为1和1000的查询并排序：

USE AdventureWorks2014
GO

DECLARE @Dummy INT

SET STATISTICS TIME ON

SELECT @Dummy = ID
FROM dbo.MemoryGrantDemo
WHERE Col = 1
ORDER BY Placeholder

SELECT @Dummy = ID
FROM dbo.MemoryGrantDemo
WHERE Col = 1000
ORDER BY Placeholder

SET STATISTICS TIME OFF

执行计划概览：

Col=1000的实际及估计行数、内存分配情况：

虽然从执行计划的形状看上去很相似，但是对比基数预估和内存分配就可以知道差异很明显，另外一个就是对于第二个语句，在排序操作符（Sort）右下角有个黄色感叹号。鼠标移到上面就可以看到：

最后，对比一下执行时间，可以看到Col=1000的比Col=1的大很多，一般来说表示性能较差：

如果你使用Profiler等工具跟踪的话，可以使用Sort Warning和Hash Warning事件。

开销预估模型

前面一直提到，关系数据库都是基于开销的优化，那开销是如何来的？下面简要介绍一下，但是毕竟SQL Server不是开源的，很多信息属于机密，不公开，所以只能从有限的资料上找。

优化器产生执行计划的执行域开销预估的准确程度有“直接的关系”。优化器在优化语句的过程中，优化器会分析很多候选执行计划，预估它们各自开销，然后选择最佳的那个作为预估执行计划。所以，不仅基数预估要准确，开销预估也要高效。

开销是对部分或完整的执行计划的预估，计算每个操作符的开销值，然后把执行计划中的所有操作符的开销加起来作为整体开销。每个操作符的开销依赖于自身算法复杂度和影响行数，有些比如排序操作符，还与服务器的内存情况有关。

从能获得的信息来看，每个操作符会关联一个CPU开销，还会可能有一些I/O开销。比如聚集索引扫描（Clustered Index Scan）会包含CPU和I/O开销，而其他一些如流聚合（Stream Aggregate），只有CPU开销。不过还是那句，我们没办法得知具体公式和开销值，所以下面的演示只有一些基础的信息。

我们使用示例数据库AdventureWorks2014。运行下面语句，看一下聚集索引扫描操作的开销情况：

USE AdventureWorks2014
GO

SELECT *
FROM Sales.SalesOrderDetail
WHERE LineTotal = 35

在较为旧的SQL Server版本中，开销以秒为单位，也和特定硬件配置有关。但是在较新的版本（最晚从SQL 2008开始），开销已经跟“单位”没关系，只是一个数值。

如上图，聚集索引扫描操作符在我本机的I/O预估开销为0.915718，CPU预估开销0.133606，,聚集索引扫描操作符中，对于第一行的CPU开销为0.0001581，后续每行为0.0000011，因为表中有121317行，所以总CPU开销为：0.0001581+0.0000011*(121317-1)≈0.133606。同样，对于第一个数据库页，最小的I/O开销为0.003125，然后每一个页为0.00074074。由于是全表扫描（聚集索引等同整表），用下面语句检查实际页数：

USE AdventureWorks2014
GO

SELECTin_row_data_page_count,
    row_count
FROM sys.dm_db_partition_stats
WHERE object_id = object_id('Sales.SalesOrderDetail')
    AND index_id = 1
 

本机得到的数据为：1233，所以预估I/O开销为：0.003125 + 0.00074074 * (1233 – 1)=0.91571668≈0.915718。最后，总开销为0.133606+0.916458=1.05006，就是这个操作符的预估开销。然后把每个操作符的总开销加起来就是这个执行计划的预估总开销。

关于统计信息的基础内容先说到这里，下一节介绍关于“统计信息常见问题及应对方案”