谁创建和管理统计信息?在性能调优中,统计信息的作用。

谁创建和管理统计信息?在性能调优中,统计信息的作用。

大家好,又见面了,我是全栈君。

有2类统计信息,索引统计信息和列统计信息。索引统计信息是索引创建的一部分(建立索引会自动创建索引统计信息)。在where条件列被引用或查询的group by子句里包含列,列统计信息都会由SQL Server自动创建。

有数据库属性设置里,可以设置数据库是否自动创建统计信息并自动更新统计信息(数据库属性->选项->自动)。

谁创建和管理统计信息?在性能调优中,统计信息的作用。

自动创建统计信息默认是启用的,它帮助查询优化器在需要更好的进行查询预估时,创建列统计信息。为了更好的性能,建议保留启用。

自动更新统计信息默认也是启用的,它帮助查询优化器在统计信息过期的时候自动更新。当数据有明显变化时,统计信息就需要更新。这里有个阀限(threshold limit)来标记统计信息是否过期。

自动异步更新统计信息默认是不启用的。当自动异步更新统计信息被启用的时候,会有2种方式进行自动更新。异步模式(默认模式),如果统计信息已经过期,查询优化器会等到计划生成完成才更新统计信息。同步模式,查询优化器会初始化统计信息,不会等到计划的生成完成。通过改变更新统计信息为同步模式可以使性能上一些工作量始终受益。SQL Server在自动创建/更新统计信息的时候,不会进行完全扫描。它只会在可接受的时间内采样数据来计算统计信息。

理解统计信息(1/6):密度里,我们看到,当引用的列在group by或where条件里时,统计信息会自动创建。我们来看看当自动创建统计信息关掉的时候,SQL Server如何进行预估。我们运行下面的语句并看看输出结果。 

复制代码
1 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS OFF
2 GO
3 DROP TABLE SalesOrderDetail_NoStats
4 SELECT * INTO SalesOrderDetail_NoStats FROM AdventureWorks2008r2.Sales.SalesOrderDetail
5 GO
6 SELECT ProductID,COUNT(*) FROM dbo.SalesOrderDetail_NoStats GROUP BY ProductID
7 GO
8 EXEC SP_HELPSTATS 'SalesOrderDetail_NoStats', 'ALL'
复制代码

 第1句,我们关掉了StatisticsDB数据库的自动更新统计信息。第2句,我们创建了salesOrderDetail表的副本。现在我们对ProductId进行group 扮演操作,点击工具栏的谁创建和管理统计信息?在性能调优中,统计信息的作用。显示包含实际的执行计划。

谁创建和管理统计信息?在性能调优中,统计信息的作用。

 在执行计划里,我们在表扫描运算符里看到一个黄色的惊叹号。具体来说,它是警告我们没有可用的统计信息。在聚集运算符里,我们看到优化器的估计行数是348.306。没有统计信息,优化器要如何估计行数呢? 这里的值是拿记录总数开方而来。这个表有121317条记录,如果你对它开方,即谁创建和管理统计信息?在性能调优中,统计信息的作用。,我们就得到348.306。如果你对这个表group by的其他任何列,预估行数还是一样的。 请注意,自动创建统计信息只控制列统计信息的自动创建。它不控制索引创建时,统计信息的自动创建。

我们来看下统计信息如何影响查询性能。来看下面2个查询,记得在最后的查询语句执行前点击工具栏的谁创建和管理统计信息?在性能调优中,统计信息的作用。显示包含实际的执行计划。

复制代码
 1 USE StatisticsDB
 2 GO
 3 /* Part 1 WITH AUTO STATS UPDATE ON */
 4 
 5 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS ON
 6 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS ON
 7 SET STATISTICS IO ON
 8 DROP TABLE SalesOrderDetail_NoStats 
 9 SELECT * INTO SalesOrderDetail_NoStats FROM SalesOrderDetail
10 CREATE INDEX ix_productid  ON SalesOrderDetail_NoStats (productid)
11 UPDATE dbo.SalesOrderDetail_NoStats SET ProductID=775 WHERE SalesOrderDetailID<>1
12 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=776
13 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=775
14 
15 /* Part 2  WITH AUTO STATS UPDATE Off */
16 
17 ALTER DATABASE StatisticsDB SET AUTO_CREATE_STATISTICS OFF
18 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS OFF
19 SET STATISTICS IO ON
20 DROP TABLE SalesOrderDetail_NoStats 
21 SELECT * INTO SalesOrderDetail_NoStats FROM SalesOrderDetail
22 CREATE INDEX ix_productid  ON SalesOrderDetail_NoStats (productid)
23 UPDATE dbo.SalesOrderDetail_NoStats SET ProductID=775 WHERE SalesOrderDetailID<>1
24 --Disabling the auto update stats
25 ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS OFF
26 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=776
27 SELECT * FROM dbo.SalesOrderDetail_NoStats WHERE ProductID=775
复制代码

上述2组语句我们都在productid列创建了索引(统计信息也会自动创建),然后我们更新productid为775,只留1条还是不同的productid值。更新后,表里只有2个不同的productid值775和776。第1组语句,我们进行了自动更新统计信息启用的SELECT查询。第2组语句我们进行了自动更新统计信息停用的SELECT查询。我们来看看2者执行计划和IO统计信息的不同。

谁创建和管理统计信息?在性能调优中,统计信息的作用。

我们来看看启用的执行计划。第1个where条件是productid=776的查询估计行数是1,000348,优化器进行的是索引查找。第2个where条件是productid=775的查询估计行数是121316,优化器选择的是表扫描,而不是非聚集索引查找和书签查找。对优化器来说表扫描更有效,相比使用索引查找和书签查找来获取表里的所有记录(只有一条记录productid是776)。完成这个操作只需要1495个逻辑读。

谁创建和管理统计信息?在性能调优中,统计信息的作用。

我们来看看停用的执行计划。第1个where条件是productid=776的查询估计行数是228,优化器进行的是索引查找。这个是基于索引创建是的统计信息来预估的,这个信息在update后已经过期了。第2个where条件是productid=775的查询估计行数是234,这就严重误导了查询优化器使用了非聚集扫描和书签查找来操作,而不是表扫描来获取表的所有记录(只有一条记录productid是776)。完成这个操作需要121710个逻辑读,相比启用情况下仅1495个逻辑读是非常非常高了。

从上面的例子,我们清楚的看到优化器需要更新的统计信息来选择最优执行计划,即使你有了必须的索引。在处理性能问题时,我们也需要关注下统计信息。把估计行数与实际行数的区别当作一个好指标,用来深入了解下统计信息,或统计信息的人为更新。

自动更新统计信息可以在以下3个级别进行关闭:

  • 数据库级别,使用修改数据库命令:ALTER DATABASE StatisticsDB SET AUTO_UPDATE_STATISTICS OFF
  • 索引级别,在创建或重建索引时使用STATISTICS_NORECOMPUTE 选项。这有点令人迷惑。这个选项默认是关闭的。就是说自动更新属性是启用的。
  • 统计信息级别,当创建或更新统计信息时使用NORECOMPUTE 选项。

使用sp_autostats 存储过程可以查看表的对应统计信息的自动更新统计信息设置情况。如果在数据级别设置自动更新统计信息为停用,那表级别也会停用。可以使用sp_autostats存储过程修改表级别的自动更新统计信息设置情况。



本文转自Woodytu博客园博客,原文链接:http://www.cnblogs.com/woodytu/p/4519477.html,如需转载请自行联系原作者

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/107953.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 做高通平台安卓驱动感言

    做高通平台安卓驱动感言

  • Java xml数据格式返回实现

    Java xml数据格式返回实现前言:对于服务器后端开发,实现的大部分接口,返回的数据格式一般要求都是json,但是也有使用xml格式的网上有多种实现方式,这里使用注解的方式把java类使用@RequestBody实现Java对象和XML/JSON数据自动转换很多人会认为接口方法使用@Controller搭配@ResponseBody和@RequestMapping注解后,java对象会转换成json格式返回。但实际上配…

  • C语言中从键盘输入字符串时的一些问题[通俗易懂]

    C语言中从键盘输入字符串时的一些问题[通俗易懂]C语言中从键盘输入字符串时的一些问题1.scanf()scanf()在输入字符串时有很大的弊端,例如:1).scanf()在从键盘读入字符时并不会根据所定义的字符数组的大小来控制读入多少个,而是从scanf()中传入的地址开始一直访问下一个元素的内存,碰见空格符或者回车符时才停止读入并存入结束符’\0’,这就有可能造成了一个在C中非常严重的问题,访问非法内存.如果…

  • pyqt5开发的漂亮界面_pyqt5美化包

    pyqt5开发的漂亮界面_pyqt5美化包今天闲来无事,用python写了一个抽奖程序pyqt版本:pyqt5python版本:3.6.6下面直接上代码,有建议的话,欢迎大家交流评论!#-*-coding:utf-8-*-#Formimplementationgeneratedfromreadinguifile’Lucky_draw.ui’##Createdby:PyQt5UIcodegenerator5.15.1##WARNING:Anymanualchangesmadeto

  • JavaScript代码规范

    JavaScript代码规范1111

  • 零拷贝技术_基因单拷贝

    零拷贝技术_基因单拷贝零拷贝技术概述零拷贝技术指在计算机执行操作时,CPU不需要先将数据从一个内存区域复制到另一个内存区域,从而可以减少上下文切换以及CPU的拷贝时间。它的作用是在数据报从网络设备到用户程序空间传递的过程中,减少数据拷贝次数,减少系统调用,实现CPU的零参与,彻底消除CPU的负载。实现零拷贝用到的主要技术是DMA数据传输技术和内存区域映射技术零拷贝机制可以减少数据在内核缓冲区和用户进程缓冲区之间反复的I/O拷贝操作零拷贝机制可以减少用户进程地址空间之间因为上下文切换而带来的CPU开销物理内存和虚拟

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号