《Microsoft Sql server 2008 Internals》读书笔记--第八章The Query Optimizer(4)

《Microsoft Sql server 2008 Internals》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

 

上篇介绍了架构优化，下来我们一起了解几个非常重要的概念：统计(Statistics)、基线估计(Cardinality estimation)和成本(costing)

 查询优化器评估每个操作的成本，决定采用哪个计划。这个成本基于被用于评估每个操作影响的行数量而得出的统计信息。默认，统计信息在优化进程中生成，以帮助生成基线(或标量)评估。查询优化器决定每个表的哪些列被统计。
一旦某几列被标识为需要统计，查询优化器试图找到一个已经存在的该列统计对象。找不到时，则会用系统样例数据创建一个新的统计对象。如果已经存在一个对象，则判断其是否足够编译当前的查询。如果该对象已经过期，一个新的样例被用于重建这个统计对象。这个过程对每一个需要统计的列持久生效。
自动创建和自动更新这两个默认选项是默认启用的。你可以通过以下语句来禁用：

Alter database... Set Auto_Create_Statistics {ON|OFF}
Alter database... Set Auto_Update_Statistics {ON|OFF}

也可以使用定义符的提示来控制单个统计对象的自动更新行为：

Create Index...With(statistics_Norecompute=ON)
Create  Statistics...With(Norecompute)

这些设置通常会被启用，创建或更新一个统计时有可能会被禁用：
■数据库有一个维护窗口，DBA决定显式更新统计而不是默认的自动更新。此时DBA认为如果统计被更改，查询优化器会选择一个差的计划。
■数据库的表非常大，自动更新统计的时间会非常长。
■数据表有非常多的惟一值，用于生成统计的样例速度不足以捕捉生成一个很好计划所需的统计。DBA通常会采用一个维护窗口来手动更新统计经获取一个更高的样例速率。
■数据库应用程序有一个非常短的查询超时定义。特别在一个事务中，一个查询超时会引起整个事务Abort而回滚。
■统计设计
统计被存储于系统元数据中。它们主要由一系列直方图（Histogram)组成。请注意：统计和直方图不是一个概念。统计对象的其他元素包括一些头信息（包括样例行的数量等）、Tries Trees(用于string 列的分发）和浓度信息等。
统计能被SQL Server 2008中的大多数年数据类型（，而不是全部）创建。作为通用的规则，数据类型支持比较符如>,=等等。而老的BLOB数据类型如text\ntext则不支持统计，还有一些用户创建的非字节序列比较的自定义数据类型也不支持。
此外，SQL Server支持计算列的统计，这允许查询优化器作基于表达式的标量统计，如几何类型。

下面我们看一个例子：

Create table geog(col1 int identity,col2 Geography)
Insert into geog (col2) values(null)
Insert into geog(col2) values(Geography::Parse('LineString(0 0,0 10,10 10,10 0,0 0)'))
alter table geog  add col3 as col2.STStartPoint().ToString() persisted
Create Statistics s2 ON geog(col3)
DBCC SHOW_Statistics('geog','s2')

在元数据中查询以上统计：

 SELECT o.Name as tablename,s.name as statname
from sys.stats s inner join sys.objects o on s.object_id=o.object_id

 

 如下图所示：

 

 一旦查询优化器决定需要创建一个新的统计对象或更新一个已存在的过时的统计对象，系统将创建一个内部查询发生成一个新的统计对象。

注意：StatMan是一个特殊的内部汇总函数。它生成一个存储在操作中被创建的直方图\浓度信息\trie trees的BLOB。完成后，统计BLOB被存储在数据库元数据中，被用于查询。

 生成统计的优化样例数据库页面包含来自每一个样例页的所有行。对小的表，所有的页都是样例（意味着所有的行都被用于创建直方图）。如果是大的表，越来越小的页面的百分比作为样例。为了保持直方图在合理的大小，这个总数被限制在200阶。如果建立直方图时每行的惟一值超过200,查询优化器运用逻辑基于算法削减步骤而尽量保持分发信息。因为直方图对于捕捉非标准的系统分发的数据非常管用，这意味着它尽量保持捕捉最频繁用到的信息。

■浓度/频度信息

 除了直方图，查询优化器还跟踪列集的惟一值的数量。列中复制值的平均数量，也就是浓度(Density),浓度刚好是频度(Frequency)的倒数。浓度存储在直方图中。

看一个例子：

Create table MultiDensity(col1 int,col2 int)
go
Declare @i int
set @i=0
while @i<100
BEGIN
    Insert into MultiDensity(col1,col2) values(@i,@i+1);
    Insert into MultiDensity(col1,col2) values(@i,@i+2);
    Insert into MultiDensity(col1,col2) values(@i,@i+3);
    set @i=@i+1
END
go
Create statistics s1 on MultiDensity(col1,col2);
go
--select * from MultiDensity
DBCC Show_Statistics('multiDensity','s1');

 

 

 上图中显示：

 列col1的浓度信息是0.01,1/0.01＝100，即列的惟一值的个数是100,(col1,col2）的浓度信息大约是0.00333。

我们通过Group by 来检查一下基线估计：

 

SET Statistics Profile on
Select count(*) as CNT from multiDensity group by col1

 

查询优化器实际上（在为一个操作计算输出基线的时候）不得不执行一个附加的步骤。因为统计通常创建在调用它们的查询编译之前，而且通常是一些样例数据。存储在统计对象的值通常不匹配行的准确数量。因此查询优化器使用两个值来计算在操作中被修饰的行的分量(Fraction)。

事实上最为不理想的是估计的基线查询与实际查询差距过大。别担心，统计分析器会帮你辨认什么是"坏的"信息。你可能需要的只是手动更新一下统计以捕捉更新的数据，或创建一个新的更大样例的统计。

本篇主要列举了统计的概念和统计的设计、统计的浓度。下篇将关注筛选统计和字符串统计、基线估计。