来自 数据库 2019-10-14 01:28 的文章
当前位置: 澳门三合彩票 > 数据库 > 正文

该部分含有以下几列,server在高速查询值时唯有

一.概述  

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

 

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

 

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

二. 统计信息分析

--查询统计信息
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如何了解该列数据的内容分布的。

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

该部分包含以下几列:

· Name:统计信息的名称。

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

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

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

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

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

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

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

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

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

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

 

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

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

该分值的计算公式为:density=1/表中非重复的行数。所以该稠密度值取值范围为:0-1。

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

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

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

Average Length:索引的平均长度。

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

本文由澳门三合彩票发布于数据库,转载请注明出处:该部分含有以下几列,server在高速查询值时唯有

关键词: