全废话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 输出了错误信息，请与系统管理员联系。

 

在这里是因