澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:SQLSE宝马X五VE途观是怎麽通

2019-05-06 作者:数据库网络   |   浏览(100)

一.概述  

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

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

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

   如下所示:

 澳门新萄京官方网站 1澳门新萄京官方网站 2

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

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

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的订单,还是不知道会有多少记录返回。这里需要第三部分的信息

澳门新萄京官方网站:SQLSE宝马X五VE途观是怎麽通过索引和计算信息来找到对象数据的,SE大切诺基VE景逸SUV的总结新闻。 

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也会做统计

澳门新萄京官方网站:SQLSE宝马X五VE途观是怎麽通过索引和计算信息来找到对象数据的,SE大切诺基VE景逸SUV的总结新闻。(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

 

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

SQL Server基于开销(Cost)评估执行计划,选择开销最小的作为“最优化”的执行计划,由于SQL Server根据索引及其统计信息来计算开销,所以,对查询优化来说,索引和统计数据是非常重要的,查询优化器(Query Optimizer)使用统计信息对查询的开销进行评估(Estimate),选择开销小的查询计划,作为最终的、“最优的”的执行计划。SQL Server自动为索引列或查询的数据列创建统计信息,统计信息包括三部分:头部(Header),密度向量(Density Vector) 和 分布直方图(Distribution Histogram)。

1 什么是统计信息

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

二. 统计信息分析

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

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

  • 20%的行数变动)。

  澳门新萄京官方网站 8

  澳门新萄京官方网站 9

  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)

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

 

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

2 统计信息的内容

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

 

澳门新萄京官方网站 10

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

 

一,查看统计信息

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。

每一个统计信息的内容都包含以上三部分的内容。

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

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 的前缀中的列的名称。

我们依次来分析下,通过这三部分内容SQL Server如何了解该列数据的内容分布的。

DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target ) 
WITH STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

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个连续的直方图梯级中,每个梯级包含一个列值范围,该范围介于两个边界值之间的所有可能列值,不包含边界值本身,最小的排序列值是第一个直方图梯级的上限值。

a、统计信息的总体属性项

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

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

该部分包含以下几列:

1,统计对象

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;

· Name:统计信息的名称。

在SSMS中打开Table的属性,展开“Statistics”,这就是跟该表有关的统计对象:

3.2 AUTO_UPDATE_STATISTICS

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

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

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

· Updated:统计信息的最近一次更新时间,这个时间信息很重要,根据它我们能知道该统计信息什么时候更新的,是不是最新的,是不是存在统计信息更新不及时造成统计的当前数据分布不准确等问题。

澳门新萄京官方网站 11

3.3 AUTO_UPDATE_STATISTICS_ASYNC

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

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

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

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

· Rows:描述当前表中的总行数。

查看统计对象 [cix_dt_test_idcode]的统计信息:

4  何时创建与更新

· Rows Sampled:统计信息的抽样数据。当数据量比较多的时候,统计信息的获取是采用的抽样的方式统计的,如果数据量比较就会通过扫描全部获取比较精确的统计值。比如,上面的例子中抽样数据就为91行。

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

4.1 创建

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

    • CREATE STATISTICS 创建

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

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

· Steps:步长值。也就是SQL Server统计信息的根据数据行的分组的个数。这个步长值也是有SQL Server自己确定的,因为步长越小,描述的数据越详细,但是消耗也越多,所以SQL Server会自己平衡这个值。

命令返回的统计信息包含三部分,分别是 头部信息,密度向量和分布直方图:

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

 

参考资料:

 

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

 澳门新萄京官方网站 12

· Average Key length:所有列的平均长度。

2,头部数据

· String Index:表示统计值是否为字符串的统计信息。这里字符串的评估目的是为了支持LIKE关键字的搜索。

第一个表是Header表,Name字段是统计对象的名称,

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

澳门新萄京官方网站 13

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

头部数据返回的字段说明:

经过上面部分的数据,统计信息已经分析出该列数据的最近更新时间、数据量、数据长度、数据类型等信息值。

  • Updated字段:是统计信息最后一次更新的时间,通过该字段,可以判断统计信息是否过期。
  • Rows字段:是统计信息更新时,表或索引视图(Indexed View)中的数据行数量,注意,该字段不会实时反应数据表的总行数。
  • Rows Sampled字段:用于计算统计信息时的样本数据的总行数,如果 Rows Sampled < Rows,显示的直方图和密度结果是根据抽样数据进行估计的。
  • Steps字段:是分布直方图中的梯级数。每个梯级都跨越一个列值范围,直方图梯级是根据统计信息中的第一个键列定义的,最大梯级数为 200。

 

3,密度向量

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

第二个表是密度向量(Density Vector),用于对键列(Key Column)执行密度分析,密度的计算公式非常简单:1和唯一值的比例,即 density= 1/(Distinct Value的个数)

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

澳门新萄京官方网站 14

澳门新萄京官方网站,该分值的计算公式为:density=1/表中非重复的行数。所以该稠密度值取值范围为:0-1。

密度向量的总行数跟索引键的数量有关,每一行都是索引键的前缀组合,而唯一值是前缀组合列的无重复值。例如,如果统计对象包含索引键列(A,B,C),密度向量为3行,第一行是(A)的密度,唯一值是列A的无重复值;第二行是(A,B)的密度,唯一值是列A和B的无重复值;第三行是(A,B,C)的密度,唯一值是列A,B和C的无重复值。

该值越小说明该列的索引项选择性更强,也就说该索引更有效。理想的情况是全部为非重复值,也就是说都是唯一值,这样它的数最小。

示例中索引列是(id,code),索引列的密度是计算(id),(id,code)的密度,密度向量表中,All Density字段是统计对象计算的密度。

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

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

简单点讲:就是当前索引的选择性高,它的稠密度值就小,那么它就重复值少,这样筛选的时候更容易找到结果值。相反,重复值多选择性就差,比如性别,一次过滤只能过滤掉一半的记录。

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

Average Length:索引的平均长度。

试想,如果列ID的重复值比较多,(ID,Code)组合的重复值比较少,那么(ID)的All Density的值大于(ID,Code)的密度,通过Density Vector可以看出数据重复率的趋势。

Columns:索引列的名称。这里因为我们是非聚集索引,所以会存在两行,一行为ContactName索引列,一行为ContactName索引列和聚集索引的列值CustomerID组合列。希望能明白这里,索引基础知识。

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

通过以上部分信息,SQL Server会知道该部分的数据获取方式那个更快,更有效。

4,分布直方图

 

第三个表是分布直方图(Distribution Histogram),使用参数target的第一个索引键列(key column)来统计数据的分布,统计的数据是第一个索引列中非重复值的出现频率。如果统计的对象是复合索引,那么只统计索引列第一列的值的分布情况,忽略其他索引列。

c、统计信息的直方图信息

本例的索引列是(ID,Code),那么统计的是ID 值的分布直方图:

我们接着分析第三部分,该列直方图信息,通过这块SQL Server能直观“掌控”该列的数据分布内容,我们来看

澳门新萄京官方网站 15

· RANGE_HI_KEY:直方图中每一组数据的最大值。这个好理解,如果数据量大的话,经过分组,这个值就是当前组的最大值。上面例子的统计信息总共分了90组,总共才91行,也就是说,SQL Server为了准确的描述该列的值,大部分每个组只取了一个值,只有一个组取了俩值。

分布直方图返回的数据列说明:

· RANGE_ROWS:直方图的没组数据的区间行数(不包括最大值)。这里我们说了总共就91行,它分了90组,所以有一组会存在两个值,我们找到它:

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

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

在分布直方图中,每一行都是一个范围(Range),

· DISTINCT_RANGE_ROWS:直方图每组数据区间的非重复值的数目。上限值除外。

  • 字段RANGE_HI_KEY是范围的最大值,范围的最小值大于上一条记录的最大值(RANGE_HI_KEY)。在直方图中,第一条记录是数据表的最小值,只有一条记录。
  • 字段Range_Rows表示在当前范围中,不包括最大值(RANGE_HI_KEY)的总行数。
  • EQ_Rows字段是当前范围中,等于最大值(RANGE_HI_KEY)的总行数。
  • DISTINCT_RANGE_ROWS字段表示在当前范围中,除去RANGE_HI_KEY之外的所有数据行,其唯一值的数量。
  • AVG_RANGE_ROWS字段是一个比例,当DISTINCT_RANGE_ROWS=0时,AVG_RANGE_ROWS=1;当DISTINCT_RANGE_ROWS>0时,AVG_RANGE_ROWS=Range_Rows/DISTINCT_RANGE_ROWS。

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

例如,当前范围中有(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。

经过最后一部分的描述,SQL Server已经完全掌控了该表中该字段的数据内容分布了。想获取那些数据根据它就可以从容获取到,并且统计信息是排序了的。

二,验证分布直方图数据

所以当我们每次写的T-SQL语句,它都能根据统计信息评估出要获取的数据量多少,并且找到最合适的执行计划来执行。

下图是统计对象 cix_dt_test_idcode 的分布直方图:

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

 澳门新萄京官方网站 16

当然,如果数据量特别大,统计信息的维护也会有小小的失误,而这时候就需要我们来站出来及时的弥补。

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

创建统计信息

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

通过上面的介绍,其实我们已经看到了统计信息的强大作用了,所以对于数据库来说它的重要性就不言而喻了,因此,SQL Server会自动的创建统计信息,适时的更新统计信息,当然我们可以关闭掉,但是我非常不建议这么做,原因很简单:No Do  No Die...

第二条记录,范围的最大值是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。

这两项功能默认是开启的,也就是说SQL Server会自己维护统计信息的准确性。

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

在日常维护中,我们大可不必要去更改这两项,当然也有比较极端的情况,因为我们知道更新统计信息也是一个消耗,在非常的大的并发的系统中需要关掉自动更新功能,这种情况非常的少之又少,所以基本采用默认值就可以。

三,更新统计信息

在以下情况下,SQL Server会自动的创建统计信息:

SQL Server 查询优化器使用这些统计信息来计算开销,选择最优的执行计划。查询优化器选择索引的一个标准是:索引列的选择性高,也就是说,该列的重复值少,重复率可以从直方图的Avg_Range_Rows和密度向量的All Desity字段中获取。

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

1,查看统计信息最后一次更新的时间

2、当SQL Server想要使用某些列上的统计信息,发现没有的时候,这时候会自动创建统计信息。

系统根据特定的规则更新统计信息,但是,随着数据的少量更新,数据表的统计信息不会实时更新,STATS_DATE 函数用于返回表或索引视图上统计信息的最后一次更新的日期:

3、当然,我们也可以手动创建。

STATS_DATE ( object_id , stats_id )

比如,自动创建的例子

参数stats_id是统计对象的ID,可以通过sys.stats来查看统计对象及其ID,系统视图:sys.stats_columns显式统计对象和基础表(或索引视图)的数据列之间的关系:

select * into CustomersStats from Customers

select    
    object_name(s.object_id) object_name,
    s.name as statistics_name,
    sc.stats_column_id,
    col_name(sc.object_id, sc.column_id) as column_name,
    stats_date(s.object_id,s.stats_id) as stats_last_updated_date
from sys.stats as s 
inner join sys.stats_columns as sc
    on s.stats_id = sc.stats_id 
        and s.object_id = sc.object_id
where s.object_id=object_id('table_name','U')
order by s.name;

sp_helpstats CustomersStats

用户有时需要手动更新统计信息,这可以通过UPDATE STATISTICS命令来实现:

 

update statistics dbo.dt_test [cix_dt_test_idcode]

来添加一个查询语句,然后再查看统计信息

在计算统计信息时,有多种扫描数据表的方式:

select * from CustomersStatswhere ContactName='Hanna Moos'

  • FULLSCAN:扫描所有的数据行,开销最大,计算的统计信息最精确;
  • SAMPLE number { PERCENT | ROWS }:取样本,只扫描样本数据;
  • RESAMPLE:使用最新的样本数据计算统计信息,可能会导致全表扫描;

go

SQL Server查询优化器根据统计来评估开销,生成最优的执行计划。 选择适当的扫面方式,能够及时更新统计数据,使用最小的工作负载,实现性能的最大提升。

sp_helpstats CustomersStats

UPDATE STATISTICS schema_name . table_name  { statistics_name | index_name }
WITH FULLSCAN | SAMPLE number PERCENT| RESAMPLE 

go

 

在以下情况下,SQL Server会自动的更新统计信息:

 

 1、如果统计信息是定义在普通的表格上,那么当发生以下任一种的变化后,统计信息就会被触发更新动作。

参考文档:

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

UPDATE STATISTICS (Transact-SQL).aspx)

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

DBCC SHOW_STATISTICS (Transact-SQL).aspx)

· 对于数据量大于500行的表格,当统计信息的第一个字段数据累计变化大于500 (20%*表格总的数据量)以后。所以对于较大的表,只有1/5以上的数据发生变化后,SQL Server才会重新计算统计信息。

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

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

UPDATE STATISTICS Customers WITH FULLSCAN

 

 

 

 

SQL Server调优系列进阶篇(深入剖析统计信息)

  • 指尖流淌 - 博客园.html

 

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

捕鸟王"Bird Catcher 王中之王King of Kings 虔诚者Pious 宗教信仰捍卫者 Defender of the Faith. 卡拉卡拉红斗篷 Caracalla red cloak

简称:: Emir Attilax Akbar 埃米尔 阿提拉克斯 阿克巴

全名::Emir Attilax Akbar bin Mahmud bin  attila bin Solomon Al Rapanui 

埃米尔 阿提拉克斯 阿克巴 本 马哈茂德 本 阿提拉 本 所罗门  阿尔 拉帕努伊   

常用名:艾提拉(艾龙),   EMAIL:1466519819@qq.com

转载请注明来源:attilax的专栏   

--Atiend

 

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:SQLSE宝马X五VE途观是怎麽通

关键词: