快捷搜索:

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

本文是对 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造访的速率就越抉,打消碎片有助于前进系统的机能和更有效地使用数据存储空间。(如例二,例二的造访速率就比例一要快)。

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

2. SQL查询语句与查询履行计划资源

--查询语句:

SET STATISTICS IO on

go SET STATISTICS TIME on

go

select* from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'

go SET STATISTICS IO off

go

SET STATISTICS TIME off go

3.查询所必要的光阴与I/O

SQL Server 履行光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 16 毫秒,消费光阴 = 76 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

(所影响的行数为 13 行)

表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1,逻辑读 4825 次,物理读 6 次,预读 19672 次。

SQL Server 履行光阴:

CPU 光阴 = 47 毫秒,消费光阴 = 10544 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 履行光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

二、表低度碎片化

1. 此表的碎散播信息,从下图中可以看出此表的有异常多的内部碎片与外部碎片。

a) 此表的匀称页密度只有97%,也便是说数据差不多把一个数据页都塞满了,没有多余的空间,没有内部碎片。

b) 此表的扫描密度只有98%,也便是说理论上的区的数量与实现上区的数量之比为1:1,也便是说基础上没有外部碎片,也便是说每个区的使用率相称高。

如下图。

备注:对付上图中的一些字段阐明,见(一)。

2.SQL查询语句与查询履行计划资源

--查询语句:

SET STATISTICS IO on

go SET STATISTICS TIME on

go

select* from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'

go SET STATISTICS IO off

go

SET STATISTICS TIME off go

3.查询所必要的光阴与I/O

SQL Server 履行光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 92 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

(所影响的行数为 13 行)

表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1,逻辑读 1205 次,物理读 0 次,预读 1209 次。

SQL Server 履行光阴:

CPU 光阴 = 16 毫秒,消费光阴 = 390 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 履行光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

阐明:逻辑读取的数值十分靠近数据库中数据页数字,预读的次数也十分靠近数据页的数字,物理读取值为0,即所必要查询的数据整个在预读的数据中心。

三、表添加主键,我们看一下有索引的查询

1. 此表的碎散播信息,从下图中可以看出此表的有异常多的内部碎片与外部碎片。

a) 此表的匀称页密度只有97%,也便是说数据差不多把一个数据页都塞满了,没有多余的空间,没有内部碎片。

b) 此表的扫描密度只有98%,也便是说理论上的区的数量与实现上区的数量之比为1:1,也便是说基础上没有外部碎片,也便是说每个区的使用率相称高。

如下图:

备注:对付上图中的一些字段阐明,见(一)。

2.SQL查询语句与查询履行计划资源

--查询语句:

SET STATISTICS IO on

go SET STATISTICS TIME on

go

select* from T_EPZ_INOUT_ENTRY_DETAIL where entry_apply_id='31227000034000090169'

go SET STATISTICS IO off

go

SET STATISTICS TIME off go

3.查询所必要的光阴与I/O

SQL Server 履行光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 98 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

(所影响的行数为 13 行)

表 'T_EPZ_INOUT_ENTRY_DETAIL'。扫描计数 1,逻辑读 3 次,物理读 2 次,预读 0 次。

SQL Server 履行光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 30 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 履行光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

SQL Server 阐发和编译光阴:

CPU 光阴 = 0 毫秒,消费光阴 = 0 毫秒。

对照以上三者的各类关键值,就可以看出机能的提升程度。

物理操作

逻辑操作

I/O资源

CPU资源

资源

子树资源

逻辑读

物理读

预读

例一:堆表高度碎片化

Table Scan 逻辑运算符和物理运算符检索 Argument 列内指定表中的所有行

同左

3.61

0.0342

3.645123

3.65

4825

6

19672

例二:堆表低度碎片化

同上

同左

0.464

0.0171

0.963642

0.963

1205

0

1209

例三:表(带主键)

Clustered Index Seek 逻辑运算符和物理运算符使用索引的查找能力从凑集索引中检索行

同左

0.0032

0.000086

0.003289

0.00328

3

2

0

例一/例二/例三

1128/145/1

397/198/1

1108/292/1

1112/293/1

1608/401/1

3/0/1

16/1/0

对表中列的阐明:

物理操作:应用的物理运算符,例如 Hash Join 或 Nested Loops。

逻辑操作:与物理运算符匹配的逻辑运算符,如 Join 运算符。

I/O 资源:用于操作的所有 I/O 活动的估计资源。该值应尽可能低。

CPU 资源:用于操作的所有 CPU 活动的估计资源。

资源:查询优化器履行此操作的资源,包括此操作的资源占查询总资源的百分比。因为查询引擎选择最高效的操作履行查询或履行语句,是以该值应尽可能低。

子树资源:查询优化器履行此操作及同一子树内位于此操作之前的所有操作的总资源。

在本文的三个例子中,预读值最高的为19672,最低的为0,物理读的值最高为6,最低为0,而逻辑读的值最高为4825,最低为3。

那么我在办事器上 履行查询时的历程是怎么样的呢?以例一为例。

首先,SQL Server会开始反省完成查询所必要的数据是否在数据缓冲区中,它会很快地发明这些数据不在数据缓冲区中, 并启动预读机制将它所必要的数据页读取到数据缓冲区中,然则因为数据页碎片严重环境,必要多次切区,大年夜大年夜提升了I/O的耗损,如例一中读取19672次,以是当碎片异常严重时,I/O读取异常频繁,多读取了4倍的数据页。

其次,如例一,当SQLServer反省是否所必要的整个数据都已经在数据缓冲区时,会发明已经有4819个数据页在数据缓冲区中,还有六个数据页不在,它就会急速再次读取磁盘,以是有了6次的物理读,在将所必要的页读到数据缓冲区。一旦所有的数据都在数据缓冲区后,SQL Server就可以处置惩罚查询了。

您可能还会对下面的文章感兴趣: