[整理] - Index Fragmentation
Pages & Extents(页和扩展盘区)
SQL Server 2000最基本的数据存储单元是data page,1个8K的存储空间。在分配存储空间时,SQL Server 2000并不是每次分配1个page,基本的存储空间分配单元是8个page的连续空间,称为extent。
关于SQL Server 2000的page、extents和index结构,参考:MSDN - Pages and Extents, MSDN - Table and Index Architecture。
Page Split(页切分)
SQL Server在Insert/Update时,如果要更新的page已经存储满,无法容纳下新的数据,则SQL Server将这个page的一半数据切分出来,重新分配一个page存放,然后再进行Insert/Update操作,将以满的数据页切分成两个数据页的操作叫做page split。
不管是data page还是index page,都会发生page split。在Insert操作时,如果page上的free space小于要插入的记录大小,将进行page split;在Update时,如果table中存在变宽字段,变宽字段的长度变大导致原page上free space不够,将进行page split。
Index Fragmentation(索引碎片)
SQL Server的index fragmentation有两种:external fragmentation和internal fragmentation。
External fragmentation:
Index page的逻辑顺序不连续时,叫做external fragmentation。Index建立时,index page的存储在逻辑上都是连续的。在进行insert操作时,可能需要在两个索引之间插入这个新的索引。如果在索引插入位置的index page还有足够的空间,则会直接在这个index page中插入新的索引值;如果在这个index page上空间已满或者不够新的索引值所需空间,则SQL Server会进行page split,将插入位置的index page一部分数据移走,以释放出空间来插入新的索引,被移走的数据在其它位置重新分配新的page存放。这样,随着insert操作的增加,index page在逻辑上的连续程度就越来越低。
下图示例索引刚刚建立好之后逻辑上是连续时的索引结构:
![](https://images.cnblogs.com/cnblogs_com/riccc/db_sql_index1.GIF)
假如此时需要插入索引值为2的新索引,则插入之后的索引结构如下图:
![](https://images.cnblogs.com/cnblogs_com/riccc/db_sql_index2.GIF)
插入之后index page结构在逻辑上变得不连续。
在通过index返回特定记录,或者返回不用指定排序的记录集时,external fragmentation不会对查询性能产生太大影响。当需要返回指定排序的记录集时,排序过程中需要对逻辑上非连续的index page进行额外处理,对于大数据量的表,如果index page非常多,external fragmentation很严重,就需要消耗高昂的查询成本。另外,external fragmentation对缓存效率产生影响。
External fragmentation使用两个方面的指标来描述,page的连续程度和extent的连续程度。
Internal fragmentation:
Index page中如果存储空间未达到最大存储容量,叫做internal fragmentation。不考虑fill factor因素的影响,index建立时,索引结构逻辑上连续,并且每个index page都存储满,被充分利用。Delete操作会造成index page出现空闲;External fragmentation的示例中,insert操作时的page split也造成index page出现空闲。
严重的internal fragmentation,造成index page占用比实际所需大得多的存储空间。查询中进行index scan时,增加了logical READS、I/O等操作,产生性能问题。
Internal fragmentation使用页的平均页的空闲程度/利用程度作为指标。
Fill factor:
如果有设置或者是SQL Server自动维护了一个fill factor值,则在创建索引时,每一个index page都不会存储满,而根据fill factor值预留一部分空闲空间。在external fragmentation的示例中,假如第一个index page没有存储满,则在插入索引值为2的新索引时,就不需要将这个index page进行split,从而可以改善insert操作。
Fill factor用于需要频繁进行insert/update操作的表中,避免大量的page split出现。显然,fill factor的使用类似于internal fragmentation,但对大量的insert操作以及各种data page的结构带来极大的改善。对于fill factor,不太方便准确的评估什么样的值最佳,Microsoft建议让SQL Server自动维护。不恰当的fill factor设置,同internal fragmentation一样,影响SQL Server性能。
DBCC SHOWCONFIG
用于显示数据、索引fragmentation信息。
DBCC SHOWCONTIG (TblUserItem,PK_TblUserItem)
显示表TblUserItem中索引PK_TblUserItem的fragmentation信息。
DBCC SHOWCONTIG (TblUserItem) WITH ALL_INDEXES
显示表TblUserItem所有索引的fragmentation信息。
DBCC SHOWCONTIG WITH ALL_INDEXES
显示当前数据库中所有索引的fragmentation信息。
执行的结果示例如下:
DBCC SHOWCONTIG scanning 'TblUserItem' table...
Table: 'TblUserItem' (1077578877); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 56933
- Extents Scanned..............................: 7563
- Extent Switches..............................: 7565
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 94.07% [7117:7566]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.03%
- Avg. Bytes Free per Page.....................: 114.3
- Avg. Page Density (full).....................: 98.59%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Page Scanned:实际扫描的page数量。可以从每个数据行的大小、总的行数大致计算出实际所需的data page数量,如果Page Scanned数量远远超过计算出的实际data page数量,则internal fragmentation比较严重。
Extents Scanned:理想值为将Page Scanned/8圆整为最小整数。如果Extents Scanned大于理论值,则存在一定程度的external fragmentation。
Extent Switches:理想值为Extents Scanned减1,超过这个值说明存在external fragmentation。
Avg. Pages per Extent:理想值为8,小于8则存在external fragmentation。
Scan Density [Best Count:Actual Count]:这是DBCC SHOWCONTIG返回的最有意义的一个值,为理想的extents数量比实际的extents数量,反应external fragmentation的重要统计信息之一。理想值为100%,不能低于60%。
Logical Scan Fragmentation:另外一个非常有意义的值,指示page的非连续程度,反应external fragmentation的重要统计信息之一。应当在0%-10%之间,不能高于15%。
Extent Scan Fragmentation:指示extents的非连续程度,理想值为0%。
Avg. Bytes Free per Page:平均每page上的空闲字节数。过高的值表明存在internal fragmentation,但是需要将fill factor因素排除。
Avg. Page Density (full):与Avg. Bytes Free per Page对立的一个百分比参数,较低的值表明存在internal fragmentation。
另外DBCC SHOWCONTIG还有几个可选参数可以使用,具体参考Online Help。
Resolving fragmentation issues
1. Drop原来的索引再重建这些索引
这个过程中索引被drop和rebuild,会使这个期间所有的查询阻塞;对所有的clustered index和non-clustered index使用该方法,可能会导致non-clustered index重建两次。
优点是索引彻底重建,达到最理想的状况。如果external和internal fragmentation都相当严重,应当使用该方法。
2. 使用DROP_EXISTING子句
使用DROP_EXISTING子句,可以避免non-clustered index被重建两次。
3. DBCC DBREINDEX
可以仅指定Table名字,而无须指定索引名称,该命令自动将Table的所有索引进行重建,这样比写多条DROP INDEX和CREATE INDEX语句进行操作要方便。这个命令同时将Table的PRIMARY KEY和UNIQUE约束、STATISTICS重建,无须额外对这些约束和STATISTICS进行操作。
DBCC DBREINDEX能够比较充分的利用多CPU进行处理,对数据量相当大和fragmentation非常严重的表操作时会比较快。
该方法在一个事务中完成操作,在数据文件中需要有足够的free space来满足将所有的索引及相关的一些对象进行重建,否则操作可能失败,或者是重建的不十分彻底,例如重建完后logical fragmentation可能仍大于0。对于数据量非常大的表,所需的free space也更多,应当特别注意这一点。
4. DBCC INDEXDEFRAG
DBCC INDEXDEFRAG分两个步骤进行操作,首先对各个index page进行压缩,释放出多余的page;然后重组index page的各个根节点,使得index page的逻辑顺序与物理存储顺序一致,即在物理存储方向上保证逻辑顺序是连续的。
4种方法中,其它三种都必须在数据库offline情况下进行,因为在操作期间会导致使用这些索引的所有查询阻塞。DBCC INDEXDEFRAG可以在数据库online的情况下执行,但是整理不够彻底。因为一方面,在执行期间会忽略被lock的 index page,另一方面它不会新分配page进行重排序,只是在原来已分配的page空间里进行重组。这个命令的目标也就是使index page的逻辑顺序与物理存储顺序一致,如果逻辑上相邻的两个page或extent的物理存储之间存在间隔,DBCC INDEXDEFRAG不会采取操作消除这种物理存储间隔。因此如果index page所占用的空间非常大时,访问index page空间可能会增加磁头定位和移动的开销,从而在一定程度上增加了I/O操作成本。
专家建议:60%<Scan Density<75%、10<Logical fragmentation<15时,使用DBCC INDEXDEFRAG;Scan Density<60%、Logical fragmentation>15时,使用DBCC DBREINDEX。
另外,在table设计方面,可以考虑以下几点:
1. 对于Insert/Update操作频繁的table,选择一个合适的fill factor。
2. 将变宽字段设计为等宽字段。例如物料号、订单号、客户代码、供应商代码等。
3. 设立删除标记而不是物理删除数据。
例如有些大型系统,一个对象可能会有几十个字段。通常情况下的做法是用一个table容纳所有这些字段,但是出于系统访问效率方面考虑,可以使用两个 table来存储。主表存放关键性、访问最频繁的字段属性,尽量不使用变宽字段;从表存放附加的、描述性的、访问比较少的字段属性。这样虽然是一对一关联的表,但是对于大多数情况下对于只需要访问主表属性的查询,可以做到极大的提高访问性能。
Reference:Randy Dyess - SQL Server Index Fragmentation and Its Resolution。
SQL Server 2000最基本的数据存储单元是data page,1个8K的存储空间。在分配存储空间时,SQL Server 2000并不是每次分配1个page,基本的存储空间分配单元是8个page的连续空间,称为extent。
关于SQL Server 2000的page、extents和index结构,参考:MSDN - Pages and Extents, MSDN - Table and Index Architecture。
Page Split(页切分)
SQL Server在Insert/Update时,如果要更新的page已经存储满,无法容纳下新的数据,则SQL Server将这个page的一半数据切分出来,重新分配一个page存放,然后再进行Insert/Update操作,将以满的数据页切分成两个数据页的操作叫做page split。
不管是data page还是index page,都会发生page split。在Insert操作时,如果page上的free space小于要插入的记录大小,将进行page split;在Update时,如果table中存在变宽字段,变宽字段的长度变大导致原page上free space不够,将进行page split。
Index Fragmentation(索引碎片)
SQL Server的index fragmentation有两种:external fragmentation和internal fragmentation。
External fragmentation:
Index page的逻辑顺序不连续时,叫做external fragmentation。Index建立时,index page的存储在逻辑上都是连续的。在进行insert操作时,可能需要在两个索引之间插入这个新的索引。如果在索引插入位置的index page还有足够的空间,则会直接在这个index page中插入新的索引值;如果在这个index page上空间已满或者不够新的索引值所需空间,则SQL Server会进行page split,将插入位置的index page一部分数据移走,以释放出空间来插入新的索引,被移走的数据在其它位置重新分配新的page存放。这样,随着insert操作的增加,index page在逻辑上的连续程度就越来越低。
下图示例索引刚刚建立好之后逻辑上是连续时的索引结构:
假如此时需要插入索引值为2的新索引,则插入之后的索引结构如下图:
插入之后index page结构在逻辑上变得不连续。
在通过index返回特定记录,或者返回不用指定排序的记录集时,external fragmentation不会对查询性能产生太大影响。当需要返回指定排序的记录集时,排序过程中需要对逻辑上非连续的index page进行额外处理,对于大数据量的表,如果index page非常多,external fragmentation很严重,就需要消耗高昂的查询成本。另外,external fragmentation对缓存效率产生影响。
External fragmentation使用两个方面的指标来描述,page的连续程度和extent的连续程度。
Internal fragmentation:
Index page中如果存储空间未达到最大存储容量,叫做internal fragmentation。不考虑fill factor因素的影响,index建立时,索引结构逻辑上连续,并且每个index page都存储满,被充分利用。Delete操作会造成index page出现空闲;External fragmentation的示例中,insert操作时的page split也造成index page出现空闲。
严重的internal fragmentation,造成index page占用比实际所需大得多的存储空间。查询中进行index scan时,增加了logical READS、I/O等操作,产生性能问题。
Internal fragmentation使用页的平均页的空闲程度/利用程度作为指标。
Fill factor:
如果有设置或者是SQL Server自动维护了一个fill factor值,则在创建索引时,每一个index page都不会存储满,而根据fill factor值预留一部分空闲空间。在external fragmentation的示例中,假如第一个index page没有存储满,则在插入索引值为2的新索引时,就不需要将这个index page进行split,从而可以改善insert操作。
Fill factor用于需要频繁进行insert/update操作的表中,避免大量的page split出现。显然,fill factor的使用类似于internal fragmentation,但对大量的insert操作以及各种data page的结构带来极大的改善。对于fill factor,不太方便准确的评估什么样的值最佳,Microsoft建议让SQL Server自动维护。不恰当的fill factor设置,同internal fragmentation一样,影响SQL Server性能。
DBCC SHOWCONFIG
用于显示数据、索引fragmentation信息。
DBCC SHOWCONTIG (TblUserItem,PK_TblUserItem)
显示表TblUserItem中索引PK_TblUserItem的fragmentation信息。
DBCC SHOWCONTIG (TblUserItem) WITH ALL_INDEXES
显示表TblUserItem所有索引的fragmentation信息。
DBCC SHOWCONTIG WITH ALL_INDEXES
显示当前数据库中所有索引的fragmentation信息。
执行的结果示例如下:
DBCC SHOWCONTIG scanning 'TblUserItem' table...
Table: 'TblUserItem' (1077578877); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 56933
- Extents Scanned..............................: 7563
- Extent Switches..............................: 7565
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 94.07% [7117:7566]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.03%
- Avg. Bytes Free per Page.....................: 114.3
- Avg. Page Density (full).....................: 98.59%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Page Scanned:实际扫描的page数量。可以从每个数据行的大小、总的行数大致计算出实际所需的data page数量,如果Page Scanned数量远远超过计算出的实际data page数量,则internal fragmentation比较严重。
Extents Scanned:理想值为将Page Scanned/8圆整为最小整数。如果Extents Scanned大于理论值,则存在一定程度的external fragmentation。
Extent Switches:理想值为Extents Scanned减1,超过这个值说明存在external fragmentation。
Avg. Pages per Extent:理想值为8,小于8则存在external fragmentation。
Scan Density [Best Count:Actual Count]:这是DBCC SHOWCONTIG返回的最有意义的一个值,为理想的extents数量比实际的extents数量,反应external fragmentation的重要统计信息之一。理想值为100%,不能低于60%。
Logical Scan Fragmentation:另外一个非常有意义的值,指示page的非连续程度,反应external fragmentation的重要统计信息之一。应当在0%-10%之间,不能高于15%。
Extent Scan Fragmentation:指示extents的非连续程度,理想值为0%。
Avg. Bytes Free per Page:平均每page上的空闲字节数。过高的值表明存在internal fragmentation,但是需要将fill factor因素排除。
Avg. Page Density (full):与Avg. Bytes Free per Page对立的一个百分比参数,较低的值表明存在internal fragmentation。
另外DBCC SHOWCONTIG还有几个可选参数可以使用,具体参考Online Help。
Resolving fragmentation issues
1. Drop原来的索引再重建这些索引
这个过程中索引被drop和rebuild,会使这个期间所有的查询阻塞;对所有的clustered index和non-clustered index使用该方法,可能会导致non-clustered index重建两次。
优点是索引彻底重建,达到最理想的状况。如果external和internal fragmentation都相当严重,应当使用该方法。
2. 使用DROP_EXISTING子句
使用DROP_EXISTING子句,可以避免non-clustered index被重建两次。
3. DBCC DBREINDEX
可以仅指定Table名字,而无须指定索引名称,该命令自动将Table的所有索引进行重建,这样比写多条DROP INDEX和CREATE INDEX语句进行操作要方便。这个命令同时将Table的PRIMARY KEY和UNIQUE约束、STATISTICS重建,无须额外对这些约束和STATISTICS进行操作。
DBCC DBREINDEX能够比较充分的利用多CPU进行处理,对数据量相当大和fragmentation非常严重的表操作时会比较快。
该方法在一个事务中完成操作,在数据文件中需要有足够的free space来满足将所有的索引及相关的一些对象进行重建,否则操作可能失败,或者是重建的不十分彻底,例如重建完后logical fragmentation可能仍大于0。对于数据量非常大的表,所需的free space也更多,应当特别注意这一点。
4. DBCC INDEXDEFRAG
DBCC INDEXDEFRAG分两个步骤进行操作,首先对各个index page进行压缩,释放出多余的page;然后重组index page的各个根节点,使得index page的逻辑顺序与物理存储顺序一致,即在物理存储方向上保证逻辑顺序是连续的。
4种方法中,其它三种都必须在数据库offline情况下进行,因为在操作期间会导致使用这些索引的所有查询阻塞。DBCC INDEXDEFRAG可以在数据库online的情况下执行,但是整理不够彻底。因为一方面,在执行期间会忽略被lock的 index page,另一方面它不会新分配page进行重排序,只是在原来已分配的page空间里进行重组。这个命令的目标也就是使index page的逻辑顺序与物理存储顺序一致,如果逻辑上相邻的两个page或extent的物理存储之间存在间隔,DBCC INDEXDEFRAG不会采取操作消除这种物理存储间隔。因此如果index page所占用的空间非常大时,访问index page空间可能会增加磁头定位和移动的开销,从而在一定程度上增加了I/O操作成本。
专家建议:60%<Scan Density<75%、10<Logical fragmentation<15时,使用DBCC INDEXDEFRAG;Scan Density<60%、Logical fragmentation>15时,使用DBCC DBREINDEX。
另外,在table设计方面,可以考虑以下几点:
1. 对于Insert/Update操作频繁的table,选择一个合适的fill factor。
2. 将变宽字段设计为等宽字段。例如物料号、订单号、客户代码、供应商代码等。
3. 设立删除标记而不是物理删除数据。
例如有些大型系统,一个对象可能会有几十个字段。通常情况下的做法是用一个table容纳所有这些字段,但是出于系统访问效率方面考虑,可以使用两个 table来存储。主表存放关键性、访问最频繁的字段属性,尽量不使用变宽字段;从表存放附加的、描述性的、访问比较少的字段属性。这样虽然是一对一关联的表,但是对于大多数情况下对于只需要访问主表属性的查询,可以做到极大的提高访问性能。
Reference:Randy Dyess - SQL Server Index Fragmentation and Its Resolution。