SQL Server查询性能优化——堆表、碎片与索引(二)

时间:2021-12-03 12:12:44

本文是对 SQL Server查询性能优化——堆表、碎片与索引(一)的一些总结。

 第一:先对 SQL Server查询性能优化——堆表、碎片与索引(一)中的例一的SET STATISTICS IO之后出现的关键信息如下
表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1,逻辑读 4825 次,物理读 6 次,预读 19672 次。
这句解释一下。(有些内容来自网络,有些内部是自己的理解。)
 
SQL SERVER 数据库引 擎当遇到一个查询语句时,SQL SERVER数据库引擎会分别生成执行计划(占用CPU和内存资源),同时存储引擎读取 IAM 以生成必须要读取的磁 盘地址排序列表。这使 SQL Server 得以将其 I/O 优化为大型有序读取,根据它们在磁盘上的位置按顺序完成。磁盘中取得需要取的数据(占用 I/O资源,这就是预读),注 意,两个步骤是并行的,SQL SERVER通过这种方式可以让计算和 I/O 重叠进行,从而充分利用 CPU 和磁 盘,从而提高性能。
 
扫描计数:查询数据时对涉及到的表被 访问次数或涉及到的索引的扫描次数。在我们的例子中,不管是表扫描(例一与例二)还是索引扫描(例三)其 中的表或索引都只被访问了1次,由于查询中不包括连接命令,这一信息并不是十分有用,但如果查询中包含有一个或多个连接,则这一信息是十分有用的。

  一个循环外部的表的扫描计数值为1,但对于一个循环内的表而言,其值为循环的次数。可以想象得到,对于一个循环内的表而言,其 扫描计数值越小,它所
使用的资源越少,查询的性能也就越高。因此在调节一个带连接的查询的性能时,需要关注扫描计数的值,在进行调节 时,注意观察它是增加还是减少了。

逻辑读: 这是SET STATISTICS IO或SET STATISTICS TIME命令提供的最有用的数据。我们知道,SQL Server在对任何数据进行操作前,必须首先从磁盘中读取数据所在的数据页或索引页,并把数据页或索引页存到数据缓冲区高速缓存中。

  那么逻辑读的意义是什么呢?逻辑读是指SQL Server为得到查询中的结果而必须从数据缓冲区高速缓存读取的页数。在执行查询时,SQL Server不会读取比实际需求多或少的数据, 因此,当在相同的数据集上执行同一个查询,得到的逻辑读的数字总是相同的。
  为什么说在调节查询性能中知道SQL Server执行查询时的逻辑读值是很重要的呢?因为在每次执行同一查询时,这个数值是不会变化的。因此,在进行查询性能的调节时,这是一个可以用来衡量你的调节措施是否成功的一个很好的标准。

  在对查询的性能进行调节时,如果逻辑读值下降,就表明查询使用的服务器资源减少,查询的性能有所提高。如果逻辑读值增加,则表示调节措施降低了查询的性能。在其他条件不变的情况下,一个查询使用的逻辑读越少,其效率就越高,查询的速度就越快。

物理读:在这里我要说的的东西可能初听起来有点自相矛盾,但只要反复思考,就会明白其中的真正含意。
  物理读指的是,在执行真正的查询操作前,SQL Server必须从磁盘上向数据缓冲区高速缓存中读取它所需要的数据。在SQL Server开始执
行查询前,它要作的第一件事就是检查它所需要的数据是否在数据缓冲区高速缓存中,如果在,就从中读取,如果不在,SQL Server必须首先将它需要的
数据从磁盘上读到数据缓冲区高速缓存中。
  我们可以想象得到,SQL Server在执行物理读时比执行逻辑读需要更多的服务器资源。因此,在理想情况下,我们应当尽量避免物理读操作。

  下面的这一部分听起来让人容易感到糊涂了。在对查询的性能进行调节时,可以忽略物理读而只专注于逻辑读。你一定会纳闷儿,刚才不是还说物理读比逻辑读需要更多的服务器资源吗?
  情况确实是这样,SQL Server在执行查询时所需要的物理读次数不可能通过性能调节而减少的。减少物理读的次数是DBA的一项重要工作,但它涉
及到整个服务器性能的调节,而不仅仅是查询性能的调节。在进行查询性能调节时,我们不能控制数据缓冲区高速缓存的大小或服务器的忙碌程度以及完成查询所需
要的数据是在数据缓冲区中还是在磁盘上,唯一我们能够控制的数据是得到查询结果所需要执行的逻辑读的次数。

  因此,在查询性能的调节中,我们可以心安理得地不理会SET STATISTICS IO命令提供的物理读的值。(减少物理读次数、加快SQL Server运行速度的一种方式是确保服务器的物理内存足够多。)

预读:与 物理读一样,这个值在查询性能调节中也没有什么用处。预读表示SQL Server在执行预读机制时从磁盘上读取的数据页或索引页。为了优化其
性能,SQL Server数据引擎首先预测执行查询执行计划所需的数据和索引页,然后在查询实际使用这些页之前将它们读入缓冲区高速缓存。根据
SQL Server对数据 需求预测的准确程度,预读的数据页可能有用,也可能没用。
备注:一个缓冲区就是一个 8KB 大小的内存页
 
以上文字大部分来源自网络,本人对其中部分不认同处,或有自己的理解处,做了少量修改。
 
第二:关于碎片对于性能影响的结论:
SQL Server 中数据存储的基本单位是页,一页包含8KB数据。磁盘 I/O 操作在页级执行。也就是说,SQL Server 读取
或写入的基本单位是数据页。连续的8个页面组成一个区(extent)。数据的insert和update操作可以引起页面分割产生碎片。如果分割后的两
个页面在同一个区内,这种碎片称为内部碎片,如果分割后的两个页面处于不同的区内,这种碎片称为外部碎片。
 
一、内部碎片和外部碎片对数据检索性能都有负面影响。
1. 内部碎片的产生使数据稀疏的分布在大量的页面中,这增加了读取页面到内存中所需的磁盘I/O操作,增加了从内存中检索数据的逻辑读取数量。
 
2. 外部碎片导致磁盘上的索引页面不连续,新的叶子页面和原始叶子页面离得很远,物理顺序与逻辑顺序不同。为了更好的性能,首选顺序I/O,
因为这能在一个磁盘I/O读取整个区(8个8KB页面)。非连续的页面需要非顺序或者随机I/O操作来从磁盘读取数据,一个随机I/O只能读取一个页面
(8KB)。
 
二、在堆表中,当删除数据链中间的记录行时,会出现空页,随着空页的累积,区的利用率也会下降,从而出现内部碎片与外部碎片。带索引的表也有可
能出现外部碎片,如在现有的聚集索引中插入一行,这行正好导致现有的页空间无法满足容纳新的行,从而导致了分页,如果分页后的两个页面正好分布在两个区,
就是外部碎片。当有外部碎片存在,会出现以下问题:对表进行处理时,常常出现死锁;利用较大的I/O操作或增加I/O缓冲区的大小也无法改变较慢的I/O
速度;行操作的争用。
 
三、带有索引的表会由于插人记录而导致分页,但当删除记录后,页会获得释放.从而形成跨几个区的数据.而要访问该数据就必须遍历几个区,这将导
致增加I/O操作,查询记录的时间大大延长,开始时数据库的性能虽然较高,但使用一段时间后就会发生性能下降等问题。实际上,数据在存储空间上排列得越紧
密有序,SQL Server访问的速度就越抉,消除碎片有助于提高系统的性能和更有效地利用数据存储空间。(如例二,例二的访问速度就比例一要快)。
 

四、对于扫描部分或全部表的查询,这些表碎片会导致额外的页读取,这将防碍数据的并行扫描。

SQL Server查询性能优化——堆表、碎片与索引(二)的更多相关文章

  1. SQL Server查询性能优化——堆表、碎片与索引(一)

    SQL Server在堆表中查询数据时,是不知道到底有多少数据行符合你所指定的查找条件,它将根据指定的查询条件把数据表的全部数据都查找 一遍.如果有可采用的索引,SQL Server只需要在索引层级查 ...

  2. SQL Server 查询性能优化 相关文章

    来自: SQL Server 查询性能优化——堆表.碎片与索引(一) SQL Server 查询性能优化——堆表.碎片与索引(二) SQL Server 查询性能优化——覆盖索引(一) SQL Ser ...

  3. SQL SERVER 查询性能优化——分析事务与锁(五)

    SQL SERVER 查询性能优化——分析事务与锁(一) SQL SERVER 查询性能优化——分析事务与锁(二) SQL SERVER 查询性能优化——分析事务与锁(三) 上接SQL SERVER ...

  4. Sql Server查询性能优化之走出索引的误区

    据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是, ...

  5. SQL Server查询性能优化——覆盖索引(二)

    在SQL Server 查询性能优化——覆盖索引(一)中讲了覆盖索引的一些理论. 本文将具体讲一下使用不同索引对查询性能的影响. 下面通过实例,来查看不同的索引结构,如聚集索引.非聚集索引.组合索引等 ...

  6. SET STATISTICS IO和SET STATISTICS TIME 在SQL Server查询性能优化中的作用

    近段时间以来,一直在探究SQL Server查询性能的问题,当然也漫无目的的查找了很多资料,也从网上的大神们的文章中学到了很多,在这里,向各位大神致敬.正是受大神们无私奉献精神的影响,所以小弟也作为回 ...

  7. Sql Server查询性能优化之不可小觑的书签查找

    小小程序猿SQL Server认知的成长 1.没毕业或工作没多久,只知道有数据库.SQL这么个东东,浑然分不清SQL和Sql Server Oracle.MySql的关系,通常认为SQL就是SQL S ...

  8. SQL Server 查询性能优化——创建索引原则

    索引是什么?索引是提高查询性能的一个重要工具,索引就是把查询语句所需要的少量数据添加到索引分页中,这样访问数据时只要访问少数索引的分页就可以.但是索引对于提高查询性能也不是万能的,也不是建立越多的索引 ...

  9. SQL Server查询性能优化——创建索引原则(一)

    索引是什么?索引是提高查询性能的一个重要工具,索引就是把查询语句所需要的少量数据添加到索引分页中,这样访问数据时只要访问少数索引的分页 就可以.但是索引对于提高查询性能也不是万能的,也不是建立越多的索 ...

随机推荐

  1. PAT 1005. 继续(3n+1)猜想 (25) JAVA

    当我们验证卡拉兹猜想的时候,为了避免重复计算,可以记录下递推过程中遇到的每一个数.例如对n=3进行验证的时候,我们需要计算3.5.8.4.2.1,则当我们对n=5.8.4.2进行验证的时候,就可以直接 ...

  2. Objective-C语言分类与协议

    分类(Category)允许向一个类文件中添加新的方法声明,它不需要使用子类机制,并且在类实现的文件中的同一个名字下定义这些方法.其语法举例如下: #import "ClassName.h& ...

  3. msmms (一) sms与mms区别

    sms与mms区别 SMS,Short Messaging Service短信业务,可以通过手机等移动设备发送文本型短信.SMS这个术语最早在80年代初期出现,但一直到了90年代初才开始进入商用市场, ...

  4. Data Flow ->> Pivot

    这个组件和SQL Server的Pivot基本就是一回事.不过我观察到SSIS的Pivot只支持SUM这种聚合类型,并没有找到哪个选择可以设置聚合类型,而T-SQL语法的Pivot则支持多种聚合类型, ...

  5. 写了几天的博客-feel

    写博客 真的总结我自己的知识.长见识了.记录下自己遇到的东西,算是一个总结,有问题了,还可以回头看一下.很好 真的很好.

  6. 编译Android源代码与内核总结

    这些天花了些时间自己下载了android源代码来编译,当中走了一些弯路导致耗了些时间,如今又一次梳理总结下,让有同样想法的人自己编译的时候能少走些弯路,官方指导文档在http://source.and ...

  7. c#常用的预处理器指令

    预处理器指令指导编译器在实际编译开始之前对信息进行预处理.所有的预处理器指令都是以 # 开始. #define 预处理器指令创建符号常量.#define 允许您定义一个符号,这样,通过使用符号作为传递 ...

  8. Swift2.0语言教程之类的属性

    Swift2.0语言教程之类的属性 类 虽然函数可以简化代码,但是当一个程序中出现成百上千的函数和变量时,代码还是会显得很混乱.为此,人们又引入了新的类型——类.它是人们构建代码所用的一种通用.灵活的 ...

  9. eclipse软件与git配合使用创建git仓库

    一.在eclipse上安装git,和安装其他插件一样 help->Install new software->add... 在弹出框中输入name:git,location:http:// ...

  10. 【源码学习之spark core 1.6.1 各种部署模式所使用的的TaskSceduler及SchedulerBackend】

    说明:个人原创,转载请说明出处 http://www.cnblogs.com/piaolingzxh/p/5656879.html 未完待续 未完待续