SQLSERVER 索引维护
浏览数：182 /
时间：2015年06月12日
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在逻辑上的连续程度就越来越低。
下图示例索引刚刚建立好之后逻辑上是连续时的索引结构：
假如此时需要插入索引值为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。
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。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
