SQLServer统计信息理解分析Word文件下载.docx
- 文档编号:19651664
- 上传时间:2023-01-08
- 格式:DOCX
- 页数:22
- 大小:626.29KB
SQLServer统计信息理解分析Word文件下载.docx
《SQLServer统计信息理解分析Word文件下载.docx》由会员分享,可在线阅读,更多相关《SQLServer统计信息理解分析Word文件下载.docx(22页珍藏版)》请在冰豆网上搜索。
column_name
sc.stats_column_id
3.FROM
s
4.INNER
JOIN
sys.stats_columns
sc
ON
s.object_id
=
sc.object_id
AND
s.stats_id
sc.stats_id
5.INNER
sys.columns
c
c.object_id
c.column_id
sc.column_id
6.WHERE
OBJECT_ID(N'
);
7.
8.--查看所有统计信息更新时间
9.exec
sp_helpstats
N'
'
ALL'
统计信息的属性:
右键统计信息,选择“属性”,可看到统计信息的设置和分布。
1.还可以使用命令DBCC
SHOW_STATISTICS查看,以下为列。
2.DBCC
SHOW_STATISTICS('
'
IX_SalesOrderDetail_ProductID'
主要分为三部分,分别为“统计信息头部”,“密度向量”,“直方图”
1
统计信息头信息
列名
说明
Name
统计信息对象的名称(IX_SalesOrderDetail_ProductID)
Updated
上一次更新统计信息的日期和时间(Mar142012
1:
14PM)
Rows
上次更新统计信息时表或索引视图中的总行数(121317)。
如果筛选统计信息或者统计信息与筛选索引对应,该行数可能小于表中的行数
RowsSampled
用于统计信息计算的抽样总行数(121317)。
如果RowsSampled<
Rows,显示的直方图和密度结果则是根据抽样行估计的。
Steps
直方图中的梯级数(200)。
每个梯级都跨越一个列值范围,后跟上限列值。
直方图梯级是根据统计信息中的第一个键列定义的。
最大梯级数为200。
Density
计算公式为:
1/统计信息对象第一个键列中的所有值(不包括直方图边界值)的
distinctvalues。
(0.0078125)
查询优化器不使用此Density值,显示此值的目的是为了与SQLServer2008之前的版本实现向后兼容。
AverageKeyLength
统计信息对象中所有键列的每个值的平均字节数
(12:
3个int类型。
ProductID,SalesOrderID,SalesOrderDetailID)
StringIndex
(NO)Yes指示统计信息对象包含字符串摘要统计信息,以改进对使用LIKE运算符的查询谓词的基数估计;
例如
WHEREProductNameLIKE'
%Bike'
。
字符串摘要统计信息与直方图分开存储,如果统计信息对象为char、varchar、nchar、nvarchar、varchar(max)、nvarchar(max)、text
或
ntext.
类型,则基于其第一个键列创建字符串摘要统计信息。
FilterExpression
包含在统计信息对象中的表行子集的谓词。
NULL=未筛选的统计信息。
UnfilteredRows
应用筛选表达式前表中的总行数(121317)。
如果FilterExpression为NULL,则UnfilteredRows等于Rows。
2
密度信息
AllDensity
Density为1/distinctvalues。
结果显示统计信息对象中各列的每个前缀的密度,每个密度显示一行。
非重复值是每个行前缀和列前缀的列值的非重复列表。
反映索引列的选择性(selectivity)
"
选择性"
反映数据集里重复的数据量是多少,或者反过来说,值唯一的数据量有多少。
如果一个字段的数据很少有重复,那么他的可选择性就比较高。
比如身份证号,是不可重复的。
哪怕对整个中国的身份记录做查询,代入一个身份证号码最多只会有一条记录返回,在这样的字段上的过滤条件,能够有效地过滤掉大量数据返回的结果集会比较小举个相反的例子:
性别。
所有人只有两种,非男即女。
这个字段上的重复性就很高选择性就很低。
一个过滤条件,最多只能过滤掉一半的记录SQL通过计算“选择性”,使得自己能够预测一个过滤条件做完后,大概能有多少记录返回
Density的定义是:
density=1/cardinalityofindexkeys如果这个值小于0.1,一般讲这个索引的选择性比较高,如果大于0.1,他的选择性就不高了。
(参考《Microsoftsqlserver企业级平台管理实践》)
AverageLength
存储列前缀的列值列表的平均长度(以字节为单位)。
Columns
为其显示Alldensity和Averagelength的前缀中的列的名称
这里至于为什么会有3行,是因为【ProductID】为非聚集索引,【SalesOrderID,SalesOrderDetailID】为聚集索引,而每个非聚集索引中都包含有聚集索引的键值,所以这里的统计信息也出现了3个可选项。
当前统计信息
[AllDensity]
计算方法:
1.select
count(*)
(select
a
[Sales].[SalesOrderDetail]group
by
ProductID
as
T
[Sales].[SalesOrderDetail]
group
ProductID,SalesOrderID)
3.select
ProductID,SalesOrderID,SalesOrderDetailID)
4.
5.--按不同组统计如下:
6.group
--266行
7.group
ProductID,
SalesOrderID
--121317行
8.group
SalesOrderID,
SalesOrderDetailID
9.
10.select
1.0/266
[all
density]
11.union
all
12.select
1.0/121317
13.union
14.select
直方图
RANGE_HI_KEY
直方图梯级的上限列值。
列值也称为键值。
(按ProductID
的范围分布)
RANGE_ROWS
其列值位于直方图梯级内(不包括上限)的行的估算数目。
(2个ProductID值之间有多少行)
EQ_ROWS
其列值等于直方图梯级的上限的行的估算数目。
(等于当前行ProductID值的有多少行)
DISTINCT_RANGE_ROWS
非重复列值位于直方图梯级内(不包括上限)的行的估算数目。
(2个ProductID值之间有多少不重复的键值ProductID)
AVG_RANGE_ROWS
重复列值位于直方图梯级内(不包括上限)的平均行数(如果DISTINCT_RANGE_ROWS>
0,则为
RANGE_ROWS/DISTINCT_RANGE_ROWS)。
统计信息的重要性:
SQLServer中,在执行一个批处理语句时,关系引擎中的查询优化器会先估计生成较优的执行计划,执行执行器才安照此执行计划请求数据。
即在生成执行计划期间,sqlserver是根据表中的统计信息进行行数估计,按照脚本语义来确定物理操作步骤生成执行计划,再按照该执行计划访问数据。
而对于数据较大的表,按照统计信息估计的行数也常常不准确,这就是使查询使用了不准确的执行计划而比较慢。
类似如:
“参数嗅探”因传递参数值无法确定而估算错误;
使用表变量不会有统计信息也不会估算行数。
1.--现在以这个表的列统计为例[Sales].[SalesOrderDetail](SpecialOfferID)
_WA_Sys_0000000B_44CA3770'
如果查询日期范围在'
2005-07-01'
<
ModifiedDate<
='
2005-08-01'
看上图,查询返回的估计行数应该为896.7728(190.2021+706.5707)
1.SELECT
COUNT(
ModifiedDate
)FROM
2.WHERE
>
2005-07-01
00:
00'
2005-08-01
估计行数为896.773,与统计信息的直方图的信息一致。
其实就是根据直方图统计出来的,如果估计行数不准确,一定是统计信息没有正确的直方图信息,因此需要更新统计信息。
在看带参数的测试:
COUNT(*)
FROM
WHERE
ProductID=800
估计行数是495,是直方图里显示ProductID=800的估计。
现在使用参数替换。
1.DECLARE
@ProductID
INT
2.SET
800
3.SELECT
ProductID=@ProductID
看到估计行数是456.079,这个估计与实际的相差不大,不影响执行计划改变。
但是为什么又变了呢?
这个怎么来的?
下面解释
1.DBCC
ProductID=800的估计行数是495,而使用参数的是456.079,统计信息中并没有记录,但是SqlServer却能根据密度计算。
看红框中的数值,因为我是以ProductID为谓词,因此选择了密度
Alldensity=
0.003759399,估计行数为:
[EstimateRows]=121317*
0.003759399
456.079008483
参数估计公式:
[EstimateRows]
=Rows*[Alldensity]
有时候即使更新了统计信息,结果还是一样,因为数据量太大,估计数据不完全,看RowsSampled可知道,因此也可以在更新统计信息时采用全表行数统计,但是这样扫描表数据也耗性能。
即便如此,还是有些可能不一样,因为直方图的步长最多200,数据列中相同的和不同的差距太大,200段分布也有参差不齐的数据,不能使用更多步更详细的数据直方图。
统计信息的更新设置:
Sqlserver默认自动维护统计信息,在数据库级别可以设置自动创建和更新统计信息的选项。
1.用脚本设置如下:
2.ALTER
DATABASE
[databaseName]
SET
AUTO_CREATE_STATISTICS
WITH
NO_WAIT
3.ALTER
AUTO_UPDATE_STATISTICS
4.ALTER
AUTO_UPDATE_STATISTICS_ASYNC
AUTO_CREATE_STATISTICS:
AUTO_CREATE_STATISTICS=ON时,当将某列作为条件查询时,系统自动为每个条件列创建单列的统计信息。
创建索引时也会自动创建相应的统计信息.查询优化器通过使用AUTO_CREATE_STATISTICS选项创建统计信息时,统计信息名称以_WA开头。
AUTO_UPDATE_STATISTICS:
AUTO_UPDATE_STATISTICS=ON时,查询优化器将确定统计信息何时可能过期,然后在查询使用这些统计信息时更新它们。
统计信息将在插入、更新、删除或合并操作更改表或索引视图中的数据分布后过期。
查询优化器通过计算自最后统计信息更新后数据修改的次数并且将这一修改次数与某一阈值进行比较,确定统计信息何时可能过期。
该阈值基于表中或索引视图中的行数。
查询优化器在编译查询和执行缓存查询计划前,检查是否存在过期的统计信息。
在编译某一查询前,查询优化器使用查询谓词中的列、表和索引视图确定哪些统计信息可能过期。
在执行缓存查询计划前,数据库引擎确认该查询计划引用最新的统计信息。
AUTO_UPDATE_STATISTICS_ASYNC:
异步统计信息更新选项AUTO_UPDATE_STATISTICS_ASYNC将确定查询优化器是使用同步统计信息更新还是异步统计信息更新。
默认情况下,异步统计信息更新选项被关闭,并且查询优化器以同步方式更新统计信息。
AUTO_UPDATE_STATISTICS_ASYNC选项适用于为索引创建的统计信息对象、查询谓词中的单列以及使用
CREATESTATISTICS
语句创建的统计信息。
统计信息更新可以是同步(默认设置)或异步的。
对于同步统计信息更新,查询将始终用最新的统计信息编译和执行;
在统计信息过期时,查询优化器将在编译和执行查询前等待更新的统计信息。
对于异步统计信息更新,查询将用现有的统计信息编译,即使现有统计信息已过期。
如果在查询编译时统计信息过期,查询优化器可以选择非最优查询计划。
在异步更新完成后编译的查询将从使用更新的统计信息中受益。
统计信息自动维护更新:
Sqlserver之所以自动维护统计信息,首先设置AUTO_UPDATE_STATISTICS=ON,sqlserver会在符合某条件时自动更新表中的统计信息。
其中我们可以看到的,系统表sysindexes的列rowmodctr,它记录自上次更新统计信息后插入、删除、更新行的累计总次数。
对于满足统计信息更新的条件,系统会自动更新。
SELECT
name,rows,rowmodctrFROMsys.sysindexes
自动更新统计规则:
•表中行范围rows=0行增长rows>
0行;
•表中行范围
0<
rows<
=500行,只要变化的次数rowmodctr>
500;
•表中行范围rows>
500行,只要变化的次数rowmodctr>
500+20%rows;
•临时表行数rows<
6,只要变化的次数rowmodctr>
6;
需要手动更新统计信息:
查询执行时间很长。
在升序或降序键列上发生插入操作。
在维护操作后。
1.--创建测试表
2.create
table
test(id
int
identity(1,1),name
char(20),value
numeric(18,4),meno
varchar(50))
3.
4.create
clustered
index
IX_test
on
test(name)
5.
6.alter
test
add
constraint
PK_test
primary
key
nonclustered(id)
8.
9.--以[dbo].[test]表为例,先查看
i.name,rows,rowmodctr,stats_date(s.object_id,s.stats_id)
update_date
11.from
sys.sysindexes
i
inner
join
i.name=s.name
12.where
OBJECT_ID('
[dbo].[test]'
13.
14.--此时观看两个索引的直方图,什么都没有
15.DBCC
IX_test'
16.DBCC
PK_test'
17.
18.--插入1行数据,统计信息没有更新?
19.insert
into
test(name,value,meno)
20.select
name'
0,'
meno'
1.--最终增删了506*2行,统计信息都没有生成
2.insert
4.go
500
6.delete
1.--重新测试:
重新删除创建表。
在插入数据前,每个字段搜索一次,非索引字段会自动生成统计信息.
id=1
name='
4.select
value=0
5.select
meno='
1.--插入一行数据,否则操作任何列统计信息都没有更新
6.--上面的查询并没有自动更新统计信息,只有作为where条件的更改或删除了才更新统计信息
7.update
set
8.update
9.update
10.delete
1.--再重新插入数据,准备测试用
5.--查看统计情况
6.select
7.from
8.where
1.--当我更新索引的统计信息到
rowmodctr
行的时候,统计信息并没有更新
2.update
3.go
497
1.--z再更新一次,使索引更新累计rowmodctr
501行
1.结果发现:
索引的统计信息更新了,rowmodctr重新设置为1行。
按相同的方法更新value为501次,非键列是没有更新的!
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLServer 统计 信息 理解 分析