DSS型工作量
测试中，DSS工作量包含22个由复杂Select语句构成的报表类型的查询。这些查询严格地以批处理方式在服务器端运行。所有查询包含一个或多个 多表联接，大多数查询需要扫描很大范围的索引。
表2 记录测试中用到的索引的平均碎片和页密度级别。碎片级别通过下属行为组合得到：
- 以Bulk Insert方式插入新数据到数据库，并模拟周期性地刷新数据。
- 删除某个范围内的数据。
- 按关键值执行一些更新操作，虽然这至少会影响碎片级别，不过和插入和删除操作相比，更新涉及到的记录相对还是比较少。
| Fragmentation level | Average logical fragmentation (%) | Average page density (%) |
| Small-Scale Environment | | |
| Low (1) | 7.8 | 80.1 |
| Medium (2) | 16.6 | 68.1 |
| High (3) | 29.5 | 69.2 |
| Large-Scale Environment | | |
| Low (1) | 5.9 | 84.4 |
| Medium (2) | 13.8 | 70.3 |
DSS工作类型的测试结果和OLTP工作类型相差很大。在整理索引碎片后，性能提高很明显。因为此时工作量的性能强烈依赖于磁盘吞吐量(大多数查询 会包含索引扫描)，因此该结果是可以预料到的。下面的图2和图3显示DSS型工作量在整理索引碎片前后的性能收益情况。如图中所示，性能从碎片整理中显著 提升。
在小规模环境中，在碎片较低级别，性能提升了60％，而在碎片较高级别提升了460％。在大规模环境中，在碎片较低级别性能提升13%，在中等级别 提升了40%。结果显示大规模环境中，碎片对性能影响影响相对较小，这是因为该环境从磁盘子系统的表现获益更多一些。更详细的讨论见本文后面 的“碎片对磁盘吞吐量的影响和SQL Server预读管理器”章节。
图2从数值上显示，在小规模环境下，DBCC INDEXDEFRAG的结果比DBCC DBREINDEX要好一些。不过，在大多数情况下，完全重建索引应该具有更好的性能。
在解释这些测试结果时，需要牢记以下几点：
- 图2和图3显示的结果意味着这是应用DBCC INDEXDEFRAG的“最佳”场合。测试中，DBCC INDEXDEFRAG运行在一个禁止的系统上；因此DBCC INDEXDEFRAG可以完全消除碎片。当DBCC INDEXDEFRAG运行在一个动态的系统上，即数据保持在线更新，DBCC INDEXDEFRAG会跳过那些被锁住的页。因此DBCC INDEXDEFRAG也许就无法完全消除碎片。要衡量DBCC INDEXDEFRAG发挥了多大作用，可以在DBCC INDEXDEFRAG后立刻运行DBCC SHOWCONTIG。
- 数据的分布情况会影响磁盘性能。小规模环境中，数据只分布在两个物理磁盘(磁盘容量加起来一共33.5GB)上，在数据库创建前，这两个磁盘是空 的。数据库创建后，数据文件大小在22GB到30GB之间。当数据库创建时，数据分布在磁盘外围部分。DBCC INDEXDEFRAG整理碎片也是从最邻近原始数据的位置开始。因为DBCC DBREINDEX完全重建索引，在释放旧的索引页前，它必须首先给新索引页分配空间。分配新空间使得数据离原始数据位置较远，并且分布在磁盘内侧，因此 造成I/O吞吐量有轻微的下降。在小规模环境下的benchmark测试中，这种下降表现为读取数据吞吐量下降15%。
- 剩余空间容量同样会影响DBCC DBREINDEX。如果没有大量连续的空闲空间，DBREINDEX会强迫使用数据文件中的空闲空间，从而导致索引重建时带有一小部分数量的逻辑碎片。 关于DBCC DBREINDEX对剩余空间的需求的信息，见本文后面的"DBCC DBREINDEX"章节。
确定查询的I/O流量
因为具有大量I/O读写的查询从碎片整理中获益最多，所以讨论如何确定某个特定查询的I/O流量是必要的。SET STATISTICS IO命令可以报告完成一个特定查询时，服务器实例上的读取量和读取类型。可以在查询管理器中选择该命令开关为ON和OFF，使用方法如下：
|
SET STATISTIC IO ON |
|
Table ‘table_1′. |
| Value | Description |
| Scan count | Number of scans performed |
| logical reads | Number of pages read from the data cache |
| physical reads | Number of pages read from disk |
| read-ahead reads | Number of pages placed into the cache for the query |
通过physical reads和read-ahead reads值，可以对某个特定查询涉及的I/O量有一个估计。physical reads和read-ahead reads都表示从磁盘读取的页数。多数情况下，read-ahead reads比physical reads数值要大。
注意 在通过SQL Profiler 获取信息时，reads列表示的是逻辑读取量(logical reads)，而不是物理读取量(physical reads)。
除了重新对页进行排序，通过增加索引叶级页的页密度，索引的碎片整理还降低执行某个查询的I/O数量。页密度的提高导致完成相同的查询需要读取更少 的页，从而提高性能。
理解碎片整理带来的影响和SQL Server预读管理器
碎片对大量读取磁盘边缘的操作带来负面影响。可以使用Windows性能监视器来获取这种影响的衡量。通过性能监视器，可以观测磁盘活动情况，并且 有助于决定何时进行碎片整理。
为了理解为什么碎片对DSS型工作具有如此的影响，首先很重要的是要理解碎片是如何影响SQL Server预读管理器的。为了完成需要扫描一个或多个索引的查询，SQL Server预读管理器负责提前对索引页进行扫描，并且将额外的数据页放到SQL Server数据缓存中。根据基础页的物理顺序，预读管理器动态地调整读取量。当碎片较少时，预读管理器即时可以读取较大的数据块，更高效地利用I/O子 系统。当数据产生碎片时，预读管理器只能读取较小的数据块。预读的数量虽然和数据的物理顺序无关，不过，因为较小的读取请求消耗更多的CPU/时钟，所以 最终就会降低整个磁盘的吞吐量。
在所有情况下，预读管理器能提高性能；但是，当存在碎片，且预读管理器无法读取较大的数据块，整个磁盘的吞吐量就下降。通过检查性能监视器中的 Physical Disk相关的计数器可以发现该现象。下表列举并描述了这些计数器。
| Physical Disk counter | Description |
| Avg Disk sec/ Read | 该计数器用于衡量磁盘延迟。测试显示当碎片出于很高水平(大于等于30%)时，会增加磁盘延迟。 |
| Disk Read Bytes/ sec | 该计数器能很好地衡量全面的磁盘吞吐量。一段时间内工作量的下降趋势可以用来表示碎片正在影响性能。 |
| Avg Disk Bytes/ Read | 该计数器用于衡量每个读取请求带来的数据读取量。当索引页连续，SQL Server预读管理器可以一次读取较大的数据块，对I/O子系统的利用效率较高。测试显示该计数器值和碎片数量间有着直接联系。当碎片级别上升，该值就 下降，从而影响全面的磁盘吞吐量。 |
| Avg Disk Read Queue Length | 一般而言，该计数器为每两个物理磁盘上持续的平均数值。测试中，很可能由于较高的延迟和较低的全面磁盘吞吐量，使得该计数器随着碎片的增加而增 加。 |
图4到图7显示在DSS型工作中，性能监视器报告的磁盘吞吐量和平均读取大小。
上面的图显示碎片对磁盘性能的影响趋势。虽然使用DBCC DBREINDEX和DBCC INDEXDEFRAG得到的结果不同，但是注意在所有系统上，都得到了一致的结果，即每次读取的平均大小和整体磁盘吞吐量随着碎片的增加而降低。正如你 所见的，整理所有碎片对磁盘吞吐量提高极大。
每次读取的平均大小还可以用来展示，碎片是如何影响预读管理器读取较大数据块的能力的。这点需牢记在心，不过，平均读取数量较大并不总是意味着整体 吞吐量较高。平均读取量大表示数据传输中，CPU负荷较小。当索引没有碎片时，读取64KB大小数据的速度和读取256KB大小数据的速度几乎一样。这个 结论在那些数据分布在多个磁盘上的大型系统而言，尤其如此。这些普遍和特殊的结论，会根据不同的系统，因为各种各样的原因(例如，不同的I/O子系统，工 作类型差异，数据在磁盘的分布特征等等)而不同。当监视系统时，寻找那些持续时间较长的磁盘吞吐量和读取数量的下降阶段。这些阶段以及DBCC SHOWCONTIG命令提供的信息，可以用于帮助来确定什么时候该进行索引的碎片整理了。
测试结果显示碎片也会带来磁盘延迟，不过，至于那些最高级别的碎片才会对磁盘延迟带来巨大的负面影响，并且也只在小规模环境下才有此现象。在大规模 环境下，由于SAN提供了很高的I/O性能，因此磁盘延迟值十分小而从来不会带来问题。
DBCC DBREINDEX vs. DBCC INDEXDEFRAG
除了使用Create INDEX命令来删除或者重新创建索引，还可以使用DBCC DBREINDEX和DBCC INDEXDEFRAG命令来帮助维护索引。
DBCC DBREINDEX
DBCC DBREINDEX用于在指定的表上重建一个或多个索引。DBCC DBREINDEX是离线操作方式。当该操作运行时，涉及到的表就无法被用户访问。DBCC DBREINDEX动态地重建索引。没有必要知道参与重建的表结构到底如何，是否用主键或者唯一性约束等信息；重建的时候会自动管理的。DBCC DBREINDEX完全重建索引，也就是说，将页密度级别恢复到最初(默认)的填充因子水平；当然你也可以选择页密度的新值。从内部运行看，DBCC DBREINDEX和手工用T-SQL语句来运行删除然后重新创建索引十分相似。
下面两点是DBCC DBREINDEX比DBCC INDEXDEFRAG优越的地方：
- DBCC DBREINDEX在重建索引过程中，自动重建统计；这将显著提高工作性能。
- DBCC DBREINDEX可以运行在多处理器环境下，利用多处理器的优势，当重建较大和碎片厉害的索引时，速度可以十分快。
DBCC DBREINDEX的所有工作是一个单一的，原子事务。必须完成创建新的索引并替换旧索引，然后旧索引页被释放。完成重建需要数据文件中有足够的空余空 间。如果空余空间不够，DBCC DBREINDEX要么无法重建索引，要么会产生大于0的逻辑碎片。所需空余空间视情况而定，取决于事务中要创建的索引数目。对于聚集索引而言，一个不错 的指导公式为：所需空余空间 = 1.2 * (平均行大小) * (行数量)。
对于非聚集索引，可以通过计算非聚集索引包含的每行平均大小(非聚集索引包含关键字长度 + 聚集中的索引关键字或者row ID长度)乘以行数量得到所需空间。如果对整个表进行索引重建，需要为聚集索引和非聚集索引留出足够的空间。同样，如果重建不唯一非聚集索引，也需要为聚 集和非聚集索引留出空余空间。因为SQL Server必须为每行创建唯一标识，因此非聚集索引是隐式重建的。使用DBCC DBREINDEX时，较佳的做法是指定那些索引需要整理。这样做可以使得对操作有更多的控制力，以及避免不必要的麻烦。
DBCC INDEXDEFRAG
DBCC INDEXDEFRAG用于对指定的索引进行重建。和DBCC DBREINDEX类似，也不需顾及表的基础结构；不过，DBCC INDEXDEFRAG无法用一个语句对所有的索引进行重建。对于每个希望进行碎片整理的索引，都必须运行一次DBCC INDEXDEFRAG。
和DBCC DEREINDEX不同的是，DBCC INDEXDEFRAG是在线操作的；因此在整理索引碎片时，仍然可以访问被整理到的表和索引。另外一个和DBCC INDEXDEFRAG很不同的地方是，DBCC INDEXDEFRAG可以中止和重新开始而不丢失任何工作信息。整个DBCC DBREINDEX操作作为一个原子事务运行。这意味着如果中止DBCC DBREINDEX操作，整个操作会回滚，继续的话必须重新开始。但是，如果中止DBCC INDEXDEFRAG，任务会立刻中止并不会丢失已经完成的任务，因为DBCC INDEXDEFRAG每个工作单位是独立的事务。
DBCC INDEXDEFRAG包括两个阶段：
- 压缩页并试图根据索引创建时指定的填充因子来调整页密度。DBCC INDEXDEFRAG会根据最初的填充因子，尽可能提高页密度级别。而DBCC INDEXDEFRAG不会，但是它会将那些高于最初填充因子的页密度降低。
- 通过移动页来使得物理顺序和索引叶级页的逻辑顺序一致，从而整理索引碎片。这个工作由一系列独立的很小的事务来完成；因此DBCC INDEXDEFRAG对整体系统性能，影响很小。图8显示DBCC INDEXDEFRAG的碎片整理阶段中页移动情况。
DBCC INDEXDEFRAG并不会帮助整理分散插入到数据文件中的索引，这种分散插入称为Interleave。同样，DBCC INDEXDEFRAG也不对扩展页碎片进行整理。当索引扩展页(扩展页=8页)中的数据并不连续的时候，出现Interleave，此时多个扩展页的数 据在文件中是交叉状态。因为即使逻辑顺序和物理顺序一致情况下，所有的索引页也不见得一定是连续的，因此即使没有逻辑碎片情况下，Interleave也 会存在。
虽然上面提到了DBCC INDEXDEFRAG的限制，但是测试显示，DBCC INDEXDEFRAG对性能的改善和DBCC DBREINDEX一样有用。实际上，从测试结果可以看出，即使重建了索引，使得Interleave最小，这部分优化并不会对性能带来显著提升。减少逻 辑碎片级别这部分优化才对性能提升最多。这就是为什么检查索引碎片时，建议将重点放在逻辑碎片整理和页密度碎片上的原因。表5总结了DBCC DBREINDEX和DBCC INDEXDEFRAG之间的差别。
| Functionality | DBCC DBREINDEX | DBCC INDEXDEFRAG |
| Online/Offline | Offline | Online |
| Faster when logical fragmentation is: | High | Low |
| Parallel processing | Yes | No |
| Compacts pages | Yes | Yes |
| Can be stopped and restarted without losing work completed to that point | No | Yes |
| Able to untangle interleaved indexes | May reduce interleaving | No |
| Additional free space is required in the data file for defragmenting | Yes | No |
| Faster on larger indexes | Yes | No |
| Rebuilds statistics | Yes | No |
| Log space usage | High in full recovery mode (logs entire contents of the index), low in bulk logged or simple recovery mode (only logs allocation of space) | Varies based on the amount of work performed |
| May skip pages on busy systems | No | Yes |
性能: DBCC DBREINDEX vs. DBCC INDEXDEFRAG
测试显示，无论是DBCC DBREINDEX还是DBCC INDEXDEFRAG，都可以有效地整理索引碎片，并将页密度恢复到初始填充因子规定的页密度附近。基于这些结果，下面需要决定什么时候应用哪种整理方 式。
如果允许有一段时间进行离线索引重建，DBCC DBREINDEX一般来说比DBCC INDEXDEFRAG要快。DBCC DBREINDEX可以充分利用多处理器系统的平行性能。DBCC INDEXDEFRAG用于对生产环境干扰不大，对工作性能影响不大的场合。测试显示，即使同时几个DBCC INDEXDEFRAG并行工作，对性能下降的影响也从来不会超出10%。但是，这也同样使得DBCC INDEXDEFRAG针对较大的索引整理时，需要很长的时间才能完成。而且，工作时间的长短还依赖于当时在服务器上运行的访问工作。
图9为DBCC INDEXDEFRAG和DBCC DBREINDEX的性能比较。图中的数据为小规模环境下，对所有索引进行整理的时间(大规模环境下结果类似，DBCC INDEXDEFRAGY运行时间为DBCC INDEXREINDEX的8倍)。当碎片级别增加，索引大小增加时，DBCC DBREINDEX可以比DBCC INDEXDEFRAG执行得更快。
日志考虑: DBCC DBREINDEX vs. DBCC INDEXDEFRAG
最后要考察的问题是使用DBCC INDEXDEFRAG和DBCC DBREINDEX时，写入事务日志的数据量差别。DBCC INDEXDEFRAG中写入事务日志的数据量依赖于碎片的级别和完成的工作量。测试显示，当数据库完全恢复模式下，DBCC INDEXDEFRAG写入事务日志的数据量远远小于DBCC DBREINDEX。DBCC INDEXDEFRAG的日志数据量，可以变化很大。这是因为DBCC INDEXDEFRAG完成的碎片整理工作量由页移动数量和必要的页压缩数量决定。因为DBCC INDEXDEFRAG工作由一系列小事务组成，因此可以通过备份来回收DBCC INDEXDEFRAG使用的那部分日志空间。
从日志使用的角度看，DBCC DBREINDEX和DBCC INDEXDEFRAG稍有不同；在大批量(bulk)日志恢复模式下，日志量具有最大的差异。在完全恢复模式下，DBCC DBREINDEX对每个索引页有日志镜像，在日志恢复模式下，就没有。因此，在完全恢复模式下，DBCC DBREINDEX所需的日志空间大约等于索引页数量乘以8KB。可以通过DBCC SHOWCONTIG来确定给定索引的页数量。在大规模环境下，运行DBCC DBREINDEX时，建议将恢复模式改为日志恢复模式。而在运行结束后，再改为完全恢复模式。
注意 ：由于大规模环境下，回滚事务需要付出巨大的时间代价，因此理解日志的需求很重要。
图10 显示在小规模和中度碎片级别环境下，DBCC INDEXDEFRAG和DBCC DBREINDEX日志空间使用的差别。虽然DBCC INDEXDEFRAG日志空间波动很大，不过测试结果可以体现DBCC DBREINDEX和DBCC INDEXDEFRAG的一般性差别。
结论
对于不同的工作类型，索引碎片整理具有十分不同的影响。某些应用可以从碎片整理中获取很大的性能提升。理解应用特征，系统性能和SQL Server提供的碎片统计信息，是正确决定何时进行碎片整理的关键。SQL Server提供一些命令来完成索引碎片整理。本文可以帮助我们来决定何时以及如何整理索引碎片，从而使性能得到最大的改善。
更多信息
要得到关于监视，分析和改善工作性能的更多信息，请访问如下资源：
- Microsoft Knowledge Base article 243589: "HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later" at http://support.microsoft.com/default.aspx?scid=KB;en-us;243589&sd=tech
- Microsoft SQL Server 2000 Performance Tuning Technical Reference, Microsoft Press, ISBN: 0-7356-1270-6
- "Windows 2000 IO Performance" from Microsoft Research at http://research.microsoft.com/BARC/Sequential_IO/Win2K_IO.pdf
附录 A : 测试环境
本文的测试使用下面的硬件和软件环境：
Microsoft Software
Microsoft Windows 2000 Data Center (Service Pack 3)
Microsoft SQL Server 2000 Enterprise Edition (Service Pack 2)
Hardware Platform
Small-Scale Environment:
Dell PowerEdge 6450
4 Intel Pentium III Xeon 550 MHz processors
4 GB RAM
Large-Scale Environment:
Dell PowerEdge 8450
8 Intel Pentium III Xeon 550 MHz processors
16 GB RAM
Storage
Small-Scale Environment:
1 Dell PowerVault 660f, with 2, 18 GB 10,000 RPM disks
Total Disk Space = 36 GB (Raid 0)
Large-Scale Environment:
1 Hitachi Freedom Storage Lightning 9960 system, with 192, 73 GB, 10,000
RPM disks
Total Disk Space = 13 TB (~6 TB after RAID 1+0 and further
striping/slicing)
Host bus adapters (HBA)
8 Emulex LP9002L PCI Host Bus Adapters
Firmware 3.82A1
Port Driver v5-2.11a2
Fabric switch
1 McData Switch, 1 GB
Storage management software
Hitachi Command Control Interface (CCI)
Hitachi ShadowImage
Databases
Representative OLTP and DSS databases
