1010cc时时彩标准版 > 1010cc三分网站 > 统计信息,索引阐述系列八

原标题:统计信息,索引阐述系列八

浏览次数:114 时间:2019-10-07

二. 统计信息分析

--查询统计信息
DBCC SHOW_STATISTICS(tablename,'indexname')

  下面是一个复杂的统计信息,上一次更新统计信息时间是2018年5月8日,距离现在有二个多月没更新了,也就是说更新条件没有达到(改变达到500次

  • 20%的行数变动)。

  图片 1

  图片 2

  2.1 统计信息三部分:头信息,字段选择性,直方图。
   (1) 头信息

    name:统计信息名称,也是索引的名字。
    updated:上一次统计信息更新时间(重要)。
    rows:上一次统计表中的行数,反映了表里的数据量。
    rows Sampled: 用于统计信息计算的抽样总行数。当表格数据比较大,为了降低消耗,只会取一小部分数据做抽样。  rows sampled<rows时候统计信息可能不是最精确的。
    steps:把数据分成几组。最多200个组,每个直方图梯级都包含一个列值范围,后跟上限列值。
    density:索引第一列前缀的选择性。查询优化器不使用此 Density, 值此值的目的是为了与 SQL Server 2008 之前的版本实现向后兼容。
    average key length:索引列平均字节数。
    string index: YES 代表字符串索引。

  (2)数据字段选择性

    all density: 反映了索引列的选择度。它反映了数据集里重复的数据量多少,如果数据很少有重复,那么它选择性就比较高。 密度为 1/非重复值。值越小选择性就越高。如果值小于了0.1,那索引的选择性就非常高了(这一点通过查看自增ID主键索引列,非常明显小于了0.1的值)。
    average length: 索引列平均字节长度 例如model 列值平均长度是25个字节。
    columns:索引列名称

  (3)直方图(对应steps 组)

      直方图度量数据集中每个非重复值的出现频率。 查询优化器根据统计信息对象第一个键列中的列值来计算直方图,它选择列值的方法是以统计方式对行进行抽样或对表或视图中的所有行执行完全扫描。
    range_hi_key: 列值也称为键值。直方图里每一组(step)数据最大值 。上图值是model字符串类型
    range_rows:每组数据区间估算数目。
    eq_rows:表中值与直方图每组数据库上限相等的数目
    distinct_range_rows:每组中非重复数目, 如果没有重复则range_rows等于distinct_range_rows值。
    avg_range_rows:每组数据区间重复值平均数目, (range_rows)

 

 三. 人工维护的几种情况

1.查询执行时间很长
  如果查询响应时间很长或不可预知,则在执行其他故障排除步骤前,确保查询具有最新的统计信息。
2.在升序或降序键列上发生插入操作。
  与查询优化器执行的统计信息更新相比,升序或降序键列(例如 IDENTITY 或实时时间戳列)上的统计信息可能要求更频繁地更新。插入操作将新值追加到升序或降序键列上
3.在维护操作后。
  考虑在执行维护过程(例如截断表或对很大百分比的行执行大容量插入)后更新统计信息。 这可以避免在将来查询等待自动统计信息更新时在查询处理中出现延迟。

-- 更新统计信息
UPDATE STATISTICS tablename(indexname)

  更新统计信息可确保查询使用最新的统计信息进行编译。 不过,更新统计信息会导致查询重新编译。 我们建议不要太频繁地更新统计信息,因为需要在改进查询计划和重新编译查询所用时间之间权衡性能。

SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第三篇)

 最近真的没有什么精力写文章,天天加班,为了完成这个系列,硬着头皮上了

再看这篇文章之前请大家先看我之前写的第一篇和第二篇

第一篇:SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第一篇)

第二篇:SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第二篇)

 

1、统计信息的含义与作用

为了以尽可能快的速度完成语句,光有索引是不够的。对于同一句话,SQLSERVER有很多种方法来完成他。

有些方法适合于数据量比较小的时候,有些方法适合于数据量比较大的时候。同一种方法,在数据量不同的时候,

复杂度会有非常大的差别。索引只能帮助SQLSERVER找到符合条件的记录。SQLSERVER还需要知道每一种操作

所要处理的数据量有多少,从而估算出复杂度,选取一个代价最小的执行计划。说得通俗一点,SQLSERVER要能够

知道数据是“长得什么样”的才能用最快方法完成指令

 

SQLSERVER不像人,光看看数据就能够大概心理有数。那么怎麽能让SQL知道数据的分布信息呢?

在数据库管理系统里有个常用的技术,就是数据“统计信息(statistics)”

SQLSERVER就是通过他了解数据的分布情况的

 

下面可以先来看前两篇文章的两张范例表在SalesOrderID这个字段上的统计信息,以便对这个概念有点直观认识

dbo.SalesOrderHeader_test保存的是每张订单的概要信息,一张订单只会有一条记录

所以SalesOrderID是不会重复的。现在这张表里,应该有31474条记录。SalesOrderID是一个int型的字段,

所以字段长度是4。

运行

1 DBCC SHOW_STATISTICS(tablename,INDEX OR STATISTICS name)
2 
3 DBCC SHOW_STATISTICS([SalesOrderHeader_test],SalesOrderHeader_test_CL)

图片 3

统计信息内容分3部分

1、统计信息头信息

       列名                              说明

      name                     统计信息的名称,这里就是索引的名字

     updated                  上一次更新统计信息的日期和时间。这里是12 18 2012  1:16AM
                                   这个时间非常重要,根据他能够判断统计信息是什么时候更新的
                                   是不是在数据量发生变化之后,是不是存在统计信息不能反映当前
                                   数据分布特点的问题

       rows                     表中的行数。这里是31465行,不能完全完全正确地反映了当前表里数据量(因为统计信息没有及时更新)

  rows sampled             统计信息的抽样行数这里也是31465,说明上次SQL更新统计信息
                                   的时候,对整个表里所有记录的SalesOrderID字段,都扫描了一遍
                                  ,这样做出来的统计信息一般都是很精确的

       steps                    在统计信息的第三部分,会把数据分成几组,这里是3组

      density                  第一个列前缀的选择性(不包括EQ_ROWS)

average key length       所有列的平均长度,因为SalesOrderHeader_test_CL索引只有一列数据类型是int,

                                   所以长度是4(单位是字节),如果索引有多个列,每个列的数据类型都不一样,

                                   比如再有一个列colc char(10) 那么平均长度是(10 4)/2=7

     string index             如果为“是”,则统计信息中包含字符串摘要索引,以支持为LIKE条件
                                   估算结果集大小。仅适用于char,varchar,nchar和nvarchar,varchar(max)
                                   nvarchar(max),text,ntext 数据类型的前导列。这里是int,所以这个值是“NO”

 

2、数据字段的选择性
           列名                                说明

all density                反映索引列的选择性(selectivity)
                              "选择性"反映数据集里重复的数据量是多少,或者反过来说,值唯一的数据量
                              有多少。如果一个字段的数据很少有重复,那么他的可选择性就比较高。比如
                              身份证号,是不可重复的。哪怕对整个中国的身份记录做查询,代入一个身份证号码
                              最多只会有一条记录返回,在这样的字段上的过滤条件,能够有效地过滤掉大量数据
                              返回的结果集会比较小
                              举个相反的例子:性别。所有人只有两种,非男即女。这个字段上的重复性就很高
                              选择性就很低。一个过滤条件,最多只能过滤掉一半的记录
                              SQL通过计算“选择性”,使得自己能够预测一个过滤条件做完后,大概能有多少记录
                              返回 Density的定义是: density = 1/cardinality of index keys
                              如果这个值小于0.1,一般讲这个索引的选择性比较高,如果大于0.1,他的选择性
                              就不高了。这里[SalesOrderHeader_test]有31474条没有重复的记录
                              1/31474 = 3.177e-5 这个字段的选择性是不错的

       average length        索引列的平均长度,这里还是4

        columns                 索引列的名称,这里是字段名 SalesOrderID

 

从这一部分的信息,可以推断出统计信息所关心的字段的长度,以及他有多少条唯一值。但是这些信息对SQLSERVER预测结果集复杂度还不够。

比如我现在要查一个SalesOrderID=60000的订单,还是不知道会有多少记录返回。这里需要第三部分的信息

 

3、直方图(histogram)
         列名                                   说明
     range_hi_key                直方图里每一组(step)数据的最大值
                                        订单号的最小号码在表格里是43659,这里SQL选择他作为第一个step
                                        的最大值,3组数据分别是 ~43659  43660~75131   75132~75132

     range_rows                  直方图里每组数据区间行数,上限值除外 第一组只有一个数:43659
                                        第三组也只有一个数:75132,其他数据都在第二组里,区间里有31471个数

      EQ_ROWS                   表中值与直方图每组数据上限值相等的行数目 这里都是1

distinct_range_rows           直方图里每组数据区间非重复值的数目,上限值除外由于这个字段没有重复值,所以这里 就等于range_rows的值

  avg_range_rows              直方图里每组数据区间内重复值的平均数目,上限值除外。计算公式
                                      (range_rows/distinct_range_rows for distinct_range_rows>0)
                                      这里distinct_range_rows的值就等于range_rows的值,所以avg_range_rows等于1

 

有这麽一个直方图,就能够很好地知道表格里的数据分布了。在SalesOrderID这个字段里,最小值是43659,

最大值是75132,在这个区间里有31473个值,而且没有重复值,所以可以推算出表里的值就是从43659开始到75132结束的每个int值。

SQL没有必要存储很多step的信息,只要这3个step,就能够完全表达数据分布

 

这里要说明两点的是:

(1)如果一个统计信息是为一组字段建立的,例如一个复合索引建立在两个以上的字段上,SQLSERVER维护所有字段的选择性信息,

但是只会维护第一个字段的直方图。因为第一个字段的行数就是整张表的行数,就算那个字段在某条记录里为null,SQLSERVER也会做统计

(2)当表格比较大的时候,SQLSERVER在更新统计信息的时候为了降低消耗,只会取表格的一部分数据做抽样(rows sample),

这时候统计信息里面的数据都是根据这些抽样数据估算出来的值可能和真实值会有些差异

 

统计信息越细致,当然会越精确,但是维护统计信息要付出的额外开销也就越大。有可能提高统计信息精确度所带来的执行性能的提升

还抵消不了维护统计信息成本的增加。 SQLSERVER做这样的设计,不是因为其能力有限,而是为了谋求一个对大多数情况都合适的平衡

 

-------------------------------------------统计信息的维护和更新---------------------------------

当SQLSERVER需要去估算某个操作的复杂度时,他必定要试图去寻找相应的统计信息做支持。

DBA无法预估SQLSERVER会运行什么样的操作,所以也无法预估SQLSERVER可能需要什么样的统计信息

如果靠人力来建立和维护统计信息,那将是一个非常复杂的工程。好在SQLSERVER不是这样设计的

在绝大多数情况下,SQLSERVER自己会很好地维护和更新统计信息,用户基本没有感觉,DBA也没有额外的负担。

这主要是因为在SQLSERVER 数据库属性里,有两个默认打开的设置

auto create statistics 自动创建统计信息

auto update statistics自动更新统计信息

他们能够让SQLSERVER在需要的时候自动建立要用到的统计信息,也能在发现统计信息过时的时候,自动去更新他

图片 4

 

SQLSERVER会在什么情形下创建统计信息呢?

主要有3种情况

(1)在索引创建时,SQLSERVER会自动在索引所在的列上创建统计信息,所以从某种角度讲,索引的作用是双重的,

他自己能够帮助SQLSERVER快速找到数据,而他上面的统计信息,也能够告诉SQLSERVER数据的分布情况

补充一下:索引重建的时候也会更新表的统计信息,所以有时候查询变慢的时候重建一下索引查询变快了统计信息的更新也是原因之一

 

(2)DBA也可以通过之类的语句手动创建他认为需要的统计信息 CREATE STATISTICS

如果打开了auto create statistics自动创建统计信息,一般来讲很少需要手动创建

 

(3)当SQSERVERL想要使用某些列上的统计信息,发现没有的时候,“auto create statistics 自动创建统计信息”

会让SQLSERVER自动创建统计信息

例如,当语句要在某个(或者几个)字段上做过滤,或者要拿他们和另外一张表做联接(join) SQLSERVER要估算最后从这张表会返回多少记录。

这时候就需要一个统计信息的支持。如果没有,SQLSERVER会自动创建一个

 

在打开“auto create statistics 自动创建统计信息”的数据库上,一般不需要担心SQLSERVER没有足够的统计信息来选择执行计划。

这一点完全交给SQLSERVER管理就可以了

 

更新统计信息

SQLSERVER不仅要建立合适的统计信息,还要及时更新他们,使他们能够反映表格里数据的变化数据的插入、删除、修改都可能会引起统计信息的更新。

但是,更新统计信息本身也是一件消耗资源的事情,尤其是对比较大的表格。如果有一点点小的修改SQLSERVR都要去更新统计信息,

可能SQLSERVER就得光忙活这个,来不及做其他事情了。SQLSERVER还是要在统计信息的准确度和资源合理消耗之间做一个平衡。

在SQL2005/SQL2008,触发统计信息自动更新的条件是:

(1)如果统计信息是定义在普通表格上,那么当发生下面变化之一后,统计信息就被认为是过时的了。下次使用到时,会自动触发一个更新动作

分离数据库的时候,也可以手动选择是否更新统计信息

 1、表格从没有数据变成有大于等于1条数据

2、对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后

3、对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于 --500 (20%*表格数据总量)以后。所以对于比较大的表,

只有1/5以上的数据发生变化后 --SQL才会去重算统计信息

 

(2)临时表(temp table)上可以有统计信息。其维护策略基本和普通表一致。 但是表变量(table variable)上不能建立统计信息

 

这样的维护策略能够保证花费比较小的代价,确保统计信息基本正确

 

SQL2000和SQL2005在更新统计信息的策略上的区别:

在SQLSERVER2000的时候,如果SQLSERVR在编译一个语句时发现某个表的某个统计信息已经过时,

他会暂停语句的编译,转去更新统计信息,等统计信息更新好以后,用新的信息来做执行计划。这样的方法

当然能够帮助得到一个更准确的执行计划,但是缺点是语句执行要等统计信息更新完毕。这个过程有点费时。

在大部分情况下,语句执行效率对统计信息没有那么敏感。如果用老的统计信息也能做出比较好的执行计划,

这里的等待就白等了

 

所以在SQLSERVER2005以后,数据库属性多了一个“auto update statistics asynchronously自动异步更新统计信息”

图片 5

当SQLSERVER发现某个统计信息过时时,他会用老的统计信息继续现在的查询编译,但是会在后台启动一个任务,更新这个统计信息。

这样下一次统计信息被使用到时,就已经是一个更新过的版本。这样做的缺点是,不能保证当前这句查询的执行计划准确性。

凡事有利有弊,DBA可以根据实际情况做选择

 

写完了,可能篇幅很长,不过没有办法,大部分内容都是首尾呼应,没有前面的铺垫可能看不懂下面的内容

 

 


2013-8-25 补充:

如果需要更新某张表的统计信息,使用下面的SQL语句

1 USE [pratice] --需要更新统计信息的数据库
2 GO
3 
4 UPDATE STATISTICS tableA
5 GO

如果需要更新整个数据库的统计信息,使用下面的SQL语句,不带参数

1 USE [pratice] --需要更新统计信息的数据库
2 GO
3 EXEC [sys].[sp_updatestats] --@resample = '' -- char(8)
4 GO

图片 6图片 7

  1 正在更新 [dbo].[testpivot]
  2     [_WA_Sys_00000001_0425A276],不需要更新...
  3     [_WA_Sys_00000002_0425A276],不需要更新...
  4     已更新 0 条索引/统计信息,2 不需要更新。
  5  
  6 正在更新 [dbo].[Users]
  7     [IX_UserID],不需要更新...
  8     [_WA_Sys_00000002_08EA5793],不需要更新...
  9     [_WA_Sys_00000003_08EA5793],不需要更新...
 10     [_WA_Sys_00000004_08EA5793],不需要更新...
 11     [_WA_Sys_00000005_08EA5793],不需要更新...
 12     已更新 0 条索引/统计信息,5 不需要更新。
 13  
 14 正在更新 [dbo].[TABLE1]
 15     [INDEX_ID],不需要更新...
 16     [INDEX_CATEGORYID],不需要更新...
 17     已更新 0 条索引/统计信息,2 不需要更新。
 18  
 19 正在更新 [dbo].[TABLE2]
 20     [INDEX_CATEGORYID],不需要更新...
 21     已更新 0 条索引/统计信息,1 不需要更新。
 22  
 23 正在更新 [dbo].[Orders]
 24     [_WA_Sys_00000005_0EA330E9],不需要更新...
 25     已更新 0 条索引/统计信息,1 不需要更新。
 26  
 27 正在更新 [dbo].[Department]
 28     [CL_DepartmentID],不需要更新...
 29     已更新 0 条索引/统计信息,1 不需要更新。
 30  
 31 正在更新 [dbo].[UserInfo]
 32     已更新 0 条索引/统计信息,0 不需要更新。
 33  
 34 正在更新 [dbo].[tb_test]
 35     已更新 0 条索引/统计信息,0 不需要更新。
 36  
 37 正在更新 [dbo].[Department9]
 38     [NCL_Name_GroupName],不需要更新...
 39     已更新 0 条索引/统计信息,1 不需要更新。
 40  
 41 正在更新 [dbo].[bulkinserttest]
 42     已更新 0 条索引/统计信息,0 不需要更新。
 43  
 44 正在更新 [dbo].[SystemPara]
 45     [_WA_Sys_00000001_173876EA],不需要更新...
 46     [_WA_Sys_00000002_173876EA],不需要更新...
 47     [_WA_Sys_00000004_173876EA],不需要更新...
 48     已更新 0 条索引/统计信息,3 不需要更新。
 49  
 50 正在更新 [dbo].[TB]
 51     [_WA_Sys_00000001_178D7CA5],不需要更新...
 52     [_WA_Sys_00000002_178D7CA5],不需要更新...
 53     [_WA_Sys_00000003_178D7CA5],不需要更新...
 54     已更新 0 条索引/统计信息,3 不需要更新。
 55  
 56 正在更新 [dbo].[SQLTRACESAMPLE]
 57     已更新 0 条索引/统计信息,0 不需要更新。
 58  
 59 正在更新 [dbo].[HeapTable]
 60     [_WA_Sys_00000001_1A69E950],不需要更新...
 61     已更新 0 条索引/统计信息,1 不需要更新。
 62  
 63 正在更新 [dbo].[testcolumn]
 64     已更新 0 条索引/统计信息,0 不需要更新。
 65  
 66 正在更新 [dbo].[encrypttb_demo]
 67     已更新 0 条索引/统计信息,0 不需要更新。
 68  
 69 正在更新 [dbo].[ClusteredTable]
 70     [CIX],不需要更新...
 71     已更新 0 条索引/统计信息,1 不需要更新。
 72  
 73 正在更新 [dbo].[test23]
 74     已更新 0 条索引/统计信息,0 不需要更新。
 75  
 76 正在更新 [dbo].[Table_1]
 77     [_WA_Sys_00000002_2022C2A6],不需要更新...
 78     [_WA_Sys_00000001_2022C2A6],不需要更新...
 79     已更新 0 条索引/统计信息,2 不需要更新。
 80  
 81 正在更新 [dbo].[Department10]
 82     [NCL_Name_GroupName],不需要更新...
 83     [_WA_Sys_00000003_2116E6DF],不需要更新...
 84     已更新 0 条索引/统计信息,2 不需要更新。
 85  
 86 正在更新 [dbo].[BankUser]
 87     [PK__BankUser__236943A5],不需要更新...
 88     已更新 0 条索引/统计信息,1 不需要更新。
 89  
 90 正在更新 [dbo].[PWDQuestion]
 91     [PK__PWDQuestion__2645B050],不需要更新...
 92     已更新 0 条索引/统计信息,1 不需要更新。
 93  
 94 正在更新 [dbo].[fulltext_test]
 95     [UQ__fulltext_test__28B808A7],不需要更新...
 96     [IX_ID],不需要更新...
 97     已更新 0 条索引/统计信息,2 不需要更新。
 98  
 99 正在更新 [dbo].[tabelcheckindent]
100     [PK_tabelcheckindent],不需要更新...
101     已更新 0 条索引/统计信息,1 不需要更新。
102  
103 正在更新 [dbo].[SecretInfo]
104     已更新 0 条索引/统计信息,0 不需要更新。
105  
106 正在更新 [dbo].[Insert_Test]
107     [_WA_Sys_00000001_2A164134],不需要更新...
108     已更新 0 条索引/统计信息,1 不需要更新。
109  
110 正在更新 [dbo].[TestInsert]
111     [PK__TestInsert__2B3F6F97],不需要更新...
112     已更新 0 条索引/统计信息,1 不需要更新。
113  
114 正在更新 [dbo].[RowToColumn]
115     [_WA_Sys_00000001_2C3393D0],不需要更新...
116     [_WA_Sys_00000002_2C3393D0],不需要更新...
117     [_WA_Sys_00000003_2C3393D0],不需要更新...
118     [_WA_Sys_00000004_2C3393D0],不需要更新...
119     [_WA_Sys_00000005_2C3393D0],不需要更新...
120     [_WA_Sys_00000006_2C3393D0],不需要更新...
121     [_WA_Sys_00000007_2C3393D0],不需要更新...
122     [_WA_Sys_00000008_2C3393D0],不需要更新...
123     已更新 0 条索引/统计信息,8 不需要更新。
124  
125 正在更新 [dbo].[Insert_Test2]
126     [PK__Insert_Test2__2DE6D218],不需要更新...
127     已更新 0 条索引/统计信息,1 不需要更新。
128  
129 正在更新 [dbo].[pagediff]
130     已更新 0 条索引/统计信息,0 不需要更新。
131  
132 正在更新 [dbo].[DP_OilCanOption]
133     [_WA_Sys_00000001_31EC6D26],不需要更新...
134     [_WA_Sys_00000002_31EC6D26],不需要更新...
135     已更新 0 条索引/统计信息,2 不需要更新。
136  
137 正在更新 [dbo].[DBCCResult]
138     [_WA_Sys_00000002_32767D0B],不需要更新...
139     [_WA_Sys_0000000A_32767D0B],不需要更新...
140     已更新 0 条索引/统计信息,2 不需要更新。
141  
142 正在更新 [sys].[fulltext_catalog_freelist_16]
143     [docid],不需要更新...
144     已更新 0 条索引/统计信息,1 不需要更新。
145  
146 正在更新 [sys].[fulltext_index_map_667149422]
147     [i1],不需要更新...
148     [i2],不需要更新...
149     [i3],不需要更新...
150     [i4],不需要更新...
151     已更新 0 条索引/统计信息,4 不需要更新。
152  
153 正在更新 [dbo].[计算列]
154     已更新 0 条索引/统计信息,0 不需要更新。
155  
156 正在更新 [dbo].[LobTestTable]
157     [_WA_Sys_00000003_351DDF8C],不需要更新...
158     已更新 0 条索引/统计信息,1 不需要更新。
159  
160 正在更新 [dbo].[LobIndexTestTable]
161     [IX_LobIndexTestTable],不需要更新...
162     [IX_LobCIndexTestTable],不需要更新...
163     已更新 0 条索引/统计信息,2 不需要更新。
164  
165 正在更新 [dbo].[Department3]
166     [CL_DepartmentID],不需要更新...
167     已更新 0 条索引/统计信息,1 不需要更新。
168  
169 正在更新 [dbo].[LobCIndexTestTable]
170     [IX_LobCIndexTestTable],不需要更新...
171     已更新 0 条索引/统计信息,1 不需要更新。
172  
173 正在更新 [dbo].[Department4]
174     [PK_Department4_1],不需要更新...
175     [_WA_Sys_00000002_3A179ED3],不需要更新...
176     已更新 0 条索引/统计信息,2 不需要更新。
177  
178 正在更新 [dbo].[testheap2013119]
179     已更新 0 条索引/统计信息,0 不需要更新。
180  
181 正在更新 [dbo].[Department5]
182     [CL_Company],不需要更新...
183     [_WA_Sys_00000002_3CF40B7E],不需要更新...
184     [_WA_Sys_00000001_3CF40B7E],不需要更新...
185     已更新 0 条索引/统计信息,3 不需要更新。
186  
187 正在更新 [dbo].[TESTkeylock]
188     [PK_TEST11],不需要更新...
189     已更新 0 条索引/统计信息,1 不需要更新。
190  
191 正在更新 [dbo].[Department6]
192     [PK_Department6_1],不需要更新...
193     已更新 0 条索引/统计信息,1 不需要更新。
194  
195 正在更新 [dbo].[ChangeAttempt]
196     已更新 0 条索引/统计信息,0 不需要更新。
197  
198 正在更新 [dbo].[Department2]
199     [PK__Department2__467D75B8],不需要更新...
200     [_WA_Sys_00000003_4589517F],不需要更新...
201     已更新 0 条索引/统计信息,2 不需要更新。
202  
203 正在更新 [dbo].[tempPKNCL]
204     [PK__tempPKNCL__46E78A0C],不需要更新...
205     已更新 0 条索引/统计信息,1 不需要更新。
206  
207 正在更新 [dbo].[test_index]
208     [PK__test_index__489AC854],不需要更新...
209     已更新 0 条索引/统计信息,1 不需要更新。
210  
211 正在更新 [dbo].[ddl_log]
212     [_WA_Sys_00000002_48CFD27E],不需要更新...
213     [_WA_Sys_00000003_48CFD27E],不需要更新...
214     [_WA_Sys_00000004_48CFD27E],不需要更新...
215     [_WA_Sys_00000005_48CFD27E],不需要更新...
216     已更新 0 条索引/统计信息,4 不需要更新。
217  
218 正在更新 [dbo].[Tmp_testComputeColumn]
219     已更新 0 条索引/统计信息,0 不需要更新。
220  
221 正在更新 [dbo].[test1]
222     [PK_test1],不需要更新...
223     已更新 0 条索引/统计信息,1 不需要更新。
224  
225 正在更新 [dbo].[test13]
226     [pk],不需要更新...
227     已更新 0 条索引/统计信息,1 不需要更新。
228  
229 正在更新 [dbo].[Department8]
230     [NCL_Name_GroupName],不需要更新...
231     [_WA_Sys_00000001_52E34C9D],不需要更新...
232     [_WA_Sys_00000003_52E34C9D],不需要更新...
233     已更新 0 条索引/统计信息,3 不需要更新。
234  
235 正在更新 [dbo].[Department12]
236     [PK__Department12__7167D3BD],不需要更新...
237     [NCL_Name_GroupName],不需要更新...
238     已更新 0 条索引/统计信息,2 不需要更新。
239  
240 正在更新 [dbo].[CompareNonclusteredScan]
241     [_WA_Sys_00000003_73501C2F],不需要更新...
242     已更新 0 条索引/统计信息,1 不需要更新。
243  
244 正在更新 [dbo].[Department13]
245     [PK__Department13__762C88DA],不需要更新...
246     [NCL_Name_GroupName],不需要更新...
247     [_WA_Sys_00000003_753864A1],不需要更新...
248     已更新 0 条索引/统计信息,3 不需要更新。
249  
250 正在更新 [sys].[queue_messages_1977058079]
251     [queue_clustered_index],不需要更新...
252     [queue_secondary_index],不需要更新...
253     已更新 0 条索引/统计信息,2 不需要更新。
254  
255 正在更新 [dbo].[Department11]
256     [PK__Department11__7908F585],不需要更新...
257     [NCL_Name_GroupName],不需要更新...
258     已更新 0 条索引/统计信息,2 不需要更新。
259  
260 正在更新 [sys].[queue_messages_2009058193]
261     [queue_clustered_index],不需要更新...
262     [queue_secondary_index],不需要更新...
263     已更新 0 条索引/统计信息,2 不需要更新。
264  
265 正在更新 [sys].[queue_messages_2041058307]
266     [queue_clustered_index],不需要更新...
267     [queue_secondary_index],不需要更新...
268     已更新 0 条索引/统计信息,2 不需要更新。
269  
270 正在更新 [dbo].[Demo_AExportHeader]
271     已更新 0 条索引/统计信息,0 不需要更新。
272  
273 正在更新 [dbo].[table_a]
274     [_WA_Sys_00000001_7B905C75],不需要更新...
275     已更新 0 条索引/统计信息,1 不需要更新。
276  
277 正在更新 [dbo].[tableA]
278     [_WA_Sys_00000002_7E6CC920],不需要更新...
279     已更新 0 条索引/统计信息,1 不需要更新。
280  
281 已更新了所有表的统计信息。

View Code

 

1、在索引创建时,SQL Server会自动的在索引列上创建统计信息。

target 参数是:索引的名称,统计对象的名称,或者列名。如果target是索引名称,或统计对象的名称,那么该命令返回关于target的统计信息。如果target是数据列,那么该命令会自动在该列上创建统计,返回关于该列的统计信息。

4.1 创建

  • 查询优化器自动创建
    • 创建索引时,查询优化器自动为表格或者视图上的索引创建统计信息
    • 在 AUTO_CREATE_STATISTICS 为 ON 时,查询优化器为查询谓词中的单列创建统计信息
  • 手动执行创建

    • CREATE STATISTICS 创建

常规情况下,查询优化器创建的统计信息就可以满足我们的大多数需求,但是如果出现以下情况,可以考虑手动创建:

  • 数据库引擎优化顾问建议创建
  • 查询谓词包含尚不位于相同索引中的多个相关列
  • 查询从数据的子集中选择数据
  • 查询缺少统计信息

一.概述  

  sql server在快速查询值时只有索引还不够,还需要知道操作要处理的数据量有多少,从而估算出复杂度,选择一个代价小的执行计划,这样sql server就知道了数据的分布情况。索引的统计值信息,还内置策略用来在没有索引的属性列上创建统计值。在有索引和没有索引的属性列上统计值信息会被自动维护。大部分场景下不需要手动去维护统计信息。   
  作用是 sqlserver 查询优化器使用统计信息来创建可提高查询性能的查询计划。 对于大多数查询,查询优化器已为高质量查询计划生成必要的统计信息。每个索引都会自动建立统计信息, 统计信息的准确性直接影响指令的速度,执行计划的选择是依据统计信息。

  1.1 属性列统计值
  默认情况下,每当在一个查询的where子句中使用非索引属性列时,sqlserver会自动地创建统计值,统计名称以_WA_Sys开头。

-- 查看表中非索引的统计信息
 sp_helpstats PUB_Search_Log

   如下所示:

 图片 8图片 9

  1.2 自动更新统计信息的阀值

  在自动更新统计信息选项 AUTO_UPDATE_STATISTICS 为 ON 时,查询优化器将确定统计信息何时可能过期。查询优化器通过计算自最后统计信息更新后数据修改的次数并且将这一修改次数与某一阈值进行比较,确定统计信息何时可能过期。
  (1)如果在评估时间统计信息时表基数为 500 或更低,则每达到 500 次修改时更新一次。
  (2)如果在评估时间统计信息时表基数大于 500,则改变每达到 500 20%的行数更新一次(大表特别要注意更新时间)

· Density:密度值,也就是列值前缀的大小。

第一行的密度是0.001,由于列id的唯一值数量是1000,因此,1/1000=0.001

1 什么是统计信息

    统计信息 描述了 表格或者索引视图中的某些列的值 的分布情况,属于数据库对象。根据统计信息,查询优化器就能评估查询过程中需要读取的行数及结果集情况,同时也能创建高质量的查询计划。有了统计信息,查询优化器可以使用基数估计来选择合理的索引,而不需要耗费更多的IO资源扫描来评估哪个索引合理,能有效提供查询性能。所以,简单的说,统计信息是用来 反应数据在实体表格或者视图中的分布情况。

当然,我们也可以手动的更新统计信息,更新脚本如下:

3,密度向量

3.2 AUTO_UPDATE_STATISTICS

    默认为ON。自动更新统计信息选项,查询优化器自动确定统计信息何时过期何时需要更新。

通常情况,从上次自动更新至今,如果期间积累了较大数量的数据变更,包括插入、删除及修改,或表结构变更等,均会造成统计信息过期。

    该选项适用于为索引创建统计信息对象、查询谓词中的单列以及使用 create statistics 语句创建的统计信息。

 

统计信息是数据分布的反馈,SQL Server根据数据更新的数量和特定的规则自动更新统计信息,一般情况下,表的数据量越大,SQL Server更新统计信息需要的数据更新量越大,随着数据的更新,有些表的数据不会及时更新,以至于统计信息过时,不能真实反映数据的分布情况,用户可以通过命令手动更新统计信息,但是更新统计信息需要扫描数据表,这可能是一个非常耗时的IO密集型操作,用户需要权衡性能的提升和资源的消耗。

3.1 AUTO_CREATE_STATISTICS

    默认为ON。自动创建统计信息选项,仅应用于 表格单列统计信息!!!

    查询优化器根据查询谓词的使用情况,在表格上单独给某一列创建统计信息(这些单列暂时未创建直方图),协助查询计划的基数估计。

    该选项不决定是否为索引创建统计信息,也不生产筛选统计信息。

    通过该选项创建的统计信息,名称以 _WA 开头。可以通过sys.stats视图查看。

1 SELECT OBJECT_NAME(s.object_id) AS object_name,
2     COL_NAME(sc.object_id, sc.column_id) AS column_name,
3     s.name AS statistics_name
4 FROM sys.stats AS s JOIN sys.stats_columns AS sc
5     ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
6 WHERE s.name like '_WA%'
7 ORDER BY s.name;

我也相信经过上面三部分的分析,关于文章开篇我们提到的那个关于‘K’和‘Y’的问题会找到答案了,这里不解释了。

--Distinct Count=1000
select count( distinct id)
from dbo.dt_test

3 影响统计信息的选项

    每个表格或者索引视图 何时创建统计信息、基于哪些列创建统计信息及何时更新统计信息,需要根据  AUTO_CREATE_STATISTICS 、 AUTO_UPDATE_STATISTICS、 AUTO_UPDATE_STATISTICS_ASYNC 的设定值 来确定,这三个属于 数据库级别的选项,可以通过系统视图查看,也可以通过 图形界面选择数据库的“属性”,查看“选项”。

1 --查看数据库统计信息选项设定值
2 SELECT
3       name dbname,
4       is_auto_create_stats_on,
5          is_auto_update_stats_on,
6          is_auto_update_stats_async_on
7 FROM sys.databases

2、临时表上也可以有统计信息。这也是很多情况下采用临时表优化的原因之一。其维护策略基本和普通表格一样,但是表变量不能创建统计信息。

例如,当前范围中有(1),(2),(3),(1),(2)五个数据行,最大值是(3),且只有一个,因此,RANGE_HI_KEY=(3),EQ_Rows=1,除去最大值,共有4行数据,唯一值是2个,因此Range_Rows=4,DISTINCT_RANGE_ROWS=2,由于唯一值的数量不是0,因此,AVG_RANGE_ROWS=4/2。

2 统计信息的内容

    可以通过sys.stats查看到统计信息的名字及基于哪一个表格,然后根据 dbcc show_statistics(<table_name>,<index_or_statistics_name>) 来查看统计信息内容。

 

图片 10

可以看到,统计信息分为三部分内容,头信息,数据字段选择性及直方图。

b、统计信息的覆盖索引项

UPDATE STATISTICS (Transact-SQL).aspx)

3.3 AUTO_UPDATE_STATISTICS_ASYNC

    默认为OFF。异步自动更新统计信息选项,确定查询优化器是使用 同步统计信息更新还是异步统计信息更新。OFF则代表使用同步自动更新统计信息,这样,查询计划始终使用最新的统计信息进行编译执行,如果遇到统计信息过期,则会在查询编译前等待更新统计信息,若是异步自动更新统计信息,则在遇到统计信息过期时,直接使用现有统计信息编译然后执行,即使可能由于统计信息过期造成编译不佳,执行计划非最优,但仍按照编译结果运行。

    该选项使用于适用于 为索引创建的统计信息对象、查询谓词中的单列以及使用 CREATE STATISTICS 语句创建的统计信息。

通常情况下,使用 同步自动更新统计信息,则设置该选项为OFF,而在以下两种情况下,则可开启为ON(来自官网):

  • 应用程序贫富执行相同查询或者类似查询,与同步统计信息更新相比,使用异步统计信息更新查询的响应时间可以不受影响,避免出现等待最新统计信息的情况;
  • 应用程序遇到了客户端请求超时,这些超时是由于一个或多个查询正在等待更新后的统计信息所导致的。 在某些情况下,等待同步统计信息可能会导致应用程序因过长超时而失败。

select * into CustomersStats from Customers

第一条记录是数据表的最小值,也是该范围的最大值,数据只有一条:

4.2 更新

    统计信息定义在普通的表格上,当发生以下任一变化时,统计信息就会被认为是过时的,下次使用到的时候,会自动触发更新动作:

  • - 表格从没有数据变成大于等于1条数据;
  • - 对于数据量小于500行的表格,当统计信息的第一个字段数据累计变化量大于500以后;
  • - 对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化量大于500 (20%*表格数据总量)以后。

    这三种情况下,第三种情况最容易出现更新不及时的情况,比如一张100万的表格,它最近一个月的数据增长是15万左右,由于小于20%,统计信息没有更新,这就导致了有关最近一个月数据sql执行有不是很正确的信息提供,那么就需要定期去检查并及时更新统计信息!

 

    临时表上可以有统计信息,其维护策略基本和普通表格一样,但是表变量上不能建立统计信息。

 1 --更新指定统计信息
 2 UPDATE STATISTICS Sales.SalesOrderDetail AK_SalesOrderDetail_rowguid;
 3 GO
 4 
 5 --更新表格上的所有统计信息
 6 UPDATE STATISTICS Sales.SalesOrderDetail;
 7 GO
 8 
 9 --更新整个数据库上的所有统计信息
10 EXEC sp_updatestats;
11 
12 --删除统计信息
13 DROP STATISTICS Purchasing.Vendor.VendorCredit, Sales.SalesOrderHeader.CustomerTotal;
14 GO
15 
16 --查看统计信息上一次更新时间
17 
18 SELECT
19        OBJECT_NAME(OBJECT_ID)
20 FROM sys.stats
21 WHERE STATS_DATE(object_id, stats_id) is not null

 

参考资料:

 

举个例子:比如上面的例子该列存在91行,假如顾客不存在重名的情况下,那么该密度值就为1/91=0.010989,该列为性别列,那么它只存在两个值:男、女,那么该列的密度值就为0.5,所以相比而言SQL Server在索引选择的时候很显然就会选择ContactName(顾客名字)列。

第二条记录,范围的最大值是7,范围的最小值是1,是大于第一条记录(0)的最小值;从1到7共有7条记录,除去最大值7之外,共有6行数据,所以,Range_Rows=6;这6行数据都不重复,因此DISTINCT_RANGE_ROWS=6;由于DISTINCT_RANGE_ROWS>0,因此 AVG_RANGE_ROWS=Range_Rows/DISTINCT_RANGE_ROWS=6/6=1。

4  何时创建与更新

创建统计信息

直方图第二行:RANGE_HI_KEY=7,EQ_Rows=1,Range_Rows=6,DISTINCT_RANGE_ROWS=6,AVG_RANGE_ROWS=1

2.1 头信息

列名 说明
Name 统计信息的名称。
Updated 上次更新统计信息的日期何时间
Rows 预估表中的行数,不一定是精确的
Rows Sampled 统计信息的抽样行数,如果小于Rows,则说明直方图和密度结果是更加抽样行估计的
Steps 直方图中的梯级数。
Number of steps in the histogram.
每个梯级都跨越一个列值范围,后跟上限列值。 直方图梯级是根据统计信息中的第一个键列定义的。 最大梯级数为 200。
Density 计算公式为 1/统计信息对象第一个键列中的所有值(不包括直方图边界值)的非重复值。 查询优化器不使用此 Density 值,显示此值的目的是为了与 SQL Server 2008 之前的版本实现向后兼容。
Average key length 统计信息对象中所有键列的每个值的平均字节数。
String Index Yes 指示统计信息对象包含字符串摘要统计信息,以改进对使用 LIKE 运算符的查询谓词的基数估计;例如 WHERE ProductName LIKE '%Bike'。
Yes indicates the statistics object contains string summary statistics to improve the cardinality estimates for query predicates that use the LIKE operator; for example, WHERE ProductName LIKE '%Bike'.
字符串摘要统计信息与直方图分开存储,并当它是类型的统计信息对象第一个键列上创建char, varchar, nchar, nvarchar, varchar (max), nvarchar (max),文本,或ntext。
Filter Expression 包含在统计信息对象中的表行子集的谓词。 NULL = 未筛选的统计信息。 有关筛选的谓词的详细信息,请参阅Create Filtered Indexes。 有关筛选的统计信息的详细信息,请参阅统计信息。
Unfiltered Rows 应用筛选表达式前表中的总行数。 如果筛选表达式为 NULL,则 Unfiltered Rows 等于 Rows。

Atitit sql计划任务与查询优化器--统计信息模块

2,头部数据

2.2 数据字段选择性

列名 Description
Density 密度为 1/非重复值。 结果显示统计信息对象中各列的每个前缀的密度,每个密度显示一行。 非重复值是每个行前缀和列前缀的列值的非重复列表。 例如,如果统计信息对象包含键列 (A, B, C),结果将报告以下每个列前缀中非重复值列表的密度:(A)、(A,B) 以及 (A, B, C)。 使用前缀 (A, B, C),以下每个列表都是一个非重复值列表:(3, 5, 6)、(4, 4, 6)、(4, 5, 6) 和 (4, 5, 7)。 使用前缀 (A, B),相同列值则具有以下非重复值列表:(3, 5)、(4, 4) 和 (4, 5)
Average Length
存储列前缀的列值列表的平均长度(以字节为单位)。 例如,如果列表 (3, 5, 6) 中的每个值都需要 4 个字节,则长度为 12 个字节。
columns
为其显示 All density 和 Average length 的前缀中的列的名称。

· Filter Expression:过滤表达式,这个是SQL Server2008以后版本的新特性,支持添加过滤表达式,更加细粒度进行统计分析。

统计信息不是实时更新的,如果统计信息过期,查询优化器(Query optimizer)可能不能生成高质量的查询计划,必须有必要的调度程序,自动更新统计数据。数据库管理员(DBA)可以使用DBCC SHOW_STATISTICS 能够查看表或索引视图(Indexed view)的统计信息,以及最后一次更新统计信息的日期,如果统计信息过期,可以使用UPDATE STATISTICS命令手动更新统计信息,以使查询优化器依据正确的统计信息生成高效的查询计划。但是,并不是统计信息更新的越频繁越好,更新统计信息是IO密集型的操作,还会导致现有的查询计划的重新编译,建议不要太频繁地更新统计信息,在改进查询计划和查询计划的重新编译之间权衡开销,找到一个平衡点。

2.3 直方图

列名 Description
RANGE_HI_KEY 直方图梯级的上限列值。 列值也称为键值。
RANGE_ROWS 其列值位于直方图梯级内(不包括上限)的行的估算数目。
EQ_ROWS 其列值等于直方图梯级的上限的行的估算数目。
DISTINCT_RANGE_ROWS 非重复列值位于直方图梯级内(不包括上限)的行的估算数目。
AVG_RANGE_ROWS
重复列值位于直 方图梯级内(不包括上限)的平均行数(如果 DISTINCT_RANGE_ROWS > 0,则为 RANGE_ROWS / DISTINCT_RANGE_ROWS)。

   

    直方图,用于计算数据中每个非重复值出现的频率。使用统计信息对象的第一个键列中的列值来计算直方图,可以通过抽样行或者全表扫描的形式。如果是抽样创建,那么,这里边的 存储总行数何非重复值总数则为估计值。

    创建直方图的时候,查询优化器对列值进行排序,同时计算每个非重复列值匹配的个数,然后将这列非重复列值 分为 1-200个连续的直方图梯级中,每个梯级包含一个列值范围,该范围介于两个边界值之间的所有可能列值,不包含边界值本身,最小的排序列值是第一个直方图梯级的上限值。

作者:: 绰号:老哇的爪子claw of Eagle 偶像破坏者Iconoclast image-smasher

二,验证分布直方图数据

All density:反映索引列的稠密度值。这是一个非常重要的值,SQL Server会根据这个评分项来决定该索引的有效程度。

密度向量始终是从索引列的第一列开始统计,如果筛选子句(where,on)中没有包含索引的第一列,那么查询优化器不会使用索引,因此,索引列的顺序非常重要。

· Unfiltered Rows:没有经过表达式过滤的行,也是新特性。

update statistics dbo.dt_test [cix_dt_test_idcode]

· AVG_RANGE_ROWS:每个直方图平均的行数。

直方图第一行:RANGE_HI_KEY=0, EQ_Rows=1 ,Range_Rows=0,DISTINCT_RANGE_ROWS=0,AVG_RANGE_ROWS=1

· EQ_ROWS:这里表示和上面最大值相等的行数目。因为我们不包含一样的,所以这里值都为 1

dbcc show_statistics('dbo.dt_test',[cix_dt_test_idcode])

本文由1010cc时时彩标准版发布于1010cc三分网站,转载请注明出处:统计信息,索引阐述系列八

关键词:

上一篇:centos安装配置教程,centOS的mysql和tomcat的安装配置

下一篇:没有了